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, 2.5.1

assigning query result to variable, 2.5.2

assigning values to variables, 2.5

best practices information on OTN, Preface

blocks, 13

structure, 1.2.1

CASE expressions, 2.6.3

character sets, 2.1

collection types, 5

collections

overview, 1.2.10.2

comments, 2.1.4

comparisons, 2.6

compiler limitations, C

compiler parameter settings, 11.1, 11.2, 11.9.7

compiler parameters, 11.1

compile-time warnings, 10.9

compiling PL/SQL code for native execution, 11.9

conditional compilation, 1.2.7, 2.7

constants, 1.2.2

control structures, 1.2.6, 4.1

creating Web applications and pages, 2.8

data abstraction, 1.2.10

datatypes, 3

debugging with JDeveloper tutorial on Oracle by Example, Preface

declarations

constants, 2.2

demos, Preface

displaying output, 1.2.2.2, 9.7.2

engine, 1.3

environment, 9.6

error handling

overview, 1.2.11

errors, 10

exceptions, 10

expressions, 2.6

functions, 2.9, 8

hiding or obfuscating source code, A

input data, 1.2.9

keywords, D

lexical units, 2.1

limitations of programs, C

limits on programs, C

literals, 2.1.3

logical operators, 2.6.1

name resolution, B.1

naming conventions, 2.3

new features, Preface

Oracle By Example tutorial, Preface

output data, 1.2.9

performance

advantage, 1.1.2

performance problems, 11.3

portability, 1.1.4

procedural aspects, 1.2

procedures, 8

profiling and tracing programs, 11.4

querying data, 6.3

recompiling, 10.9.2

records

overview, 1.2.10.3

reserved words, D

sample programs, Preface

scope of identifiers, 2.4

Server Pages (PSPs), 2.8.2

SQL support, 6.1

statements, 13

subprograms, 8

calling, 1.3.1.2

syntax of language elements, 13

transaction processing, 6.7

tuning code, 11.2.1

tuning computation-intensive programs, 11.6

tuning dynamic SQL programs, 11.7

using dynamic SQL, 7.1

using NOCOPY for tuning, 11.8

using transformation pipelines, 11.10

variables, 1.2.2

warning messages, 10.9.1

Web applications, 2.8.1

writing reusable code, 1.2.8

PLSQL datatypes

numeric literals, 2.1.3.1

PLSQL_CCFLAGS initialization parameter

conditional compilation, 2.7.1.7, 2.7.1.7

use with conditional compilation, 2.7.1.5

plsql_code_type column

native compilation, 11.9.7

PLSQL_CODE_TYPE initialization parameter

setting PL/SQL native compilation, 11.9.5.3

use with conditional compilation, 2.7.1.5

PLSQL_DEBUG initialization parameter

use with conditional compilation, 2.7.1.5

PLSQL_LINE flag

use with conditional compilation, 2.7.1.5

PLSQL_NATIVE_LIBRARY_DIR initialization parameter

for native compilation, 11.9.5.1

Real Application Clusters with native compilation, 11.9.3.2

PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT initialization parameter

for native compilation, 11.9.5.2

PLSQL_OPTIMIZE_LEVEL initialization parameter

optimizing PL/SQL programs, 11.2

use with conditional compilation, 2.7.1.5

PLSQL_UNIT flag

use with conditional compilation, 2.7.1.5

PLSQL_WARNINGS initialization parameter, 10.9

use with conditional compilation, 2.7.1.5

pointers

REF CIRSOR, 6.5.1

portability, 1.1.4

POSITIVE

BINARY_INTEGER subtype, 3.1.1.1.1

POSITIVE and POSITIVEN subtypes, 3.1.1.1.1

POSITIVEN

BINARY_INTEGER subtype, 3.1.1.1.1

PRAGMA

compiler directive with AUTONOMOUS_TRANSACTION, 13, 13

compiler directive with AUTONOMOUS_TRANSACTION for procedures, 13

compiler directive with EXCEPTION_INIT, 13

compiler directive with RESTRICT_REFERENCES, 13

compiler directive with SERIALLY_REUSABLE, 13, 13

pragmas

AUTONOMOUS_TRANSACTION, 6.8.2

compiler directives, 10.4.3

EXCEPTION_INIT, 10.4.3

RESTRICT_REFERENCES, 6.8.5, 7.4.10, 8.12

precedence, operator, 2.6

precision of digits

specifying, 3.1.1.3

predefined exceptions

raising explicitly, 10.5.1

redeclaring, 10.4.5

predicates, 6.1.5

PRIOR method

collections, 5.8.5, 13

PRIOR row operator, 6.1.4

private objects

packages, 9.5.1

PROCEDURE

with CREATE statement, 1.2.8.1, 1.3.1.2, 8.1

procedure declaration

syntax, 13

procedures

body, 8.3, 13

calling, 1.2.8.2, 8.3

creating, 1.2.8.1, 1.3.1.2, 8.1

declaration, 13

in PL/SQL, 8

links to examples, 13

parts, 8.3

recompiling with ALTER PROCEDURE, 10.9.3

specification, 8.3

understanding PL/SQL, 8.3

productivity, 1.1.3

program units, 1.2.8

PROGRAM_ERROR exception, 10.3

propagation

exceptions in PL/SQL, 10.6

pseudocolumns

CURRVAL, 6.1.4

LEVEL, 6.1.4

NEXTVAL, 6.1.4

ROWID, 6.1.4

ROWNUM, 6.1.4

SQL, 6.1.4

UROWID, 6.1.4

use in PL/SQL, 6.1.4

public objects

packages, 9.5.1

purity rules, 8.12

PUT_LINE

displaying output with, 1.2.2.2

Q

qualifiers

using subprogram names as, 2.3

when needed, 2.3, 2.4

query work areas, 6.5.1

querying data

BULK COLLECT clause, 6.3.2

cursor FOR loop, 6.3.3

implicit cursor FOR loop, 6.3.5

looping through multiple rows, 6.3.3

maintaining, 6.4.2

performing complicated processing, 6.3.4

SELECT INTO, 6.3.1

using explicit cursors, 6.3.4

using implicit cursors, 6.3.5

with PL/SQL, 6.3

work areas, 6.5.1

quoted identifiers, 2.1.2.3

R

RAISE statement

exceptions in PL/SQL, 10.5.1

links to examples, 13

syntax, 13

using in exception handler, 10.7, 10.8

raise_application_error procedure

for raising PL/SQL exceptions, 10.4.4

raising an exception

in PL/SQL, 10.5

range operator, 4.3.6

RAW datatype, 3.1.2.3

converting, 3.3.5

maximum length, 3.1.2.3

READ ONLY parameter

setting transactions, 13

transactions, 6.7.6

READ WRITE parameter

setting transactions, 13

readability, 2.1

with NULL statement, 4.4.2

read-only transaction, 6.7.6

REAL

NUMBER subtype, 3.1.1.3.1

Real Application Clusters

and PL/SQL native compilation, 11.9.3.2

with native compilation, 11.9.3.2

recompiling

functions, packages, and procedures, 10.9.2

RECORD datatype, 5.1

record definition

syntax, 13

records

%ROWTYPE, 6.3.6

assigning values, 5.11

bulk-binding collections of, 5.11.5

comparing, 5.11.1

declaring, 5.10

defining, 5.10

definition, 1.2.5.2, 13

group of fields, 5.1.2

group of related data items, 5.1

implicit declaration, 6.3.6

inserting, 5.11.2

links to examples, 13

manipulating, 5.10.1

nesting, 5.10

overview, 1.2.10.3

passing as parameters, 5.10.1

restriction on assignments, 5.11

restrictions on inserts and updates of, 5.11.4

returning into, 5.11.3

ROWTYPE attribute, 5.1.2

updating, 5.11.3

using as function return values, 5.10.1

recursion

using with PL/SQL subprograms, 8.10

REF CURSOR datatype, 6.5.1

cursor variables, 6.5

defining, 6.5.3

using with cursor subqueries, 6.6.3

REF CURSOR variables

as parameters to table functions, 11.10.8

predefined SYS_REFCURSOR type, 11.10.8

REF function

manipulating objects in PL/SQL, 12.2.4

reference datatypes, 3.1

references

external, 8.9.2

resolving external, 8.9.4

referencing

collections, 5.4

referencing elements

allowed subscript ranges, 5.4.1

regular expression functions

REGEXP_LIKE, 6.2.2.3

relational operators, 2.6.1.4

remote access indicator, 2.3

REPEAT UNTIL structure

PL/SQL equivalent, 4.3.5

REPLACE function

treatment of nulls, 2.6.4.1

reraising an exception, 10.7

reserved words

list of PL/SQL, D

PL/SQL, D

syntactic meaning in PL/SQL, 2.1.2.1

resolution

name, 2.3

references to names, B.1

RESTRICT_REFERENCES pragma, 8.12

links to examples, 13

syntax, 13

using with autonomous functions, 6.8.5

using with dynamic SQL, 7.4.10

restricted rowids, 3.1.2.4.1

restrictions

cursor expressions, 6.6.1

cursor variables, 6.5.7

overloading subprograms, 8.7.2

result sets, 6.2.2.2

RETURN clause

cursor, 9.9

cursor declaration, 13

FUNCTION, 13

functions, 8.4

RETURN statement

functions, 8.4.1

links to examples, 13

syntax, 13

return types

overloading, 8.7.2

REF CURSOR, 6.5.3

return values

functions, 8.4

RETURNING clause

links to examples, 13

syntax, 13

with a record variable, 5.11.3

with dynamic SQL, 7.2

returns

bulk, 11.5.2.3

REUSE SETTINGS clause

with compiler parameters, 11.1

REVERSE

with LOOP counter, 4.3.6

REVERSE option

LOOP, 13

RNDS option

RESTRICT_REFERENCES, 13

RNPS option

RESTRICT_REFERENCES, 13

ROLLBACK statement, 6.7.2

effect on savepoints, 6.7.3

links to examples, 13

syntax, 13

rollbacks

implicit, 6.7.4

of FORALL statement, 11.5.1.1

routines

external, 8.11

row locks

with FOR UPDATE, 6.7.7

row operators, 6.1.5

ROWCOUNT cursor attribute

explicit, 6.2.2.6

implicit, 6.2.1.1

ROWID

pseudocolumn, 6.1.4

ROWID datatype, 3.1.2.4

rowids, 3.1.2.4

ROWIDTOCHAR function, 6.1.4

ROWNUM

pseudocolumn, 6.1.4

ROWTYPE attribute

declaring, 1.2.5.2

effect of default value, 2.2.5

effect of NOT NULL constraint, 2.2.5

inherited properties from columns, 13

links to examples, 13

records, 5.10

syntax, 13

using, 2.2.5

with SUBTYPE, 3.2.1

ROWTYPE_MISMATCH exception, 10.3

RPC (remote procedure call)

and exceptions, 10.6

rules

purity, 8.12

run-time errors

PL/SQL, 10

S

samples

PL/SQL, Preface

SAVE EXCEPTIONS clause

FORALL, 13

SAVEPOINT statement, 6.7.3

links to examples, 13

syntax, 13

savepoints

reusing names, 6.7.3

scalar datatypes, 3.1

scale

specifying, 3.1.1.3

scientific notation, 2.1.3.1

scope, 2.4

collection, 5.3

definition, 2.4

exceptions in PL/SQL, 10.4.2

explicit cursor, 6.2.2.1

explicit cursor parameter, 6.2.2.1

identifier, 2.4

loop counter, 4.3.6.3

package, 9.3

searched CASE expression, 2.6.3.2

searched CASE statement, 4.2.4.1

SECOND

datatype field, 3.1.6

security

SQL injection, 7.4, 13

SELECT INTO statement

links to examples, 13

returning one row, 6.3.1

syntax, 13

selector, 2.6.3.1

SELF_IS_NULL exception, 10.3

semantics

assignment, 3.4.1

blank-padding, 3.4.2

non-blank-padding, 3.4.2

string comparison, 3.4.2

separators, 2.1.1

sequences

CURRVAL and NEXTVAL, 6.1.4

SERIALLY_REUSABLE pragma

examples, 13

syntax, 13

use with packages, 13

Server Pages (PSPs)

PL/SQL, 2.8.2

SERVEROUTPUT

displaying output from PL/SQL, 1.2.9

setting ON to display output, 9.7.2

SET clause

UPDATE, 13

set operators, 6.1.5

SET TRANSACTION statement, 6.7.6

links to examples, 13

syntax, 13

short-circuit evaluation, 2.6.1.2

side effects, 8.6.3

controlling, 8.12

SIGNTYPE

BINARY_INTEGER subtype, 3.1.1.1.1

SIGNTYPE subtype, 3.1.1.1.1

simple CASE expression, 2.6.3.1

single-line comments, 2.1.4.1

size limit

varrays, 5.3

SMALLINT

NUMBER subtype, 3.1.1.3.1

spaces

where allowed, 2.1

sparse collections

nested tables versus arrays, 5.1.1.1

specification

call, 9.1

cursor, 9.9

functions, 8.4

package, 9.3

packages, 13

procedure, 8.3

spnc_commands

Oracle Metalink, 11.9.4

SQL

comparisons operators, 6.1.5

data manipulation operations, 6.1.1

define variables and data manipulation statements, 6.1.1

DML operations, 6.1.1

dynamic, 1.1.1, 7.1

exceptions raised by data manipulation statements, 6.1.1

injection, 7.4, 13

issuing from PL/SQL, 6.1

no rows returned with data manipulation statements, 6.1.1

pseudocolumns, 6.1.4

static, 1.1.1

SQL cursor

links to examples, 13

syntax, 13

SQL injection, 7.4, 13

Oracle Technology Network, 7.4, 13

SQLCODE function

links to examples, 13

syntax, 13

using with exception handlers, 10.8.4

SQLERRM function

links to examples, 13

syntax, 13

using with BULK_EXCEPTIONS ERROR_CODE field, 11.5.1.3

using with exception handlers, 10.8.4

standalone subprograms, 1.3.1.2

STANDARD package

defining PL/SQL environment, 9.6

START WITH clause, 6.1.4

statement terminator, 13

statements

assignment, 13

CASE, 13

CLOSE, 6.2.2.5, 6.5.4.4, 13

COMMIT, 13

DELETE, 13

EXECUTE IMMEDIATE, 7.2, 13

EXIT, 13

FETCH, 6.2.2.3, 6.5.4.3, 13

FORALL, 11.5.1, 13

FOR-LOOP, 13

GOTO, 13

IF, 13

INSERT, 13

LOCK TABLE, 13

LOOP, 13

MERGE, 13

NULL, 13

OPEN, 6.2.2.2, 13

OPEN-FOR, 6.5.4.1, 13

OPEN-FOR-USING, 13

PL/SQL, 13

RAISE, 13

RETURN, 13

ROLLBACK, 13

SAVEPOINT, 13

SELECT INTO, 13

SET TRANSACTION, 13

UPDATE, 13

WHILE-LOOP, 13

static constants

conditional compilation, 2.7.1.6.4

static expressions

boolean, 2.7.1.6

PLS_INTEGER, 2.7.1.6

use with conditional compilation, 2.7.1.6

VARCHAR2, 2.7.1.6

static SQL, 1.1.1

STEP clause

equivalent in PL/SQL, 4.3.6.1

STORAGE_ERROR exception, 10.3

raised with recursion, 8.10.1

store tables, 5.2.2

stored subprograms

in Oracle database, 1.3.1.2

string comparison semantics, 3.4.2

string literals, 2.1.3.3

NCHAR, 2.1.3.3

STRING subtype, 3.1.2.5.1

subprograms

actual versus formal parameters, 8.6.1

advantages in PL/SQL, 8.2

AUTHID clause, 8.9, 8.9.2

calling external, 8.11

calling from SQL*Plus, 1.3.1.2, 1.3.1.2

calling with parameters, 8.6.2

controlling side effects, 8.12

current user during execution, 8.9.3

declaring nested, 8.5

declaring PL/SQL, 1.2.4

default parameter modes, 8.6.4

definer's rights, 8.9

EXECUTE privilege, 8.9.6

external references, 8.9.2

granting privileges on invoker's rights, 8.9.6

guidelines for overloading, 8.7.1

how calls are resolved, 8.8

IN OUT parameter mode, 8.6.3.3

IN parameter mode, 8.6.3.1

in PL/SQL, 8

invoker's rights, 8.9

local, 1.3.1.2

mixed notation parameters, 8.6.2

named parameters, 8.6.2

OUT parameter mode, 8.6.3.2

overloading and inheritance, 8.8.1

overloading names, 8.7

overriding name resolution, 8.9.5

packaged, 1.3.1.2

parameter aliasing, 8.13

parameter modes, 8.6.3, 8.6.3.4

passing parameter by value, 8.13

passing parameters, 8.6

passing parameters by reference, 8.13

positional parameters, 8.6.2

procedure versus function, 8.4

recursive, 8.10.1

resolving external references, 8.9.4

restrictions on overloading, 8.7.2

roles with invoker's rights, 8.9.7

standalone, 1.3.1.2

stored, 1.3.1.2

understanding PL/SQL, 8.1

using database links with invoker's rights, 8.9.9

using recursion, 8.10

using triggers with invoker's rights, 8.9.8

using views with invoker's rights, 8.9.8

subqueries

correlated, 6.4.1

using in PL/SQL, 6.4

SUBSCRIPT_BEYOND_COUNT exception, 10.3

SUBSCRIPT_OUTSIDE_LIMIT exception, 10.3

substitutability of object types

with overloading, 8.8.1

SUBSTR function

using with SQLERRM, 10.8.4

subtypes

BINARY_INTEGER, 3.1.1.1.1

CHARACTER, 3.1.2.1.1

compatibility, 3.2.2.1

constrained versus unconstrained, 3.2

defining, 3.2.1

NATURAL and NATUARALN, 3.1.1.1.1

NUMBER, 3.1.1.3.1

PL/SQL, 3.2

POSITIVE and POSITIVEN, 3.1.1.1.1

SIGNTYPE, 3.1.1.1.1

STRING, 3.1.2.5.1

using, 3.2.2

VARCHAR, 3.1.2.5.1

synonyms

name resolution, 8.9.5

syntax

assignment statement, 13

AUTONOMOUS_TRANSACTION pragma, 13

BEGIN, 13

block declaration, 13

CASE statement, 13

CLOSE statement, 13

collection definition, 13

collection method, 13

comment, 13

constant, 13

cursor attributes, 13

cursor declaration, 13

cursor variables, 13

DECLARE, 13

DELETE statement, 13

diagram reading, 13

END, 13

EXCEPTION, 13

exception definition, 13

EXCEPTION_INIT pragma, 13

EXECUTE IMMEDIATE statement, 13

EXIT statement, 13

expression, 13

FETCH statement, 13

FORALL statement, 13

FOR-LOOP statement, 13

function declaration, 13

GOTO statement, 13

IF statement, 13

INSERT statement, 13

label, 13

literal declaration, 13

LOCK TABLE statement, 13

LOOP statement, 13

NULL statement, 13

object type declaration, 13

of PL/SQL language elements, 13

OPEN statement, 13

OPEN-FOR statement, 13

OPEN-FOR-USING statement, 13

package declaration, 13

procedure declaration, 13

RAISE statement, 13

record definition, 13

RESTRICT_REFERENCES pragma, 13

RETURN statement, 13

RETURNING clause, 13

ROLLBACK statement, 13

ROWTYPE attribute, 13

SAVEPOINT statement, 13

SELECT INTO statement, 13

SERIALLY_REUSABLE pragma, 13

SET TRANSACTION statement, 13

SQL cursor, 13

SQLCODE function, 13

SQLERRM function, 13

TYPE attribute, 13

UPDATE statement, 13

variable, 13

WHILE-LOOP statement, 13

SYS_INVALID_ROWID exception, 10.3

SYS_REFCURSOR type, 11.10.8

T

TABLE datatype, 5.3

table functions

examples on Oracle Technology Network, 11.10

exception handling, 11.10.11

fetching from results of, 11.10.7

for querying, 11.10.1

information on Oracle Technology Network, 11.10

organizing multiple calls to, 11.10.6

passing data with cursor variables, 11.10.8

performing DML operations inside, 11.10.9

performing DML operations on, 11.10.10

pipelining data between, 11.10.5

returning results from, 11.10.4

setting up transformation pipelines, 11.10

using transformation pipelines, 11.10.3

writing transformation pipelines, 11.10.2

TABLE operator

manipulating objects in PL/SQL, 12.3.1

tabs, 2.1

terminator, statement, 2.1.1

ternary operators, 2.6

THEN clause

using, 4.2.1

with IF statement, 4.2.1

TIMEOUT_ON_RESOURCE exception, 10.3

TIMESTAMP datatype, 3.1.6.2

TIMESTAMP WITH LOCAL TIME ZONE datatype, 3.1.6.4

TIMESTAMP WITH TIME ZONE datatype, 3.1.6.3

TIMEZONE_ABBR

datatype field, 3.1.6

TIMEZONE_HOUR

datatype field, 3.1.6

TIMEZONE_MINUTES

datatype field, 3.1.6

TIMEZONE_REGION

datatype field, 3.1.6

TOO_MANY_ROWS exception, 10.3

trailing blanks, 3.4.3

transactions, 6.1.2

autonomous in PL/SQL, 6.8

committing, 6.7.1

context, 6.8.2.2

ending properly, 6.7.5

processing in PL/SQL, 6.1.2, 6.7

properties, 6.7.6

read-only, 6.7.6

restrictions, 6.7.6.1

rolling back, 6.7.2

savepoints, 6.7.3

visibility, 6.8.2.3

triggers

as a stored PL/SQL subprogram, 1.3.1.3

autonomous, 6.8.4

TRIM method

collections, 5.8.7, 13

TRUE value, 2.1.3.4

TRUST option

RESTRICT_REFERENCES, 13

tuning

allocate large VARCHAR2 variables, 11.3.2.1

avoid memory overhead, 11.3.2

compiling PL/SQL code for native execution, 11.9

computation-intensive programs, 11.6

do not duplicate built-in functions, 11.3.1.4

dynamic SQL programs, 11.7

group related subprograms into a package, 11.3.2.2

guidelines for avoiding PL/SQL performance problems, 11.3

improve code to avoid compiler warnings, 11.3.2.4

information on Oracle Technology Network, 11

make function calls efficient, 11.3.1.2

make loops efficient, 11.3.1.3

make SQL statements efficient, 11.3.1.1

optimizing PL/SQL programs, 11.2

pin packages in the shared memory pool, 11.3.2.3

PL/SQL code, 11.2.1

profiling and tracing, 11.4

reducing loop overhead, 11.5

reorder conditional tests to put least expensive first, 11.3.1.5

use BINARY_FLOAT or BINARY_DOUBLE for floating-point arithmetic, 11.3.1.8

use PLS_INTEGER for integer arithmetic, 11.3.1.7

using DBMS_PROFILE and DBMS_TRACE, 11.4

using FORALL, 11.5.1

using NOCOPY, 11.8

using transformation pipelines, 11.10

TYPE attribute

declaring, 1.2.5.1

effect of default value, 2.2.4

effect of NOT NULL constraint, 2.2.4

inherited properties from column, 13

links to examples, 13

syntax, 13

using, 2.2.4

with SUBTYPE, 3.2.1

TYPE definition

associative arrays, 5.3

collection, 5.3

collection types, 5.3

nested tables, 5.3

RECORD, 5.10

REF CURSOR, 6.5.3

VARRAY, 5.3

U

unary operators, 2.6

underscores, 2.1.2

unhandled exceptions

catching, 10.8.5

propagating, 10.6

uninitialized object

how treated in PL/SQL, 12.1.2

UNION ALL set operator, 6.1.5

UNION set operator, 6.1.5

universal rowids, 3.1.2.4

UPDATE statement

links to examples, 13

syntax, 13

with a record variable, 5.11.3

URL (uniform resource locator), 9.7.6

UROWID

pseudocolumn, 6.1.4

UROWID datatype, 3.1.2.4

USE ROLLBACK SEGMENT parameter

setting transactions, 13

user-defined

exceptions in PL/SQL, 10.4

records, 5.1

subtypes, 3.2

USING clause

EXECUTE IMMEDIATE, 13

with dynamic SQL, 7.2

with EXECUTE IMMEDIATE, 13

with OPEN FOR statement, 13

UTF8 character encoding, 3.1.3.1

UTL_FILE package, 9.7.5

UTL_HTTP package, 9.7.6

UTL_SMTP package, 9.7.7

utlirp.sql script

for PL/SQL native compilation, 11.9.7

utlrp.sql script

for PL/SQL native compilation, 11.9.9

V

V$RESERVED_WORDS view

listing of reserved words and keywords, D

VALUE function, 12.2.2

VALUE_ERROR exception, 10.3

VALUES clause

INSERT, 13

VALUES OF clause, 11.5.1

FORALL, 13

VARCHAR subtype, 3.1.2.5.1

VARCHAR2 datatype, 3.1.2.5

differences with CHAR, 3.4

variables

assigning query result to, 2.5.2

assigning values, 1.2.2.2, 2.5

bind, 1.2.2.3, 13

declaring, 1.2.2.1, 2.2

declaring PL/SQL, 1.2.5

global, 9.5

host, 13

initializing, 2.5

links to examples, 13

passing as IN OUT parameter, 1.2.2.2

REF CURSOR datatype, 6.5

syntax, 13

understanding PL/SQL, 1.2.2

variable-size arrays (varrays)

understanding, 5.1.1

VARRAY datatype, 5.1.1.2, 5.3

varrays

size limit, 5.3

syntax, 13

TYPE definition, 5.3

understanding, 5.1.1

versus nested tables, 5.2.2

visibility

of package contents, 9.1

transaction, 6.8.2.3

versus scope, 2.4

W

warning messages

controlling PL/SQL, 10.9.2

Web applications

creating with PL/SQL, 2.8

Web server pages

creating with PL/SQL, 2.8

WHEN clause

exception handling, 13

exceptions, 10.8

using, 4.3.3

WHERE CURRENT OF clause

DELETE statement, 13

UPDATE, 13

WHILE-LOOP statement

overview, 1.2.6.2

syntax, 13

using, 4.3.5

wildcards, 2.6.1.6

WNDS option

RESTRICT_REFERENCES, 13

WNPS option

RESTRICT_REFERENCES, 13

words

reserved in PL/SQL, D

work areas

queries, 6.5.1

WRAP function

obfuscation, A.3

wrap utility

limitations, A.1.2

obfuscating PL/SQL code, A

obfuscation of PL/SQL code, A.2

running, A.2.2

wrapped

PL/SQL source code, A

wrapping

DBMS_DDL package, A.3

dynamically, Preface, A

limitations, A.1.2

obfuscating code, A

on-the-fly, Preface, A

recommendations, A.1.1

tips, A.1.1

understanding, A.1

viewing source, A.1, A.3.1

with CREATE_WRAPPED, A.3.1

WRITE parameter

with READ to set transactions, 13

Y

YEAR

datatype field, 3.1.6

Z

ZERO_DIVIDE exception, 10.3

ZONE

part of TIMESTAMP datatype, 3.1.6.3