Index

A B C D E F G H I J K L M N O P Q R S T U V W Y Z

Symbols

%BULK_EXCEPTIONS. See BULK_EXCEPTIONS cursor attribute

%BULK_ROWCOUNT. See BULK_ROWCOUNT cursor attribute

%FOUND. See FOUND cursor attribute

%ISOPEN. See ISOPEN cursor attribute

%NOTFOUND. See NOTFOUND cursor attribute

%ROWCOUNT. See ROWCOUNT cursor attribute

%ROWTYPE. See ROWTYPE attribute

%TYPE. See TYPE attribute

:= assignment operator, 1.2.2.2

|| concatenation operator, 2.6.1.9

. item separator, 2.1.1

<< label delimiter, 2.1.1

.. range operator, 2.1.1, 4.3.6

=, !=, <>, and ~= relational operators, 2.6.1.4

<, >, <=, and >= relational operators, 2.6.1.4

@ remote access indicator, 2.1.1, 2.3

-- single-line comment delimiter, 2.1.1

; statement terminator, 2.1.1, 13

- subtraction/negation operator, 2.1.1

A

ACCESS_INTO_NULL exception, 10.3

actual parameters, 6.4.2

additional information

Oracle Technology Network, Preface, 1

address

REF CURSOR, 6.5.1

advantages

PL/SQL, 1.1

aggregate assignment, 2.2.5.1

aggregate functions

and PL/SQL, 6.1.3

AVG, 2.9

COUNT, 2.9

AL16UTF16 character encoding, 3.1.3.1

aliases

using with a select list, 2.2.5.2

aliasing

for expression values in a cursor FOR loop, 6.3.7

parameters, 8.13

ALL row operator, 6.1.3, 6.1.5

ALL_PLSQL_OBJECT_SETTINGS view

PL/SQL compiler parameter settings, 11.1, 11.2, 11.9.7

ALTER .. COMPILE statement, 10.9.2

ALTER PROCEDURE

using to recompile a procedure, 10.9.3

analytic functions

CORR, 2.9

LAG, 2.9

anonymous blocks, 1.2.1

apostrophes, 2.1.3.3

architecture

PL/SQL, 1.3

ARRAY

VARYING, 5.3

arrays

associative, 5.1.1

index-by-tables, 5.1.1

variable-size, 5.1.1

articles

technical on OTN, Preface

defining a procedure, 8.3

defining a procedure body, 8.3

assignment operator, 1.2.2.2

assignment statement

links to examples, 13

syntax, 13

assignments

aggregate, 2.2.5.1

character string, 3.4.1

collection, 5.5

field, 5.11

IN OUT parameters, 1.2.2.2

records, 5.11

semantics, 3.4.1

variables, 1.2.2.2

associative arrays

sets of key-value pairs, 5.1.1.3

syntax, 13

understanding, 5.1.1

VARCHAR2 keys and globalization settings, 5.1.1.4

versus nested tables, 5.2.1

asynchronous operations, 9.7.1

atomically null

object types in PL/SQL, 12.1.2

attributes

%ROWTYPE, 1.2.5.2, 2.2.5

%TYPE, 1.2.5.1, 2.2.4

explicit cursors, 6.2.2.6, 6.2.2.6

AUTHID clause

specifying privileges for a subprogram, 8.9.2

use with functions, 8.4

using to specify privileges of invoker, 8.9

autonomous functions

calling from SQL, 6.8.5

RESTRICT_REFERENCES pragma, 6.8.5

autonomous transactions

advantages, 6.8.1

avoiding errors, 6.8.3

comparison with nested transactions, 6.8.2.1

controlling, 6.8.3

in PL/SQL, 6.8

SQL%ROWCOUNT attribute, 6.2.1.1

autonomous triggers

using, 6.8.4

AUTONOMOUS_TRANSACTION pragma

defining, 6.8.2

links to examples, 13

syntax, 13

AVG

SQL aggregate function, 2.9

B

base types

PL/SQL number types, 3.1.1.1.1

PL/SQL types, 3.2

basic loops, 4.3.1

BEGIN

block structure, 1.2.1

start of executable part, 8.3

start of executable PL/SQL block, 13

syntax, 13

best practices

locating information on OTN, Preface

Oracle Technology Network, 1, 11

BETWEEN clause

FORALL, 13

BETWEEN comparison operator, 2.6.1.7

expressions, 13

BFILE datatype, 3.1.4.1

binary operators, 2.6

BINARY_DOUBLE datatype, 3.1.1.2

BINARY_FLOAT and BINARY_DOUBLE datatypes

for computation-intensive programs, 11.6

BINARY_FLOAT datatype, 3.1.1.2

BINARY_FLOAT_INFINITY

constant, 3.1.1.2

BINARY_FLOAT_MAX_NORMAL

constant, 3.1.1.2

BINARY_FLOAT_MAX_SUBNORMAL

constant, 3.1.1.2

BINARY_FLOAT_MIN_NORMAL

constant, 3.1.1.2

BINARY_FLOAT_MIN_SUBNORMAL

constant, 3.1.1.2

BINARY_FLOAT_NAN

constant, 3.1.1.2

BINARY_INTEGER datatype, 3.1.1.1

compared to PLS_INTEGER, Preface

new features, Preface

BINARY_INTEGER. See also PLS_INTEGER

bind variables, 1.2.2.3

binding, 11.5

blank-padding semantics, 3.4.2

BLOB datatype, 3.1.4.2

block declaration

syntax, 13

blocks

BEGIN, 1.2.1

DECLARE, 1.2.1

END, 1.2.1

EXCEPTION, 1.2.1

label, 2.4

links to examples, 13

nesting, 1.2.1, 1.2.1

PL/SQL, 13

structure, 1.2.1, 1.2.1

syntax, 13

BODY

CREATE PACKAGE SQL statement, 1.2.8.2, 9.1, 13

CREATE TYPE SQL statement, 13

with SQL CREATE PACKAGE statement, 1.2.8.2, 9.1

body

cursor, 9.9

function, 13

functions, 8.4

package, 9.4

packages, 13

procedure, 8.3, 13

Boolean

assigning values, 2.5.1

expressions, 2.6.2

literals, 2.1.3.4

BOOLEAN datatype, 3.1.5.1

built-in functions, 2.9

bulk

fetches, 11.5.2.1

returns, 11.5.2.3

BULK clause

with COLLECT, 11.5.2

BULK COLLECT clause, 11.5.2

checking whether no results are returned, 11.5.2

DELETE statement, 13

EXECUTE IMMEDIATE, 13

FETCH, 13

retrieving DML results, 11.5.2.3

retrieving query results with, 11.5.2

RETURNING clause, 13

returning multiple rows, 6.3.2

SELECT INTO, 13

using LIMIT clause, 11.5.2, 11.5.2.2

using ROWNUM pseudocolumn, 11.5.2

using SAMPLE clause, 11.5.2

using with FORALL statement, 11.5.2.4

bulk SQL

in dynamic SQL, 7.3

using to reduce loop overhead, 11.5

BULK_EXCEPTIONS cursor attribute

ERROR_CODE field, 11.5.1.3

ERROR_INDEX field, 11.5.1.3

example, 11.5.1.3

handling FORALL exceptions, 11.5.1.3

using ERROR_CODE field with SQLERRM, 11.5.1.3

BULK_ROWCOUNT cursor attribute

affected by FORALL, 11.5.1.2

by-reference parameter passing, 8.13

by-value parameter passing, 8.13

C

CALL

SQL statement, 1.2.8.2

call specification, 9.1

calling

Java stored procedures, 8.11

procedures, 1.2.8.2

stored subprograms, 1.3.1.2

calls

inter-language, 8.11

resolving subprogram, 8.8

subprograms, 8.6.2

CARDINALITY operator

for nested tables, 5.6

carriage returns, 2.1

CASE expressions, 2.6.3, 2.6.3

overview, 1.2.6.1

case sensitivity

identifier, 2.1.2

string literal, 2.1.3.3

CASE statement

links to examples, 13

searched, 4.2.4.1

syntax, 13

using, 4.2.4

CASE_NOT_FOUND exception, 10.3

CHAR datatype, 3.1.2.1

differences with VARCHAR2, 3.4

character literals, 2.1.3.2

character sets

PL/SQL, 2.1

CHARACTER subtype, 3.1.2.1.1

character values

assigning, 3.4.1

comparing, 3.4.2

inserting, 3.4.3

selecting, 3.4.4

clauses

AUTHID, 8.4, 8.9, 8.9.2

BULK COLLECT, 11.5.2

LIMIT, 11.5.2.2

CLOB datatype, 3.1.4.3

CLOSE statement

disables cursor, 6.2.2.5

disabling cursor variable

closing, 6.5.4.4

links to examples, 13

syntax, 13

code samples

Oracle Technology Network, Preface, 1

collating sequence, 2.6.2.2

COLLECT clause

with BULK, 11.5.2

collection exceptions

when raised, 5.9

collection methods

syntax, 13

usage, 5.8

COLLECTION_IS_NULL exception, 10.3

collections

allowed subscript ranges, 5.4.1

applying methods to parameters, 5.8.9

assigning, 5.5

associative arrays versus nested tables, 5.2.1

avoiding exceptions, 5.9

bulk binding, 5.11.5, 11.5

choosing the type to use, 5.2

comparing, 5.6

constructors, 5.4

COUNT method, 5.8.2

declaring variables, 5.3, 5.3.1

defining types, 5.3

DELETE method, 5.8.8

element types, 5.3

EXISTS method, 5.8.1

EXTEND method, 5.8.6

FIRST method, 5.8.4

initializing, 5.4

LAST method, 5.8.4

LIMIT method, 5.8.3

links to examples, 13, 13

methods, 5.8

multilevel, 5.7

NEXT method, 5.8.5

operators to transform nested tables, 5.5

ordered group of elements, 5.1

overview, 1.2.10.2

PRIOR method, 5.8.5

referencing, 5.4

referencing elements, 5.4.1

scope, 5.3

syntax, 13

testing for null, 5.6

TRIM method, 5.8.7

types in PL/SQL, 5

understanding, 5.1.1

using object types with, 12.4

varrays versus nested tables, 5.2.2

column aliases

expression values in a cursor loop, 6.3.7

when needed, 2.2.5.2

COMMENT clause

using with transactions, 6.7.1

comments

in PL/SQL, 2.1.4

links to examples, 13

restrictions, 2.1.4.3

syntax, 13

COMMIT statement, 6.7.1, 13

links to examples, 13

comparison operators, 6.1.5

comparisons

of character values, 3.4.2

of expressions, 2.6.2

of null collections, 5.6

operators, 2.6.1.3

PL/SQL, 2.6

with NULLs, 2.6.4

compiler parameter settings

ALL_PLSQL_OBJECT_SETTINGS view, 11.1, 11.2, 11.9.7

compiler parameters

and REUSE SETTINGS clause, 11.1

PL/SQL, 11.1

compiling

conditional, 1.2.7, 2.7

PL/SQL procedures for native execution, 11.9

composite types

collection and records, 5.1

overview, 3.1

concatenation operator, 2.6.1.9

treatment of nulls, 2.6.4.1

conditional compilation, 1.2.7, 2.7

availability for previous Oracle database releases, 2.7.1

control tokens, 2.7.1.1

examples, 2.7.2.1

inquiry directives, 2.7.1.4

limitations, 2.7.3

new features, Preface

NLS_LENGTH_SEMANTICS initialization parameter, 2.7.1.5

PLSQL_CCFLAGS initialization parameter, 2.7.1.5

PLSQL_CODE_TYPE initialization parameter, 2.7.1.5

PLSQL_DEBUG initialization parameter, 2.7.1.5

PLSQL_LINE flag, 2.7.1.5

PLSQL_OPTIMIZE_LEVEL initialization parameter, 2.7.1.5

PLSQL_UNIT flag, 2.7.1.5

PLSQL_WARNINGS initialization parameter, 2.7.1.5

restrictions, 2.7.3

static constants, 2.7.1.6.4

using PLSQL_CCFLAGS initialization parameter, 2.7.1.7, 2.7.1.7

using static expressions with, 2.7.1.6

using with DBMS_DB_VERSION, 2.7.1.8

using with DBMS_PREPROCESSOR, 2.7.2.2

conditional control, 4.2

conditional statement

guidelines, 4.2.5

CONSTANT

declaration, 13

for declaring constants, 1.2.2.4, 2.2.1

constants

declaring, 1.2.2.4, 2.2, 2.2.1

links to examples, 13

static, 2.7.1.6.4

syntax, 13

understanding PL/SQL, 1.2.2

constraints

NOT NULL, 2.2.3

constructors

collection, 5.4

context

transactions, 6.8.2.2

control structures

conditional, 4.2

iterative, 4.3

overview of PL/SQL, 4.1

sequential, 4.4

understanding, 1.2.6

conventions

PL/SQL naming, 2.3

conversions

datatype, 3.3

functions, 3.3.2

CORR

SQL analytic function, 2.9

correlated subqueries, 6.4.1

COUNT

SQL aggregate function, 2.9

COUNT method

collections, 5.8.2, 13

CREATE

with PROCEDURE statement, 1.2.8.1, 1.3.1.2, 8.1

CREATE FUNCTION statement, 1.2.8.1, 1.3.1.2, 8.1

CREATE PROCEDURE statement, 1.2.8.1, 1.3.1.2, 8.1

CREATE statement

packages, 1.2.8.2, 9.1

with FUNCTION, 1.2.8.1, 1.3.1.2, 8.1

CREATE_WRAPPED function

obfuscation, A.3

using, A.3.1

creating

functions, 1.2.8.1, 1.3.1.2, 8.1

packages, 1.2.8.2, 9.1

procedures, 1.2.8.1, 1.3.1.2, 8.1

CURRENT OF clause

with UPDATE, 6.7.7

CURRVAL

pseudocolumn, 6.1.4

cursor attributes

%BULK_EXCEPTIONS, 11.5.1.3

%BULK_ROWCOUNT, 11.5.1.2

%FOUND, 6.2.1.1, 6.2.2.6

%ISOPEN, 6.2.1.1, 6.2.2.6

%NOTFOUND, 6.2.1.1, 6.2.2.6

%ROWCOUNT, 6.2.1.1, 6.2.2.6

explicit, 6.2.2.6

implicit, 6.2.1.1

links to examples, 13

syntax, 13

values after OPEN, FETCH, and CLOSE, 6.2.2.6

cursor declarations

links to examples, 13

syntax, 13

cursor expressions

examples, 6.6.2

REF CURSORs, 6.6.3

restrictions, 6.6.1

using, 6.6

cursor FOR loops

passing parameters to, 6.4.2

cursor subqueries

using, 6.6

cursor variables, 6.5

advantages of, 6.5.2

as parameters to table functions, 11.10.8

avoiding errors with, 6.5.6

closing, 6.5.4.4

declaring, 6.5.3

defining, 6.5.3

fetching from, 6.5.4.3

links to examples, 13

opening, 6.5.4.1

passing as parameters, 6.5.3.1

reducing network traffic, 6.5.5

restrictions, 6.5.7

syntax, 13

using as a host variable, 6.5.4.2

CURSOR_ALREADY_OPEN exception, 10.3

cursors

advantages of using cursor variables, 6.5.2

attributes of explicit, 6.2.2.6

attributes of implicit, 6.2.1.1

closing explicit, 6.2.2.5

declaring explicit, 6.2.2.1

definition, 1.2.10.1

explicit, 1.2.10.1, 6.2.2

explicit FOR loops, 6.3.6

expressions, 6.6

fetching from, 6.2.2.3

guidelines for implicit, 6.2.1.2

implicit, 1.2.10.1

opening explicit, 6.2.2.2

packaged, 9.9

parameterized, 6.4.2

REF CURSOR variables, 6.5

RETURN clause, 9.9

scope rules for explicit, 6.2.2.1

SYS_REFCURSOR type, 11.10.8

variables, 6.5

D

data abstraction

understanding PL/SQL, 1.2.10

database character set, 3.1.3

database triggers, 1.3.1.3

autonomous, 6.8.4

datatypes

BFILE, 3.1.4.1

BINARY_INTEGER, 3.1.1.1

BLOB, 3.1.4.2

BOOLEAN, 3.1.5.1

CHAR, 3.1.2.1

CLOB, 3.1.4.3

DATE, 3.1.6.1

explicit conversion, 3.3.1

families, 3.1

implicit conversion, 3.3.2

INTERVAL DAY TO SECOND, 3.1.6.6

INTERVAL YEAR TO MONTH, 3.1.6.5

LONG, 3.1.2.2

LONG RAW, 3.1.2.2

national character, 3.1.3

NCHAR, 3.1.3.2

NCLOB, 3.1.4.4

NUMBER, 3.1.1.3

NVARCHAR2, 3.1.3.3

PLS_INTEGER, 3.1.1.4

PL/SQL, 3

RAW, 3.1.2.3

RECORD, 5.1

REF CURSOR, 6.5.1

ROWID, 3.1.2.4

scalar versus composite, 3.1

TABLE, 5.3

TIMESTAMP, 3.1.6.2

TIMESTAMP WITH LOCAL TIME ZONE, 3.1.6.4

TIMESTAMP WITH TIME ZONE, 3.1.6.3

UROWID, 3.1.2.4

VARCHAR2, 3.1.2.5

VARRAY, 5.1.1.2, 5.3

DATE datatype, 3.1.6.1

dates

converting, 3.3.4

TO_CHAR default format, 3.3.4

datetime

arithmetic, 3.1.7

datatypes, 3.1.6

literals, 2.1.3.5

DAY

datatype field, 3.1.6

DBMS_ALERT package, 9.7.1

DBMS_DB_VERSION package

using with conditional compilation, 2.7.1.8

DBMS_DDL package

functions for hiding PL/SQL source code, A

limitations, A.1.2

obfuscating PL/SQL code, A

using, A.3.1

wrap functions, A.3

wrapping PL/SQL code, A

DBMS_OUTPUT package

displaying output, 1.2.9

displaying output from PL/SQL, 9.7.2

using PUT_LINE to display output, 1.2.2.2

DBMS_PIPE package, 9.7.3

DBMS_PREPROCESSOR package

using with conditional compilation, 2.7.2.2

DBMS_PROFILE package

gathering statistics for tuning, 11.4.1

DBMS_SQL package, 7.1

PARSE procedure, 8.9.4

replace with dynamic SQL, 7.1

upgrade to dynamic SQL, 11.7

using for a query, 7.1

DBMS_TRACE package

tracing code for tuning, 11.4.2

DBMS_WARNING package

controlling warning messages in PL/SQL, 10.9.3

dbmsupbin.sql script

interpreted compilation, 11.9.9

dbmsupgnv.sql script

for PL/SQL native compilation, 11.9.9

deadlocks

how handled by PL/SQL, 6.7.4

DEC

NUMBER subtype, 3.1.1.3.1

DECIMAL

NUMBER subtype, 3.1.1.3.1

declarations

collection, 5.3.1

constants, 1.2.2.4, 2.2.1

cursor variables, 6.5.3

exceptions in PL/SQL, 10.4.1

explicit cursor, 6.2.2.1

object in a PL/SQL block, 12.1.1

PL/SQL functions, 1.2.4

PL/SQL procedures, 1.2.4

PL/SQL subprograms, 1.2.4

PL/SQL variables, 1.2.5

restrictions, 2.2.6

subprograms, 8.5

using %ROWTYPE, 2.2.5

using %TYPE attribute, 2.2.4

using DEFAULT, 2.2.2

using NOT NULL constraint, 2.2.3

variables, 1.2.2.1, 2.2

declarative part

of function, 8.4

of PL/SQL block, 1.2.1

of procedure, 8.3

DECLARE

block structure, 1.2.1

for local declarations, 8.3

start of declarative part of a PL/SQL block, 13

syntax, 13

DECODE function

treatment of nulls, 2.6.4.1

DEFAULT keyword

for assignments, 2.2.2

DEFAULT option

FUNCTION, 13

RESTRICT_REFERENCES, 13

default parameter values, 8.6.4

default value

effect on %ROWTYPE declaration, 2.2.5

effect on %TYPE declaration, 2.2.4

DEFINE

limitations of use with wrap utility, A.1.2.1

definer's rights

privileges on subprograms, 8.9

DELETE method

collections, 5.8.8, 13

DELETE statement

links to examples, 13

syntax, 13

delimiters, 2.1.1

demos

PL/SQL, Preface

dense collections

arrays versus nested tables, 5.1.1.1

DETERMINISTIC hint

function syntax, 13

use with functions, 8.4

digits of precision, 3.1.1.3

displaying output

from PL/SQL, 1.2.2.2

setting SERVEROUTPUT, 1.2.9, 9.7.2

using DBMS_OUTPUT.PUT_LINE, 1.2.2.2

with DBMS_OUTPUT, 1.2.9

DISTINCT row operator, 6.1.3, 6.1.5

dot notation, 1.2.5.1

for collection methods, 5.8

for global variables, 4.3.6.3

for object attributes in PL/SQL, 12.2.1

for object methods in PL/SQL, 12.2.2

for package contents, 9.3.1

DOUBLE PRECISION

NUMBER subtype, 3.1.1.3.1

DUP_VAL_ON_INDEX exception, 10.3

dynamic dispatch, 8.8.1

dynamic SQL, 1.1.1, 7.1

building a dynamic multi-row query, 7.4.1

guidelines, 7.4

improving performance, 7.4.3

passing nulls, 7.4.7

passing object names as parameters, 7.4.4

RETURNING clause, 7.2

specifying parameter modes, 7.2.1

tips and traps, 7.4

tuning, 11.7

using bulk SQL, 7.3

USING clause, 7.2

using cursor attributes, 7.4.6

using database links, 7.4.8

using duplicate placeholders, 7.4.5

using EXECUTE IMMEDIATE statement, 7.2

using invoker rights, 7.4.9

using RESTRICT_REFERENCES, 7.4.10

using the semicolon, 7.4.2

dynamic string length

with OPEN FOR statement, 13

dynamic wrapping

DBMS_DDL package, A

new features, Preface

E

element types

collection, 5.3

ELSE clause

using, 4.2.2

ELSIF clause

using, 4.2.3

END

block structure, 1.2.1

end of a PL/SQL block, 13

end of executable part, 8.3

syntax, 13

END IF

end of IF statement, 4.2.1

END LOOP

end of LOOP statement, 4.3.5

error handling

in PL/SQL, 10

overview, 1.2.11

error messages

maximum length, 10.8.4

ERROR_CODE

BULK_EXCEPTIONS cursor attribute field, 11.5.1.3

using with SQLERRM, 11.5.1.3

ERROR_INDEX

BULK_EXCEPTIONS cursor attribute field, 11.5.1.3

evaluation

short-circuit, 2.6.1.2

EXCEPTION

block structure, 1.2.1

exception handling part, 8.3

exception-handling part of a block, 13

syntax in PL/SQL block, 13

exception definition

syntax, 13

exception handlers

OTHERS handler, 10.1

overview, 1.2.11

using RAISE statement in, 10.7, 10.8

using SQLCODE function in, 10.8.4

using SQLERRM function in, 10.8.4

WHEN clause, 10.8

EXCEPTION_INIT pragma

links to examples, 13

syntax, 13

using with RAISE_APPLICATION_ERROR, 10.4.4

with exceptions, 10.4.3

exception-handling part

of function, 8.4

of PL/SQL block, 1.2.1

of procedure, 8.3

exceptions

advantages of PL/SQL, 10.2

branching with GOTO, 10.8.3

catching unhandled in PL/SQL, 10.8.5

continuing after an exception is raised, 10.8.6.1

controlling warning messages, 10.9.2

declaring in PL/SQL, 10.4.1

definition, 13

handling in PL/SQL, 10

links to examples, 13

list of predefined in PL/SQL, 10.3

locator variables to identify exception locations, 10.8.6.3

OTHERS handler in PL/SQL, 10.8

PL/SQL compile-time warnings, 10.9

PL/SQL error condition, 10.1

PL/SQL warning messages, 10.9.1

predefined in PL/SQL, 10.3

propagation in PL/SQL, 10.6

raise_application_error procedure, 10.4.4

raised in a PL/SQL declaration, 10.8.1

raised in handlers, 10.8.2

raising in PL/SQL, 10.5

raising predefined explicitly, 10.5.1

raising with RAISE statement, 10.5.1

redeclaring predefined in PL/SQL, 10.4.5

reraising in PL/SQL, 10.7

retrying a transaction after, 10.8.6.2

scope rules in PL/SQL, 10.4.2

tips for handling PL/SQL errors, 10.8.6

user-defined in PL/SQL, 10.4

using EXCEPTION_INIT pragma, 10.4.3

using SQLCODE, 10.8.4

using SQLERRM, 10.8.4

using the DBMS_WARNING package, 10.9.3

using WHEN and OR, 10.8

WHEN clause, 10.8

executable part

of function, 8.4

of PL/SQL block, 1.2.1

of procedure, 8.3

EXECUTE IMMEDIATE statement, 7.2

links to examples, 13

syntax, 13

EXECUTE privilege

subprograms, 8.9.6

EXISTS method

collections, 5.8.1, 13

EXIT statement

early exit of LOOP, 4.3.6.4

links to examples, 13

stopping a loop, 4.3.1

syntax, 13

using, 4.3.2

where allowed, 4.3.1

EXIT-WHEN statement

overview, 1.2.6.2

using, 4.3.3

explicit cursors, 6.2.2

explicit datatype conversion, 3.3.1

explicit declarations

cursor FOR loop record, 6.3.6

expressions

Boolean, 2.6.2

CASE, 2.6.3, 2.6.3

examples, 13

PL/SQL, 2.6

static, 2.7.1.6

syntax, 13

EXTEND method

collections, 5.8.6, 13

extended rowids, 3.1.2.4.1

external

references, 8.9.2

routines, 8.11

subprograms, 8.11

F

FALSE value, 2.1.3.4

features, new, Preface

FETCH statement

links to examples, 13

syntax, 13

using explicit cursors, 6.2.2.3

with cursor variable, 6.5.4.3

fetching

across commits, 6.7.7

bulk, 11.5.2.1, 11.5.2.1

fields

of records, 5.1

file I/O, 9.7.5

FIRST method

collections, 5.8.4, 13

FLOAT

NUMBER subtype, 3.1.1.3.1

FOR loops

explicit cursors, 6.3.6

nested, 4.3.6.3

FOR UPDATE clause, 6.2.2.2

when to use, 6.7.7

FORALL statement

links to examples, 13

syntax, 13

using, 11.5.1

using to improve performance, 11.5.1

using with BULK COLLECT clause, 11.5.2.4

with rollbacks, 11.5.1.1

FOR-LOOP statement

syntax, 13

using, 4.3.6

formal parameters, 6.4.2

format

masks, 3.3.4

forward

declarations of subprograms, 8.5

references, 2.2.6

FOUND cursor attribute

explicit, 6.2.2.6

implicit, 6.2.1.1

FUNCTION

with CREATE statement, 1.2.8.1, 1.3.1.2, 8.1

function declaration

syntax, 13

functions

body, 8.4, 13

built-in, 2.9

calling, 8.4

creating, 1.2.8.1, 1.3.1.2, 8.1

declaration, 13

DETERMINISTIC hint, 8.4

in PL/SQL, 8

links to examples, 13

PARALLEL_ENABLE option, 8.4

parameters, 8.4

parts, 8.4

pipelined, 11.10

RETURN clause, 8.4

RETURN statement, 8.4.1

specification, 8.4

table, 11.10.1

understanding, 8.4

G

GOTO statement

branching into or out of exception handler, 10.8.3

label, 4.4.1

links to examples, 13

overview, 1.2.6.3

restrictions, 4.4.1.1

syntax, 13

using, 4.4.1

GROUP BY clause, 6.1.3

H

handlers

exception in PL/SQL, 10.1

handling errors

PL/SQL, 10

handling exceptions

PL/SQL, 10

raised in as PL/SQL declaration, 10.8.1

raised in handler, 10.8.2

using OTHERS handler, 10.8

handling of nulls, 2.6.4

hash tables

simulating with associative arrays, 5.2

hiding PL/SQL code, A

host arrays

bulk binds, 11.5.2.5

HOUR

datatype field, 3.1.6

HTF package, 9.7.4

HTP package, 9.7.4

hypertext markup language (HTML), 9.7.6

hypertext transfer protocol (HTTP), 1.1.6

UTL_HTTP package, 9.7.6

I

identifiers

forming, 2.1.2

maximum length, 2.1.2

quoted, 2.1.2.3

scope rules, 2.4

IF statement, 4.2

ELSE clause, 4.2.2

links to examples, 13

syntax, 13

using, 4.2.1

IF-THEN statement

using, 4.2.1

IF-THEN-ELSE statement

overview, 1.2.6.1

using, 4.2.2

IF-THEN-ELSEIF statement

using, 4.2.3

IMMEDIATE

with EXECUTE statement, 7.2

implicit cursors

attributes, 6.2.1.1

guidelines, 6.2.1.2

implicit datatype conversion, 3.3.2

implicit datatype conversions

performance, 11.3.1.6

implicit declarations

FOR loop counter, 4.3.6.3

IN comparison operator, 2.6.1.8

IN OUT parameter mode

subprograms, 8.6.3.3

IN parameter mode

subprograms, 8.6.3.1

INDEX BY

collection definition, 13

index-by tables

See associative arrays

INDICES OF clause

FORALL, 13

with FORALL, 11.5.1

infinite loops, 4.3.1

inheritance

and overloading, 8.8.1

initialization

collections, 5.4

objects in PL/SQL, 12.1.2

package, 9.4

using DEFAULT, 2.2.2

variable, 2.5

with NOT NULL constraint, 2.2.3

initialization parameters

PL/SQL compilation, 11.1

INSERT statement

links to examples, 13

syntax, 13

with a record variable, 5.11.2

INT

NUMBER subtype, 3.1.1.3.1

INTEGER

NUMBER subtype, 3.1.1.3.1

integer

BINARY, 3.1.1.1

PLS datatype, 3.1.1.4

inter-language calls, 8.11

interpreted compilation

dbmsupbin.sql script, 11.9.9

recompiling all PL/SQL modules, 11.9.9

INTERSECT set operator, 6.1.5

interval

arithmetic, 3.1.7

INTERVAL DAY TO SECOND datatype, 3.1.6.6

INTERVAL YEAR TO MONTH datatype, 3.1.6.5

intervals

datatypes, 3.1.6

INTO

SELECT INTO statement, 13

INTO clause

with FETCH statement, 6.5.4.3

INTO list

using with explicit cursors, 6.2.2.3

INVALID_CURSOR exception, 10.3

INVALID_NUMBER exception, 10.3

invoker's rights

advantages, 8.9.1

privileges on subprograms, 8.9

defining a procedure, 8.3

defining a procedure body, 8.3

IS A SET operator, 5.6

IS EMPTY operator, 5.6

IS NULL comparison operator, 2.6.1.5

expressions, 13

ISOLATION LEVEL parameter

READ COMMITTED, 13

SERIALIZABLE, 13

setting transactions, 13

ISOPEN cursor attribute

explicit, 6.2.2.6

implicit, 6.2.1.1

J

JAVA

use for calling external subprograms, 8.11

Java

call specs, 8.11

Java stored procedures

calling from PL/SQL, 8.11

JDeveloper with PL/SQL

Oracle By Example tutorial, Preface

K

keywords

in PL/SQL, D

list of PL/SQL, D

use in PL/SQL, 2.1.2.1

L

labels

block, 2.4

block structure, 13

exiting loops, 4.3.4

GOTO statement, 4.4.1

loops, 4.3.4

syntax, 13

LAG

SQL analytic function, 2.9

LANGUAGE

use for calling external subprograms, 8.11

language elements

of PL/SQL, 13

large object (LOB) datatypes, 3.1.4

LAST method

collections, 5.8.4, 13

LEVEL

pseudocolumn, 6.1.4

LEVEL parameter

with ISOLATION to set transactions, 13

lexical units

PL/SQL, 2.1

LIKE comparison operator, 2.6.1.6

expressions, 13

LIMIT clause

FETCH, 13

using to limit rows for a Bulk FETCH operation, 11.5.2.2

LIMIT method

collections, 5.8.3, 13

limitations

DBMS_DDL package, A.1.2

native compilation, 11.9.3.3

of PL/SQL programs, C

PL/SQL compiler, C

wrap utility, A.1.2

limits

on PL/SQL programs, C

literals

Boolean, 2.1.3.4

character, 2.1.3.2

datetime, 2.1.3.5

examples, 13

NCHAR string, 2.1.3.3

NUMBER datatype, 2.1.3.1

numeric, 2.1.3.1

numeric datatypes, 2.1.3.1

string, 2.1.3.3

syntax, 13

types of PL/SQL, 2.1.3

LOB (large object) datatypes, 3.1.4

lob locators, 3.1.4

local subprograms, 1.3.1.2

locator variables

used with exceptions, 10.8.6.3

LOCK TABLE statement

examples, 13

locking a table, 6.7.7

syntax, 13

locks

modes, 6.7

overriding, 6.7.7

transaction processing, 6.7

using FOR UPDATE clause, 6.7.7

logical operators, 2.6.1

logical rowids, 3.1.2.4

LOGIN_DENIED exception, 10.3

LONG datatype, 3.1.2.2

maximum length, 3.1.2.2

restrictions, 3.1.2.2

supported only for backward compatibility, 3.1.2.2

LONG RAW datatype, 3.1.2.2

converting, 3.3.5

maximum length, 3.1.2.2

supported only for backward compatibility, 3.1.2.2

LOOP statement

links to examples, 13

overview, 1.2.6.2

syntax, 13

using, 4.3.1

loops

counters, 4.3.6

dynamic ranges, 4.3.6.2

exiting using labels, 4.3.4

implicit declaration of counter, 4.3.6.3

iteration, 4.3.6.1

labels, 4.3.4

reversing the counter, 4.3.6

scope of counter, 4.3.6.3

M

maximum precision, 3.1.1.3

maximum size

CHAR value, 3.1.2.1

identifier, 2.1.2

LOB, 3.1.4

LONG RAW value, 3.1.2.2

LONG value, 3.1.2.2

NCHAR value, 3.1.3.2

NVARCHAR2 value, 3.1.3.3

Oracle error message, 10.8.4

RAW value, 3.1.2.3

VARCHAR2 value, 3.1.2.5

MEMBER OF operator, 5.6

membership test, 2.6.1.8

memory

avoid excessive overhead, 11.3.2

MERGE statement

syntax, 13

methods

collection, 5.8

MINUS set operator, 6.1.5

MINUTE

datatype field, 3.1.6

modularity, 1.2.8

packages, 9.2

MONTH

datatype field, 3.1.6

multilevel collections

using, 5.7

multi-line comments, 2.1.4.2

MULTISET EXCEPT operator, 5.5

MULTISET INTERSECT operator, 5.5

MULTISET UNION operator, 5.5

N

NAME

for calling external subprograms, 8.11

NAME parameter

setting transactions, 13

transactions, 6.7.6

name resolution, 2.3

differences between PL/SQL and SQL, B.3

global and local variables, B.1

inner capture in DML statements, B.5

overriding in subprograms, 8.9.5

qualified names and dot notation, B.2

qualifying references to attributes and methods, B.6

understanding, B.1

understanding capture, B.4

with synonyms, 8.9.5

names

explicit cursor, 6.2.2.1

qualified, 2.3

savepoint, 6.7.3

variable, 2.3

naming conventions

PL/SQL, 2.3

national character datatypes, 3.1.3

national character set, 3.1.3

National Language Support (NLS), 3.1.3

native compilation

and Real Application Clusters, 11.9.3.2

certified compilers in installation guides, 11.9.1

creating databases for, 11.9.8

dbmsupgnv.sql script, 11.9.9

dependencies, 11.9.3.1

enhancements, Preface

how it works, 11.9.3

initialization parameters, 11.9.5

invalidation, 11.9.3.1

limitations, 11.9.3.3

modifying databases for, 11.9.9

new and updated components, Preface

Oracle Metalink, 11.9

Oracle Technology Network, 11.9

performance gains, 11.9.2

PL/SQL code, 11.9

plsql_code_type column, 11.9.7

prerequirements, 11.9.1

revalidation, 11.9.3.1

setting up, 11.9.7

setting up databases, 11.9.8

setting up library subdirectories, 11.9.6

shared libraries, 11.9.3

spnc_commands file, 11.9.4

testing, 11.9.1

understanding, 11.9.1

using, 11.9.7

utlirp.sql script, 11.9.7

utlrp.sql script, 11.9.9

native dynamic SQL. See dynamic SQL

native execution

compiling PL/SQL procedures for, 11.9

NATURAL

BINARY_INTEGER subtype, 3.1.1.1.1

NATURAL and NATURALN subtypes, 3.1.1.1.1

NATURALN

BINARY_INTEGER subtype, 3.1.1.1.1

NCHAR datatype, 3.1.3.2

NCLOB datatype, 3.1.4.4

nested collections, 5.7

nested cursors

using, 6.6

nested tables

manipulating in PL/SQL, 12.4

sets of values, 5.1.1.1

syntax, 13

transforming with operators, 5.5

understanding, 5.1.1

versus associative arrays, 5.2.1

versus varrays, 5.2.2

nesting

block, 1.2.1, 1.2.1

FOR loops, 4.3.6.3

record, 5.10

new features, Preface

PL/SQL information on Oracle Technology Network, Preface

NEXT method

collections, 5.8.5, 13

NEXTVAL

pseudocolumn, 6.1.4

nibble

half a byte, 3.3.5

NLS (National Language Support), 3.1.3

NLS_LENGTH_SEMANTICS initialization parameter

use with conditional compilation, 2.7.1.5

NO COPY hint

FUNCTION, 13

NO_DATA_FOUND exception, 10.3

NOCOPY compiler hint

for tuning, 11.8

restrictions on, 11.8.1

non-blank-padding semantics, 3.4.2

NOT logical operator

treatment of nulls, 2.6.4.1

NOT NULL

declaration, 13

NOT NULL constraint

effect on %ROWTYPE declaration, 2.2.5

effect on %TYPE declaration, 2.2.4

restriction on explicit cursors, 6.2.2.1

using in collection declaration, 5.3.1

using in variable declaration, 2.2.3

NOT NULL option

record definition, 13

NOT_LOGGED_ON exception, 10.3

notation

positional versus named, 8.6.2

NOTFOUND cursor attribute

explicit, 6.2.2.6

implicit, 6.2.1.1

NOWAIT option

LOCK TABLE, 13

NOWAIT parameter

using with FOR UPDATE, 6.7.7

NVARCHAR2 datatype, 3.1.3.3

null handling, 2.6.4

in dynamic SQL, 7.4.7

NULL statement

links to examples, 13

syntax, 13

using, 4.4.2

using in a procedure, 8.3

NUMBER datatype, 3.1.1.3

range of literals, 2.1.3.1

range of values, 3.1.1.3

NUMBER subtypes, 3.1.1.3.1

NUMERIC

NUMBER subtype, 3.1.1.3.1

numeric literals, 2.1.3.1

PL/SQL datatypes, 2.1.3.1

NVL function

treatment of nulls, 2.6.4.1

O

obfuscating

PL/SQL code, A

understanding, A.1

obfuscation

CREATE_WRAPPED function, A.3

DBMS_DDL package, A.3

hiding PL/SQL code, A

limitations, A.1.2

recommendations, A.1.1

tips, A.1.1

understanding, A.1

using DBMS_DDL CREATE_WRAPPED function, A.3.1

viewing source, A.1, A.3.1

WRAP function, A.3

wrap utility, A.2

object constructors

calling in PL/SQL, 12.2.2

passing parameters to in PL/SQL, 12.2.2

object methods

calling in PL/SQL, 12.2.2

object type declaration

syntax, 13

object types

declaring in a PL/SQL block, 12.1.1

defining in PL/SQL, 12.1

initializing in PL/SQL, 12.1, 12.1.2

overview, 1.2.10.4

using with invoker's rights subprograms, 8.9.10

using with PL/SQL, 12

using with PL/SQL collections, 12.4

object-oriented programming

with PL/SQL, 12

ONLY parameter

with READ to set transactions, 13

on-the-fly wrapping

DBMS_DDL package, A

new features, Preface

OPEN FOR statement

dynamic string length, 13

OPEN statement

explicit cursors, 6.2.2.2

links to examples, 13

syntax, 13

OPEN-FOR statement, 6.5.4.1

links to examples, 13

syntax, 13

OPEN-FOR-USING statement

syntax, 13

operators

comparison, 2.6.1.3

logical, 2.6.1

precedence, 2.6

relational, 2.6.1.4

optimizing

PL/SQL programs, 11.2

OR keyword

using with EXCEPTION, 10.8

Oracle By Example

PL/SQL new features, Preface

Oracle Metalink

native compilation, 11.9

spnc_commands, 11.9.4

Oracle Technology Network

best practices, 1, 11

code samples, Preface, 1

information on PL/SQL new features, Preface

native compilation, 11.9

SQL injection, 7.4, 13

table function examples, 11.10

table functions, 11.10

tuning information, 11

order of evaluation, 2.6, 2.6.1.1

OTHERS clause

exception handling, 13

OTHERS exception handler, 10.1, 10.8

OTN

technical articles, Preface

OUT parameter mode

subprograms, 8.6.3.2

overloading

and inheritance, 8.8.1

guidelines, 8.7.1

packaged subprograms, 9.5

restrictions, 8.7.2

subprogram names, 8.7

P

PACKAGE

with SQL CREATE statement, 1.2.8.2, 9.1

PACKAGE BODY

with SQL CREATE statement, 1.2.8.2, 9.1

package declaration

syntax, 13

packaged cursors, 9.9

packaged subprograms, 1.3.1.2

packages

advantages, 9.2

bodiless, 9.3

body, 9.1, 9.4, 13

call specification, 9.1

calling subprograms, 9.3.1

contents of, 9.1.1

creating, 1.2.8.2, 9.1

cursor specifications, 9.9

cursors, 9.9

declaration, 13

dot notation, 9.3.1

examples of features, 9.5

global variables, 9.5

guidelines for writing, 9.8

hidden declarations, 9.1

initializing, 9.4

links to examples, 13

modularity, 9.2

overloading subprograms, 9.5

overview, 1.2.8.2

overview of Oracle supplied, 9.7

private versus public objects, 9.5.1

product-specific, 9.7

product-specific for use with PL/SQL, 1.1.6

referencing, 9.3.1

restrictions on referencing, 9.3.1.1

scope, 9.3

serially reusable, 13

specification, 9.1, 13

specifications, 9.3

STANDARD package, 9.6

understanding, 9.1

visibility of contents, 9.1

PARALLE_ENABLE option

FUNCTION, 13

PARALLEL_ENABLE option

use with functions, 8.4

parameter passing

by reference, 8.13

by value, 8.13

in dynamic SQL, 7.2.1

parameters

actual, 6.4.2

actual versus formal, 8.6.1

aliasing, 8.13

cursor, 6.4.2

default values, 8.6.4

formal, 6.4.2

IN mode, 8.6.3.1

IN OUT mode, 8.6.3.3

in PL/SQL subprograms, 8.3

modes, 8.6.3

OUT mode, 8.6.3.2

summary of modes, 8.6.3.4

parentheses, 2.6

pattern matching, 2.6.1.6

performance

avoid memory overhead, 11.3.2

avoiding problems, 11.3

improving with PL/SQL, 1.1.2

performance. See also tuning

physical rowids, 3.1.2.4

pipe, 9.7.3

PIPE ROW statement

for returning rows incrementally, 11.10.4

PIPELINED

function option, 11.10.2, 13

pipelined functions

exception handling, 11.10.11

fetching from results of, 11.10.7

for querying a table, 11.10.1

overview, 11.10.1

passing data with cursor variables, 11.10.8

performing DML operations inside, 11.10.9

performing DML operations on, 11.10.10

returning results from, 11.10.4

transformation of data, 11.10

transformations, 11.10.3

writing, 11.10.2

pipelines

between table functions, 11.10.5

returning results from table functions, 11.10.4

support collection types, 11.10.2

using table functions, 11.10.3

writing table functions, 11.10.2

pipelining

definition, 11.10.1

placeholders

duplicate, 7.4.5

PLS_INTEGER datatype, 3.1.1.4

compared to BINARY_INTEGER, Preface

new features, Preface

overflow condition, 3.1.1.4

range update, Preface

PL/SQL

advantages, 1.1

anonymous blocks, 1.2.1

architecture, 1.3

assigning Boolean values,