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.2.9

. item separator, 2.1.1

<< label delimiter, 2.1.1

.. range operator, 2.1.1, 4.3.8

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

<, >, <=, and >= relational operators, 2.6.2.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, 11.4

actual parameters, 6.4.2

address

REF CURSOR, 6.5.1

advantages

PL/SQL, 1.1

AFTER triggers

auditing and, 9.12.1, 9.12.1

correlation names and, 9.5.1

specifying, 9.4.3

aggregate assignment, 2.2.5.1

aggregate functions

and PL/SQL, 6.1.3

aliases

using with a select list, 2.2.5.2

aliasing

for expression values in a cursor FOR loop, 6.3.6

parameters, 8.12

ALL row operator, 6.1.3, 6.1.5.3

ALL_PLSQL_OBJECT_SETTINGS view

PL/SQL compiler parameter settings, 12.1

ALTER PROCEDURE

using to recompile a procedure, 11.10.3

ALTER TABLE statement

DISABLE ALL TRIGGERS clause, 9.10

ENABLE ALL TRIGGERS clause, 9.9

ALTER TRIGGER statement

DISABLE clause, 9.10

ENABLE clause, 9.9

anonymous blocks, 1.2.1

ANSI/ISO SQL standard, 6.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

assignment operator, 1.2.2.2

assignment statement

links to examples, 13

syntax, 13

assignments

aggregate, 2.2.5.1

collection, 5.7

field, 5.14

IN OUT parameters, 1.2.2.2

records, 5.14

variables, 1.2.2.2

associative arrays

nested tables and, 5.2.1

sets of key-value pairs, 5.1.1.3

syntax, 13

understanding, 5.1.1

VARCHAR2 keys and globalization settings, 5.1.1.4

asynchronous operations, 10.10.1

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

auditing

triggers and, 9.12.1

AUTHID clause

specifying privileges for a subprogram, 8.8.2

using to specify privileges of invoker, 8.8

autonomous functions

invoking from SQL, 6.8.5

RESTRICT_REFERENCES pragma, 6.8.5

autonomous transactions

advantages, 6.8.1

avoiding errors, 6.8.3.4

comparison with nested transactions, 6.8.2.1

controlling, 6.8.3

in PL/SQL, 6.8

SQL%ROWCOUNT attribute, 6.2.1.1.4

autonomous triggers

using, 6.8.4

AUTONOMOUS_TRANSACTION pragma

defining, 6.8.2

links to examples, 13

syntax, 13

avoiding SQL injection, 7.4

B

basic loops, 4.3.1

BEFORE triggers

complex security authorizations, 9.12.3.7

correlation names and, 9.5.1

derived column values, 9.12.3.9

specifying, 9.4.3

BEGIN

start of executable PL/SQL block, 13

syntax, 13

BETWEEN clause

FORALL, 13

BETWEEN comparison operator, 2.6.2.7

expressions, 13

BFILE datatype, 3.2.1

BINARY_DOUBLE datatype, 3.1.1.3

BINARY_FLOAT and BINARY_DOUBLE datatypes

for computation-intensive programs, 12.8

BINARY_FLOAT datatype, 3.1.1.3

BINARY_INTEGER datatype

see PLS_INTEGER datatype

bind arguments

preventing SQL injection with, 7.4.2.1

bind variables, 1.2.2.3

binding

bulk, 12.7

variables, 12.7

BLOB datatype, 3.2.2

blocks

label, 2.4

links to examples, 13

nesting, 1.2.1, 1.2.1

PL/SQL

syntax, 13

structure, 1.2.1, 1.2.1

BODY

CREATE PACKAGE SQL statement, 1.2.8.2, 10.1, 13

CREATE TYPE SQL statement, 13

with SQL CREATE PACKAGE statement, 1.2.8.2, 10.1

body

cursor, 10.12

package, 10.6

packages, 13

Boolean

assigning values, 2.5.1

expressions, 2.6.3

literals, 2.1.3.4

BOOLEAN datatype, 3.1.3

bulk

fetches, 12.7.2.1

returns, 12.7.2.3

bulk binding, 12.7

limitations, 12.7

BULK clause

with COLLECT, 12.7.2

BULK COLLECT clause, 12.7.2

checking whether no results are returned, 12.7.2

FETCH, 13

retrieving DML results, 12.7.2.3

retrieving query results with, 12.7.2

returning multiple rows, 6.3.2

SELECT INTO, 13

using LIMIT clause, 12.7.2, 12.7.2.2

using ROWNUM pseudocolumn, 12.7.2

using SAMPLE clause, 12.7.2

using with FORALL statement, 12.7.2.4

BULK COLLECT INTO clause

in EXECUTE IMMEDIATE statement, 13

in RETURNING INTO clause, 13

bulk SQL

using to reduce loop overhead, 12.7

BULK_EXCEPTIONS cursor attribute

ERROR_CODE field, 12.7.1.3

ERROR_INDEX field, 12.7.1.3

example, 12.7.1.3

handling FORALL exceptions, 12.7.1.3

using ERROR_CODE field with SQLERRM, 12.7.1.3

BULK_ROWCOUNT cursor attribute

affected by FORALL, 12.7.1.2

by-reference parameter passing, 8.12

by-value parameter passing, 8.12

C

CALL

SQL statement, 1.2.8.2

call specification, 10.1

calls

inter-language, 8.10

resolving subprogram, 8.7

subprograms, 8.5.2

CARDINALITY operator

for nested tables, 5.8

carriage returns, 2.1

CASE expressions, 2.6.4, 2.6.4

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.5

syntax, 13

using, 4.2.4

CASE_NOT_FOUND exception, 11.4

CATPROC.SQL script, 9.13.5

CHAR datatype, 3.1.2.1

differences with VARCHAR2, 3.1.2.1

character literals, 2.1.3.2

character sets

PL/SQL, 2.1

CHARACTER subtype, 3.1.2.1.1

character values

comparing, 3.1.2.1.4

CHECK constraint

triggers and, 9.12.2, 9.12.3.6

clauses

AUTHID, 8.8, 8.8.2

BULK COLLECT, 12.7.2

LIMIT, 12.7.2.2

CLOB datatype, 3.2.3

CLOSE statement

disables cursor, 6.2.2.5

disabling cursor variable

closing, 6.5.5.4

links to examples, 13

syntax, 13

collating sequence, 2.6.3.2

COLLECT clause

with BULK, 12.7.2

collection exceptions

when raised, 5.11

collection methods

syntax, 13

usage, 5.10

COLLECTION_IS_NULL exception, 11.4

collections

allowed subscript ranges, 5.6

applying methods to parameters, 5.10.9

assigning, 5.7

associative arrays and nested tables, 5.2.1

avoiding exceptions, 5.11

bulk binding, 5.14.5, 12.7

choosing the type to use, 5.2

comparing, 5.8

constructors, 5.5

COUNT method, 5.10.2

declaring variables, 5.4

defining types, 5.3

DELETE method, 5.10.8

element types, 5.3

EXISTS method, 5.10.1

EXTEND method, 5.10.6

FIRST method, 5.10.4

initializing, 5.5

LAST method, 5.10.4

LIMIT method, 5.10.3

links to examples, 13, 13

methods, 5.10

multilevel, 5.9

NEXT method, 5.10.5

operators to transform nested tables, 5.7

ordered group of elements, 5.1

overview, 1.2.10.2

PRIOR method, 5.10.5

referencing, 5.5

referencing elements, 5.6

scope, 5.3

syntax, 13

testing for null, 5.8

TRIM method, 5.10.7

types in PL/SQL, 5

understanding, 5.1.1

varrays and nested tables, 5.2.2

column aliases

expression values in a cursor loop, 6.3.6

when needed, 2.2.5.2

columns

accessing in triggers, 9.5.1

generating derived values with triggers, 9.12.3.9

listing in an UPDATE trigger, 9.4.2.2, 9.5.1.4

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

links to examples, 13

syntax, 13

comparison operators, 6.1.5.1

comparisons

of character values, 3.1.2.1.4

of expressions, 2.6.3

of null collections, 5.8

operators, 2.6.2.3

PL/SQL, 2.6

with NULLs, 2.6.5

compiler parameter settings

ALL_PLSQL_OBJECT_SETTINGS view, 12.1

compiler parameters

and REUSE SETTINGS clause, 12.1, 12.1

PL/SQL, 12.1

compiling

conditional, 1.2.7, 2.9

composite types

collection and records, 5.1

Compound triggers, 9.4.8

concatenation operator, 2.6.2.9

treatment of nulls, 2.6.5.3

conditional compilation, 1.2.7, 2.9

availability for previous Oracle database releases, 2.9.1

control tokens, 2.9.1.1

examples, 2.9.2.1

inquiry directives, 2.9.1.4

limitations, 2.9.3

NLS_LENGTH_SEMANTICS initialization parameter, 2.9.1.5

PLSQL_CCFLAGS initialization parameter, 2.9.1.5

PLSQL_CODE_TYPE initialization parameter, 2.9.1.5

PLSQL_DEBUG initialization parameter, 2.9.1.5

PLSQL_LINE flag, 2.9.1.5

PLSQL_OPTIMIZE_LEVEL initialization parameter, 2.9.1.5

PLSQL_UNIT flag, 2.9.1.5

PLSQL_WARNINGS initialization parameter, 2.9.1.5

restrictions, 2.9.3

static constants, 2.9.1.6.4

using PLSQL_CCFLAGS initialization parameter, 2.9.1.7, 2.9.1.7

using static expressions with, 2.9.1.6

using with DBMS_DB_VERSION, 2.9.1.8

using with DBMS_PREPROCESSOR, 2.9.2.2

conditional control, 4.2

conditional predicates

trigger bodies, 9.5, 9.5.1.4

conditional statement

guidelines, 4.2.6

CONSTANT

declaration, 13

for declaring constants, 1.2.2.4, 2.2.1

Constants

inlining, 8.8.4

constants

declaring, 1.2.2.4, 2.2, 2.2.1

links to examples, 13

static, 2.9.1.6.4

syntax, 13

understanding PL/SQL, 1.2.2

constraining tables, 9.5.4.4

constraints

NOT NULL, 2.2.3

triggers and, 9.2, 9.12.2

constructors

collection, 5.5

context

transactions, 6.8.2.2

CONTINUE statement

links to examples, 13

syntax, 13

CONTINUE-WHEN statement, 1.2.6.2

control structures

conditional, 4.2

overview of PL/SQL, 4.1

sequential, 4.4

understanding, 1.2.6

conventions

PL/SQL naming, 2.3

conversions

datatype, 3.4

correlated subqueries, 6.4.1

correlation names, 9.4.7

NEW, 9.5.1

OLD, 9.5.1

REFERENCING option and, 9.5.1.3

when preceded by a colon, 9.5.1

COUNT method

collections, 5.10.2, 13

CREATE

with PROCEDURE statement, 1.2.8.1, 1.3.1.2

CREATE FUNCTION statement, 1.2.8.1, 1.3.1.2

CREATE PROCEDURE statement, 1.2.8.1, 1.3.1.2

CREATE statement

packages, 1.2.8.2, 10.1

with FUNCTION, 1.2.8.1, 1.3.1.2

CREATE TRIGGER statement, 9.4

REFERENCING option, 9.5.1.3

CREATE_WRAPPED function

obfuscation, A.3

using, A.4

creating

functions, 1.2.8.1, 1.3.1.2

packages, 1.2.8.2, 10.1

procedures, 1.2.8.1, 1.3.1.2

cross-session PL/SQL function result cache, 8.13

CURRENT OF clause

with UPDATE, 6.7.7.1

current user

definer's rights subprograms, 8.8.3

invoker's rights subprograms, 8.8.3

subprograms, 8.8.3

CURRVAL

pseudocolumn, 6.1.4.1

cursor attributes

%BULK_EXCEPTIONS, 12.7.1.3

%BULK_ROWCOUNT, 12.7.1.2

%FOUND, 6.2.1.1.1, 6.2.2.6.1

%ISOPEN, 6.2.1.1.2, 6.2.2.6.2

%NOTFOUND, 6.2.1.1.3, 6.2.2.6.3

%ROWCOUNT, 6.2.1.1.4, 6.2.2.6.4

DBMS_SQL package and, 7.3

explicit, 6.2.2.6

implicit, 6.2.1.1

links to examples, 13

native dynamic SQL and, 7.2

syntax, 13

values after OPEN, FETCH, and CLOSE, 6.2.2.6.4

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

avoiding errors with, 6.5.7

closing, 6.5.5.4

declaring, 6.5.3

defining, 6.5.3

fetching from, 6.5.5.3

links to examples, 13

opening, 6.5.5.1

passing as parameters, 6.5.4

reducing network traffic, 6.5.6

restrictions, 6.5.8

syntax, 13

using as a host variable, 6.5.5.2

CURSOR_ALREADY_OPEN exception, 11.4

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.5.2

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

parameterized, 6.4.2

REF CURSOR variables, 6.5

RETURN clause, 10.12

scope rules for explicit, 6.2.2.1

SYS_REFCURSOR type, 12.12.8

variables, 6.5

D

data abstraction

understanding PL/SQL, 1.2.10

database character set, 2.1

database events

attributes, 9.13.5

tracking, 9.12.3.11

Database Resident Connection Pool, 10.10.4

database triggers, 1.3.1.3

autonomous, 6.8.4

datatypes

BFILE, 3.2.1

BLOB, 3.2.2

BOOLEAN, 3.1.3

CHAR, 3.1.2.1

CLOB, 3.2.3

DATE, 3.1.4.1

explicit conversion, 3.4.1

implicit conversion, 3.4.2

INTERVAL DAY TO SECOND, 3.1.4.6

INTERVAL YEAR TO MONTH, 3.1.4.5

LONG, 3.1.2.4

national character, 3.1.2.3

NCHAR, 3.1.2.3.2, 3.1.2.3.3

NCLOB, 3.2.4

NUMBER, 3.1.1.4

PL/SQL

see PL/SQL datatypes

RAW, 3.1.2.2

RECORD, 5.1

REF CURSOR, 6.5.1

ROWID, 3.1.2.5

TABLE, 5.3

TIMESTAMP, 3.1.4.2

TIMESTAMP WITH LOCAL TIME ZONE, 3.1.4.4

TIMESTAMP WITH TIME ZONE, 3.1.4.3

UROWID, 3.1.2.5

VARRAY, 5.1.1.2, 5.3

DATE datatype, 3.1.4.1

datetime

arithmetic, 3.1.4.7

datatypes, 3.1.4

literals, 2.1.3.5

DAY

datatype field, 3.1.4

DB_ROLE_CHANGE system manager event, 9.13.6

DBMS_ALERT package, 10.10.1

DBMS_ASSERT package, 7.4.2.2

DBMS_CONNECTION_CLASS package, 10.10.4

DBMS_DB_VERSION package

using with conditional compilation, 2.9.1.8

DBMS_DDL package

functions for hiding PL/SQL source code, A

limitations, A.1.2

obfuscating PL/SQL code, A

using, A.4

wrap functions, A.3

wrapping PL/SQL code, A

DBMS_OUTPUT package

displaying output, 1.2.9

displaying output from PL/SQL, 10.10.2

using PUT_LINE to display output, 1.2.2.2

DBMS_PIPE package, 10.10.3

DBMS_PREPROCESSOR package

using with conditional compilation, 2.9.2.2

DBMS_PROFILE package

gathering statistics for tuning, 12.6.1

DBMS_SQL package, 7.3

upgrade to dynamic SQL, 12.9

DBMS_SQL.TO_NUMBER function, 7.3

DBMS_SQL.TO_REFCURSOR function, 7.3

DBMS_TRACE package

tracing code for tuning, 12.6.2

DBMS_WARNING package

controlling warning messages in PL/SQL, 11.10.3

dbmsupbin.sql script

interpreted compilation, 12.11.6

dbmsupgnv.sql script

for PL/SQL native compilation, 12.11.6

deadlocks

how handled by PL/SQL, 6.7.4

debugging

triggers, 9.8

DEC

NUMBER subtype, 3.1.1.4

DECIMAL

NUMBER subtype, 3.1.1.4

declarations

collection, 5.4

constants, 1.2.2.4, 2.2.1

cursor variables, 6.5.3

exceptions in PL/SQL, 11.5.1

explicit cursor, 6.2.2.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.4

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 PL/SQL block, 1.2.1

DECLARE

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

syntax, 13

DECODE function

treatment of nulls, 2.6.5.4

DEFAULT keyword

for assignments, 2.2.2

DEFAULT option

FUNCTION, 13

RESTRICT_REFERENCES, 13

default parameter values, 8.5.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.8

DELETE method

collections, 5.10.8, 13

DELETE statement

column values and triggers, 9.5.1

links to examples, 13

syntax, 13

triggers for referential integrity, 9.12.3.2, 9.12.3.3

delimiters, 2.1.1

dense collections

arrays and nested tables, 5.1.1.1

dependencies

in stored triggers, 9.6.1

schema objects

trigger management, 9.5.4.2

DETERMINISTIC option

function syntax, 13

dictionary_obj_owner event attribute, 9.13.5

dictionary_obj_owner_list event attribute, 9.13.5

dictionary_obj_type event attribute, 9.13.5

digits of precision, 3.1.1.4

disabled trigger

definition, 9.1

disabling

triggers, 9.1

displaying output

from PL/SQL, 1.2.2.2

setting SERVEROUTPUT, 1.2.9, 10.10.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.3

distributed databases

triggers and, 9.5.4.2

dot notation, 1.2.5.1

for collection methods, 5.10

for global variables, 4.3.8.3

for package contents, 10.5

DOUBLE PRECISION

NUMBER subtype, 3.1.1.4

DROP TRIGGER statement, 9.7

dropping

triggers, 9.7

DUP_VAL_ON_INDEX exception, 11.4

dynamic multiple-row queries, 7.2.2

dynamic SQL, 7

DBMS_SQL package, 7.3

native, 7.2

switching between native dynamic SQL and DBMS_SQL package, 7.3

tuning, 12.9

dynamic wrapping

DBMS_DDL package, A

E

element types

collection, 5.3

ELSE clause

using, 4.2.2

ELSIF clause

using, 4.2.3

enabled trigger

definition, 9.1

enabling

triggers, 9.1

END

end of a PL/SQL block, 13

syntax, 13

END IF

end of IF statement, 4.2.1

END LOOP

end of LOOP statement, 4.3.7

error handling

in PL/SQL, 11

overview, 1.2.11

error messages

maximum length, 11.9.4

ERROR_CODE

BULK_EXCEPTIONS cursor attribute field, 12.7.1.3

using with SQLERRM, 12.7.1.3

ERROR_INDEX

BULK_EXCEPTIONS cursor attribute field, 12.7.1.3

evaluation

short-circuit, 2.6.2.2

event attribute functions, 9.13.5

event publication, 9.13

triggering, 9.13

events

attribute, 9.13.5

tracking, 9.12.3.11

EXCEPTION

exception-handling part of a block, 13

syntax in PL/SQL block, 13

exception definition

syntax, 13

exception handlers

OTHERS handler, 11.1

overview, 1.2.11

using RAISE statement in, 11.8, 11.9

using SQLCODE function in, 11.9.4

using SQLERRM function in, 11.9.4

WHEN clause, 11.9

EXCEPTION_INIT pragma

links to examples, 13

syntax, 13

using with RAISE_APPLICATION_ERROR, 11.5.4

with exceptions, 11.5.3

exception-handling part

of PL/SQL block, 1.2.1

exceptions

advantages of PL/SQL, 11.3

branching with GOTO, 11.9.3

catching unhandled in PL/SQL, 11.9.5

continuing after an exception is raised, 11.9.6.1

controlling warning messages, 11.10.2

declaring in PL/SQL, 11.5.1

definition, 13

during trigger execution, 9.5.1.5

handling in PL/SQL, 11

links to examples, 13

list of predefined in PL/SQL, 11.4

locator variables to identify exception locations, 11.9.6.3

OTHERS handler in PL/SQL, 11.9

PL/SQL compile-time warnings, 11.10

PL/SQL error condition, 11.1

PL/SQL warning messages, 11.10.1

predefined in PL/SQL, 11.4

propagation in PL/SQL, 11.7

raise_application_error procedure, 11.5.4

raised in a PL/SQL declaration, 11.9.1

raised in handlers, 11.9.2

raising in PL/SQL, 11.6

raising predefined explicitly, 11.6

raising with RAISE statement, 11.6

redeclaring predefined in PL/SQL, 11.5.5

reraising in PL/SQL, 11.8

retrying a transaction after, 11.9.6.2

scope rules in PL/SQL, 11.5.2

tips for handling PL/SQL errors, 11.9.6

user-defined in PL/SQL, 11.5

using EXCEPTION_INIT pragma, 11.5.3

using SQLCODE, 11.9.4

using SQLERRM, 11.9.4

using the DBMS_WARNING package, 11.10.3

using WHEN and OR, 11.9

WHEN clause, 11.9

executable part

of PL/SQL block, 1.2.1

EXECUTE IMMEDIATE statement, 7.2.1

links to examples, 13

syntax, 13

EXECUTE privilege

subprograms, 8.8.7

EXISTS method

collections, 5.10.1, 13

EXIT statement

early exit of LOOP, 4.3.8.4

links to examples, 13

syntax, 13

using, 4.3.2, 4.3.4

EXIT-WHEN statement, 1.2.6.2

using, 4.3.3, 4.3.5

explicit cursors, 6.2.2

explicit datatype conversion, 3.4.1

explicit declarations

cursor FOR loop record, 6.3.5.2

expressions

as default parameter values, 8.5.4

in cursors, 6.4.2

Boolean, 2.6.3

CASE, 2.6.4, 2.6.4

examples, 13

PL/SQL, 2.6

static, 2.9.1.6

syntax, 13

EXTEND method

collections, 5.10.6, 13

external

references, 8.8.2

routines, 8.10

subprograms, 8.10

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.5.3

fetching

across commits, 6.7.7.3

bulk, 12.7.2.1, 12.7.2.1

fields

of records, 5.1

file I/O, 10.10.6

FIRST method

collections, 5.10.4, 13

FLOAT

NUMBER subtype, 3.1.1.4

FOR EACH ROW clause, 9.4.6

FOR loops

explicit cursors, 6.3.5.2

nested, 4.3.8.3

FOR UPDATE clause, 6.2.2.2

when to use, 6.7.7.1

FORALL statement

links to examples, 13

syntax, 13

using, 12.7.1

using to improve performance, 12.7.1

using with BULK COLLECT clause, 12.7.2.4

with rollbacks, 12.7.1.1

FOR-LOOP statement

syntax, 13

using, 4.3.8

formal parameters, 6.4.2

forward

declarations of subprograms, 8.4

references, 2.2.6

FOUND cursor attribute

explicit, 6.2.2.6.1

implicit, 6.2.1.1.1

FUNCTION

with CREATE statement, 1.2.8.1, 1.3.1.2

function declaration

syntax, 13

function result cache, 8.13

functions

creating, 1.2.8.1, 1.3.1.2

declaration, 13

in PL/SQL, 8

invoking, 8.1.3

links to examples, 13

pipelined, 12.12

RETURN statement, 8.3

SQL, in PL/SQL, 2.8

table, 12.12.1

G

GOTO statement

branching into or out of exception handler, 11.9.3

label, 4.4.1

links to examples, 13

overview, 1.2.6.3

restrictions, 4.4.2

syntax, 13

using, 4.4.1

grantee event attribute, 9.13.5

GROUP BY clause, 6.1.3

H

handlers

exception in PL/SQL, 11.1

handling errors

PL/SQL, 11

handling exceptions

PL/SQL, 11

raised in as PL/SQL declaration, 11.9.1

raised in handler, 11.9.2

using OTHERS handler, 11.9

handling of nulls, 2.6.5

hash tables

simulating with associative arrays, 5.2

hiding PL/SQL code, A

host arrays

bulk binds, 12.7.2.5

HOUR

datatype field, 3.1.4

HTF package, 10.10.5

HTP package, 10.10.5

hypertext markup language (HTML), 10.10.7

hypertext transfer protocol (HTTP), 1.1.6

UTL_HTTP package, 10.10.7

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

implicit cursors

attributes, 6.2.1.1

guidelines, 6.2.1.2

implicit datatype conversion, 3.4.2

implicit datatype conversions

performance, 12.4.1.6

implicit declarations

FOR loop counter, 4.3.8.3

IN comparison operator, 2.6.2.8

IN OUT parameter mode

subprograms, 8.5.3.3

IN parameter mode

subprograms, 8.5.3.1

INDEX BY

collection definition, 13

index-by tables

See associative arrays

INDICES OF clause

FORALL, 13

with FORALL, 12.7.1

infinite loops, 4.3.1

initialization

collections, 5.5

package, 10.6

using DEFAULT, 2.2.2

variable, 2.5

with NOT NULL constraint, 2.2.3

initialization parameters

PL/SQL compilation, 12.1

in-line LOB locators, 3.2

INLINE pragma

syntax, 13

Inlining constants, 8.8.4

Inlining subprograms, 12.2

INSERT statement

column values and triggers, 9.5.1

links to examples, 13

syntax, 13

with a record variable, 5.14.2

instance_num event attribute, 9.13.5

INSTEAD OF triggers, 9.4.5

on nested table view columns, 9.5.1.2

INT

NUMBER subtype, 3.1.1.4

INTEGER

NUMBER subtype, 3.1.1.4

inter-language calls, 8.10

interpreted compilation

dbmsupbin.sql script, 12.11.6

recompiling all PL/SQL modules, 12.11.6

INTERSECT set operator, 6.1.5.2

interval

arithmetic, 3.1.4.7

INTERVAL DAY TO SECOND datatype, 3.1.4.6

INTERVAL YEAR TO MONTH datatype, 3.1.4.5

intervals

datatypes, 3.1.4

INTO

SELECT INTO statement, 13

INTO clause

with FETCH statement, 6.5.5.3

INTO list

using with explicit cursors, 6.2.2.3

INVALID_CURSOR exception, 11.4

INVALID_NUMBER exception, 11.4

invoker's rights

advantages, 8.8.1

privileges on subprograms, 8.8

invoking

Java stored procedures, 8.10

procedures, 1.2.8.2

stored subprograms, 1.3.1.2

IS A SET operator, 5.8

IS EMPTY operator, 5.8

IS NULL comparison operator, 2.6.2.5

expressions, 13

is_alter_column event attribute, 9.13.5

ISOLATION LEVEL parameter

READ COMMITTED, 13

SERIALIZABLE, 13

setting transactions, 13

ISOPEN cursor attribute

explicit, 6.2.2.6.2

implicit, 6.2.1.1.2

J

JAVA

use for invoking external subprograms, 8.10

Java

call specs, 8.10

Java stored procedures

invoking from PL/SQL, 8.10

K

keywords

use in PL/SQL, 2.1.2.1

keywords, PL/SQL, D

list of, D

L

labels

block, 2.4

block structure, 13

exiting loops, 4.3.6

GOTO statement, 4.4.1

loops, 4.3.6

syntax, 13

LANGUAGE

use for invoking external subprograms, 8.10

language elements

of PL/SQL, 13

large object (LOB) datatypes, 3.2

LAST method

collections, 5.10.4, 13

LEVEL

pseudocolumn, 6.1.4.2

LEVEL parameter

with ISOLATION to set transactions, 13

lexical units

PL/SQL, 2.1

LIKE comparison operator, 2.6.2.6

expressions, 13

LIMIT clause

FETCH, 13

using to limit rows for a Bulk FETCH operation, 12.7.2.2

LIMIT method

collections, 5.10.3, 13

limitations

bulk binding, 12.7

DBMS_DDL package, A.1.2

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.2

LOB datatypes

use in triggers, 9.5.1.1

LOB locators, 3.2

local subprograms, 1.3.1.2

locator variables

used with exceptions, 11.9.6.3

LOCK TABLE statement

examples, 13

locking a table, 6.7.7.2

syntax, 13

locks

modes, 6.7

overriding, 6.7.7

transaction processing, 6.7

using FOR UPDATE clause, 6.7.7.1

logical operators, 2.6.2

logical rowids, 3.1.2.5

LOGIN_DENIED exception, 11.4

LONG datatype, 3.1.2.4

maximum length, 3.1.2.4

use in triggers, 9.5.4.3

LOOP statement, 4.3

links to examples, 13

overview, 1.2.6.2

syntax, 13

using, 4.3.1

loops

counters, 4.3.8

dynamic ranges, 4.3.8.2

exiting using labels, 4.3.6

implicit declaration of counter, 4.3.8.3

iteration, 4.3.8.1

labels, 4.3.6

reversing the counter, 4.3.8

scope of counter, 4.3.8.3

M

maximum precision, 3.1.1.4

maximum size

CHAR value, 3.1.2.1

identifier, 2.1.2

LONG value, 3.1.2.4

Oracle error message, 11.9.4

RAW value, 3.1.2.2

MEMBER OF operator, 5.8

membership test, 2.6.2.8

memory

avoid excessive overhead, 12.4.2

MERGE statement

syntax, 13

Method 4, 7.3

methods

collection, 5.10

MINUS set operator, 6.1.5.2

MINUTE

datatype field, 3.1.4

modularity, 1.2.8

packages, 10.3

MONTH

datatype field, 3.1.4

multilevel collections

using, 5.9

multi-line comments, 2.1.4.2

multiple-row queries

dynamic, 7.2.2

MULTISET EXCEPT operator, 5.7

MULTISET INTERSECT operator, 5.7

MULTISET UNION operator, 5.7

mutating table

definition, 9.5.4.4

mutating tables

trigger restrictions, 9.5.4.4

N

NAME

for invoking external subprograms, 8.10

NAME parameter

setting transactions, 13

transactions, 6.7.6

name resolution, 2.3.4

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.8.6

qualified names and dot notation, B.2

qualifying references to attributes and methods, B.5

understanding, B.1

understanding capture, B.4

with synonyms, 8.8.6

names

explicit cursor, 6.2.2.1

qualified, 2.3

savepoint, 6.7.3

variable, 2.3.2

naming conventions

PL/SQL, 2.3

national character datatypes, 3.1.2.3

national character set, 2.1

native compilation

dbmsupgnv.sql script, 12.11.6

dependencies, 12.11.3

how it works, 12.11.2

invalidation, 12.11.3

modifying databases for, 12.11.6

revalidation, 12.11.3

setting up databases, 12.11.5

utlrp.sql script, 12.11.6

native dynamic SQL, 7.2

NATURAL

BINARY_INTEGER subtype, 3.1.1.1

NATURALN

BINARY_INTEGER subtype, 3.1.1.1

NCHAR datatype, 3.1.2.3.2, 3.1.2.3.3

NCLOB datatype, 3.2.4

nested collections, 5.9

nested cursors

using, 6.6

nested tables

associative arrays and, 5.2.1

sets of values, 5.1.1.1

syntax, 13

transforming with operators, 5.7

understanding, 5.1.1

varrays and, 5.2.2

nesting

block, 1.2.1, 1.2.1

FOR loops, 4.3.8.3

record, 5.12

NEW correlation name, 9.5.1

new features, Preface

NEXT method

collections, 5.10.5, 13

NEXTVAL

pseudocolumn, 6.1.4.1

NLS_LENGTH_SEMANTICS initialization parameter

use with conditional compilation, 2.9.1.5

NO COPY hint

FUNCTION, 13

NO_DATA_FOUND exception, 11.4

NOCOPY compiler hint

for tuning, 12.10

restrictions on, 12.10

NOT logical operator

treatment of nulls, 2.6.5.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.4

using in variable declaration, 2.2.3

NOT NULL option

record definition, 13

NOT_LOGGED_ON exception, 11.4

notation

positional and named, 8.5.2

NOTFOUND cursor attribute

explicit, 6.2.2.6.3

implicit, 6.2.1.1.3

NOWAIT parameter

using with FOR UPDATE, 6.7.7.1

null handling, 2.6.5

NULL statement

links to examples, 13

syntax, 13

using, 4.4.3

NULL value

dynamic SQL and, 7.2.1

NUMBER datatype, 3.1.1.4

range of literals, 2.1.3.1

range of values, 3.1.1.4

NUMERIC

NUMBER subtype, 3.1.1.4

numeric literals, 2.1.3.1

PL/SQL datatypes, 2.1.3.1

NVL function

treatment of nulls, 2.6.5.4

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.4

viewing source, A.1, A.4

WRAP function, A.3

wrap utility, A.2

object type declaration

syntax, 13

object types

overview, 1.2.10.4

using with invoker's-rights subprograms, 8.8.10

OBJECT_VALUE pseudocolumn, 9.5.2

OLD correlation name, 9.5.1

ONLY parameter

with READ to set transactions, 13

on-the-fly wrapping

DBMS_DDL package, A

OPEN statement

explicit cursors, 6.2.2.2

links to examples, 13

syntax, 13

OPEN-FOR statement, 6.5.5.1

links to examples, 13

syntax, 13

OPEN-FOR-USING statement

syntax, 13

operators

comparison, 2.6.2.3

logical, 2.6.2

precedence, 2.6.1

relational, 2.6.2.4

optimizing

PL/SQL programs, 12.2

OR keyword

using with EXCEPTION, 11.9

ora_dictionary_obj_owner event attribute, 9.13.5

ora_dictionary_obj_owner_list event attribute, 9.13.5

ora_dictionary_obj_type event attribute, 9.13.5

ora_grantee event attribute, 9.13.5

ora_instance_num event attribute, 9.13.5

ora_is_alter_column event, 9.13.5

ora_is_creating_nested_table event attribute, 9.13.5

ora_is_drop_column event attribute, 9.13.5

ora_is_servererror event attribute, 9.13.5

ora_login_user event attribute, 9.13.5

ora_privileges event attribute, 9.13.5

ora_revokee event attribute, 9.13.5

ora_server_error event attribute, 9.13.5

ora_sysevent event attribute, 9.13.5

ora_with_grant_option event attribute, 9.13.5

order of evaluation, 2.6.1, 2.6.2.1

OTHERS clause

exception handling, 13

OTHERS exception handler, 11.1, 11.9

OUT parameter mode

subprograms, 8.5.3.2

out-of-line LOB locators, 3.2

overloading

guidelines, 8.6.1

packaged subprograms, 10.7

restrictions, 8.6.2

subprogram names, 8.6

P

PACKAGE

with SQL CREATE statement, 1.2.8.2, 10.1

PACKAGE BODY

with SQL CREATE statement, 1.2.8.2, 10.1

package declaration

syntax, 13

packaged cursors, 10.12

packaged subprograms, 1.3.1.2

packages

advantages, 10.3

bodiless, 10.4

body, 10.1, 10.6, 13

call specification, 10.1

contents of, 10.2

creating, 1.2.8.2, 10.1

cursor specifications, 10.12

cursors, 10.12

declaration, 13

dot notation, 10.5

examples of features, 10.7

global variables, 10.7

guidelines for writing, 10.11

hidden declarations, 10.1

initializing, 10.6

invoking subprograms, 10.5

links to examples, 13

modularity, 10.3

overloading subprograms, 10.7

overview, 1.2.8.2

overview of Oracle supplied, 10.10

private and public objects, 10.8

product-specific, 10.10

product-specific for use with PL/SQL, 1.1.6

referencing, 10.5

restrictions on referencing, 10.5

scope, 10.4

specification, 10.1, 13

specifications, 10.4

STANDARD package, 10.9

understanding, 10.1

visibility of contents, 10.1

PARALLE_ENABLE option

FUNCTION, 13

parameter passing

by reference, 8.12

by value, 8.12

parameters

actual, 6.4.2

actual and formal, 8.5.1

aliasing, 8.12

cursor, 6.4.2

default values, 8.5.4

formal, 6.4.2

IN mode, 8.5.3.1

IN OUT mode, 8.5.3.3

modes, 8.5.3

OUT mode, 8.5.3.2

summary of modes, 8.5.3.4

parentheses, 2.6.1

parse tree, 9.6

pattern matching, 2.6.2.6

performance

avoid memory overhead, 12.4.2

avoiding problems, 12.4

physical rowids, 3.1.2.5

pipe, 10.10.3

PIPE ROW statement

for returning rows incrementally, 12.12.4

PIPELINED

function option, 12.12.2, 13

pipelined functions

exception handling, 12.12.11

fetching from results of, 12.12.7

for querying a table, 12.12.1

overview, 12.12.1

passing data with cursor variables, 12.12.8

performing DML operations inside, 12.12.9

performing DML operations on, 12.12.10

returning results from, 12.12.4

transformation of data, 12.12

transformations, 12.12.3

writing, 12.12.2

pipelines

between table functions, 12.12.5

returning results from table functions, 12.12.4

support collection types, 12.12.2

using table functions, 12.12.3

writing table functions, 12.12.2

pipelining

definition, 12.12.1

PLS_INTEGER datatype, 3.1.1.1

overflow condition, 3.1.1.1

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

blocks

structure, 1.2.1

syntax, 13

CASE expressions, 2.6.4

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

compiler parameters, 12.1

compile-time warnings, 11.10

conditional compilation, 1.2.7, 2.9

constants, 1.2.2

control structures, 1.2.6, 4.1

creating Web applications and pages, 2.10

data abstraction, 1.2.10

declarations

constants, 2.2

displaying output, 1.2.2.2, 10.10.2

engine, 1.3

environment, 10.9

error handling

overview, 1.2.11

errors, 11

exceptions, 11

expressions, 2.6

functions, 8

hiding or obfuscating source code, A

input data, 1.2.9

lexical units, 2.1

limitations of programs, C

limits on programs, C

literals, 2.1.3

logical operators, 2.6.2

name resolution, B.1

naming conventions, 2.3

new features, Preface

output data, 1.2.9

performance problems, 12.4

portability, 1.1.4

procedural aspects, 1.2

procedures, 8

profiling and tracing programs, 12.6

querying data, 6.3

records

overview, 1.2.10.3

scope of identifiers, 2.4

Server Pages (PSPs), 2.10.2

statements, 13

subprograms, 8

invoking, 1.3.1.2

syntax of language elements, 13

transaction processing, 6.7

trigger bodies, 9.5, 9.5.1

tuning code, 12.3

tuning computation-intensive programs, 12.8

tuning dynamic SQL programs, 12.9

using NOCOPY for tuning, 12.10

using transformation pipelines, 12.12

variables, 1.2.2

warning messages, 11.10.1

Web applications, 2.10.1

writing reusable code, 1.2.8

PLSQL datatypes, 3

numeric literals, 2.1.3.1

predefined, 3

PL/SQL function result cache, 8.13

PLSQL_CCFLAGS initialization parameter

conditional compilation, 2.9.1.7, 2.9.1.7

use with conditional compilation, 2.9.1.5

PLSQL_CODE_TYPE initialization parameter

setting PL/SQL native compilation, 12.11.4

use with conditional compilation, 2.9.1.5

PLSQL_DEBUG initialization parameter

use with conditional compilation, 2.9.1.5

PLSQL_LINE flag

use with conditional compilation, 2.9.1.5

PLSQL_OPTIMIZE_LEVEL initialization parameter, 12.2

optimizing PL/SQL programs, 12.2

use with conditional compilation, 2.9.1.5

PLSQL_UNIT flag

use with conditional compilation, 2.9.1.5

PLSQL_WARNINGS initialization parameter, 11.10

use with conditional compilation, 2.9.1.5

pointers

REF CIRSOR, 6.5.1

portability, 1.1.4

POSITIVE

BINARY_INTEGER subtype, 3.1.1.1

POSITIVEN

BINARY_INTEGER subtype, 3.1.1.1

PRAGMA

compiler directive with AUTONOMOUS_TRANSACTION, 13

compiler directive with EXCEPTION_INIT, 13

compiler directive with RESTRICT_REFERENCES, 13

compiler directive with SERIALLY_REUSABLE, 13

pragmas

AUTONOMOUS_TRANSACTION, 6.8.2, 13

compiler directives, 11.5.3

EXCEPTION_INIT, 11.5.3, 13

INLINE, 13

RESTRICT_REFERENCES, 6.8.5, 8.11, 13

SERIALLY_REUSABLE, 13

precedence, operator, 2.6.1

precision of digits

specifying, 3.1.1.4

predefined exceptions

raising explicitly, 11.6

redeclaring, 11.5.5

predefined PL/SQL datatypes, 3

predicates, 6.1.5.1

PRIOR method

collections, 5.10.5, 13

PRIOR row operator, 6.1.4.2

private objects

packages, 10.8

privileges

creating triggers, 9.3

dropping triggers, 9.7

recompiling triggers, 9.6.2

PROCEDURE

with CREATE statement, 1.2.8.1, 1.3.1.2

procedure declaration

syntax, 13

procedures

creating, 1.2.8.1, 1.3.1.2

declaration, 13

in PL/SQL, 8

invoked by triggers, 9.5.4.2

invoking, 1.2.8.2, 8.1.3

links to examples, 13

recompiling with ALTER PROCEDURE, 11.10.3

productivity, 1.1.3

Profiler API

gathering statistics for tuning, 12.6.1

program units, 1.2.8

PROGRAM_ERROR exception, 11.4

propagation

exceptions in PL/SQL, 11.7

pseudocolumns

CURRVAL, 6.1.4.1

LEVEL, 6.1.4.2

modifying views, 9.4.5.1

NEXTVAL, 6.1.4.1

ROWID, 6.1.4.3

ROWNUM, 6.1.4.4

SQL, 6.1.4

UROWID, 6.1.4.3

use in PL/SQL, 6.1.4

public objects

packages, 10.8

purity rules, 8.11

PUT_LINE

displaying output with, 1.2.2.2

Q

qualifiers

using subprogram names as, 2.3.4

when needed, 2.3, 2.4

queries

multiple-row

dynamic, 7.2.2

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.1

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.1

with PL/SQL, 6.3

work areas, 6.5.1

quoted identifiers, 2.1.2.3

R

RAISE statement

exceptions in PL/SQL, 11.6

links to examples, 13

syntax, 13

using in exception handler, 11.8, 11.9

raise_application_error procedure

for raising PL/SQL exceptions, 11.5.4

raising an exception

in PL/SQL, 11.6

raising exceptions

triggers, 9.5.1.5

range operator, 4.3.8

RAW datatype, 3.1.2.2

maximum length, 3.1.2.2

READ ONLY parameter

setting transactions, 13

transactions, 6.7.6

READ WRITE parameter

setting transactions, 13

readability, 2.1

with NULL statement, 4.4.3

read-only transaction, 6.7.6

REAL

NUMBER subtype, 3.1.1.4

RECORD datatype, 5.1

record definition

syntax, 13

records

%ROWTYPE, 6.3.5.2

assigning values, 5.14

bulk-binding collections of, 5.14.5

comparing, 5.14.1

declaring, 5.12

defining, 5.12

definition, 1.2.5.2, 13

group of fields, 5.1.2

group of related data items, 5.1

implicit declaration, 6.3.5.2

inserting, 5.14.2

links to examples, 13

manipulating, 5.13

nesting, 5.12

overview, 1.2.10.3

passing as parameters, 5.13

restriction on assignments, 5.14

restrictions on inserts and updates of, 5.14.4

returning into, 5.14.3

ROWTYPE attribute, 5.1.2

updating, 5.14.3

using as function return values, 5.13

recursion

using with PL/SQL subprograms, 8.9

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

predefined SYS_REFCURSOR type, 12.12.8

references

external, 8.8.2

resolving external, 8.8.4

referencing

collections, 5.5

referencing elements

allowed subscript ranges, 5.6

REFERENCING option, 9.5.1.3

referential integrity

self-referential constraints, 9.12.3.2

triggers and, 9.12.3

regular expression functions

REGEXP_LIKE, 6.2.2.3

relational operators, 2.6.2.4

RELIES ON clause, 13

remote access indicator, 2.3

remote exception handling, 9.5.3

REPEAT UNTIL structure

PL/SQL equivalent, 4.3.7

REPLACE function

treatment of nulls, 2.6.5.4

reraising an exception, 11.8

reserved words

syntactic meaning in PL/SQL, 2.1.2.1

reserved words, PL/SQL, D

list of, D

resolution

name, 2.3.4

references to names, B.1

RESTRICT_REFERENCES pragma, 8.11

links to examples, 13

syntax, 13

using with autonomous functions, 6.8.5

restrictions

cursor expressions, 6.6.1

cursor variables, 6.5.8

overloading subprograms, 8.6.2

system triggers, 9.5.4.6

result cache, 8.13

result sets, 6.2.2.2

RESULT_CACHE clause, 13

RETURN clause

cursor, 10.12

cursor declaration, 13

FUNCTION, 13

RETURN statement

functions, 8.3

links to examples, 13

syntax, 13

return types

REF CURSOR, 6.5.3

RETURNING clause

links to examples, 13

with a record variable, 5.14.3

RETURNING INTO clause

syntax, 13

returns

bulk, 12.7.2.3

REUSE SETTINGS clause

with compiler parameters, 12.1, 12.1

REVERSE

with LOOP counter, 4.3.8

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

routines

external, 8.10

row locks

with FOR UPDATE, 6.7.7.1

row operators, 6.1.5.3

row triggers

defining, 9.4.6

REFERENCING option, 9.5.1.3

timing, 9.4.3

UPDATE statements and, 9.4.2.2, 9.5.1.4

ROWCOUNT cursor attribute

explicit, 6.2.2.6.4

implicit, 6.2.1.1.4

ROWID

pseudocolumn, 6.1.4.3

ROWID datatype, 3.1.2.5

rowids, 3.1.2.5

ROWIDTOCHAR function, 6.1.4.3

ROWNUM

pseudocolumn, 6.1.4.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.12

syntax, 13

using, 2.2.5

with SUBTYPE, 3.3.1

ROWTYPE_MISMATCH exception, 11.4

RPC (remote procedure call)

and exceptions, 11.7

rules

purity, 8.11

run-time errors

PL/SQL, 11

S

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.4

scientific notation, 2.1.3.1

scope, 2.4

collection, 5.3

definition, 2.4

exceptions in PL/SQL, 11.5.2

explicit cursor, 6.2.2.1

explicit cursor parameter, 6.2.2.1

identifier, 2.4

loop counter, 4.3.8.3

package, 10.4

searched CASE expression, 2.6.4.2

searched CASE statement, 4.2.5

SECOND

datatype field, 3.1.4

security risks, 7.4

SELECT INTO statement

links to examples, 13

returning one row, 6.3.1

syntax, 13

selector, 2.6.4.1

SELF_IS_NULL exception, 11.4

semantics

string comparison, 3.1.2.1.4

separators, 2.1.1

sequences

CURRVAL and NEXTVAL, 6.1.4.1

SERIALLY_REUSABLE pragma

examples, 13

syntax, 13

Server Pages (PSPs)

PL/SQL, 2.10.2

SERVEROUTPUT

displaying output from PL/SQL, 1.2.9

setting ON to display output, 10.10.2

SET clause

UPDATE, 13

set operators, 6.1.5.2

SET TRANSACTION statement, 6.7.6

links to examples, 13

syntax, 13

short-circuit evaluation, 2.6.2.2

side effects, 8.5.3

controlling, 8.11

SIGNTYPE

BINARY_INTEGER subtype, 3.1.1.1

simple CASE expression, 2.6.4.1

SIMPLE_INTEGER datatype, 3.1.1.2

single-line comments, 2.1.4.1

size limit

varrays, 5.3

SMALLINT

NUMBER subtype, 3.1.1.4

spaces

where allowed, 2.1

sparse collections

nested tables and arrays, 5.1.1.1

specification

call, 10.1

cursor, 10.12

package, 10.4

packages, 13

SQL

comparisons operators, 6.1.5.1

data manipulation operations, 6.1.1

define variables and data manipulation statements, 6.1.1

DML operations, 6.1.1

dynamic, 7

exceptions raised by data manipulation statements, 6.1.1

no rows returned with data manipulation statements, 6.1.1

pseudocolumns, 6.1.4

static, 6

SQL cursor

dynamic SQL and, 7.3

links to examples, 13

syntax, 13

SQL functions in PL/SQL, 2.8

SQL injection, 7.4

SQL modification, 7.4.1.1

SQL reserved words, D

SQL statements

in trigger bodies, 9.5.1, 9.5.4.2

not allowed in triggers, 9.5.4.2

SQLCODE function

links to examples, 13

syntax, 13

using with exception handlers, 11.9.4

SQLERRM function

links to examples, 13

syntax, 13

using with BULK_EXCEPTIONS ERROR_CODE field, 12.7.1.3

using with exception handlers, 11.9.4

standalone subprograms, 1.3.1.2

STANDARD package

defining PL/SQL environment, 10.9

START WITH clause, 6.1.4.2

Statement injection, 7.4.1.2

statement terminator, 13

statement triggers

conditional code for statements, 9.5.1.4

row evaluation order, 9.4.4

specifying SQL statement, 9.4.2

timing, 9.4.3

UPDATE statements and, 9.4.2.2, 9.5.1.4

valid SQL statements, 9.5.4.2

statements

assignment, 13

CASE, 13

CLOSE, 6.2.2.5, 6.5.5.4, 13

COMMIT, 13

CONTINUE, 13

DELETE, 13

EXECUTE IMMEDIATE, 13

EXIT, 13

FETCH, 6.2.2.3, 6.5.5.3, 13

FORALL, 12.7.1, 13

FOR-LOOP, 13

GOTO, 13

IF, 13

INSERT, 13

LOCK TABLE, 13

LOOP, 4.3, 13

MERGE, 13

NULL, 13

OPEN, 6.2.2.2, 13

OPEN-FOR, 6.5.5.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.9.1.6.4

static expressions

boolean, 2.9.1.6

PLS_INTEGER, 2.9.1.6

use with conditional compilation, 2.9.1.6

VARCHAR2, 2.9.1.6

static SQL, 6

STEP clause

equivalent in PL/SQL, 4.3.8.1

STORAGE_ERROR exception, 11.4

raised with recursion, 8.9

store tables, 5.2.2

stored subprograms

in Oracle database, 1.3.1.2

string comparison semantics, 3.1.2.1.4

string literals, 2.1.3.3

NCHAR, 2.1.3.3

STRING subtype, 3.1.2.1.1

Subprogram inlining, 12.2

subprograms

actual and formal parameters, 8.5.1

advantages in PL/SQL, 8.2

AUTHID clause, 8.8, 8.8.2

controlling side effects, 8.11

current user during execution, 8.8.3

declaring nested, 8.4

declaring PL/SQL, 1.2.4

default parameter modes, 8.5.4

definer's rights, 8.8

EXECUTE privilege, 8.8.7

external references, 8.8.2

granting privileges on invoker's-rights, 8.8.7

guidelines for overloading, 8.6.1

how calls are resolved, 8.7

IN OUT parameter mode, 8.5.3.3

IN parameter mode, 8.5.3.1

in PL/SQL, 8

invoker's-rights, 8.8

invoking external, 8.10

invoking from SQL*Plus, 1.3.1.2, 1.3.1.2

invoking with parameters, 8.5.2

local, 1.3.1.2

mixed notation parameters, 8.5.2

named parameters, 8.5.2

OUT parameter mode, 8.5.3.2

overloading names, 8.6

overriding name resolution, 8.8.6

packaged, 1.3.1.2

parameter aliasing, 8.12

parameter modes, 8.5.3, 8.5.3.4

passing parameter by value, 8.12

passing parameters, 8.5

passing parameters by reference, 8.12

positional parameters, 8.5.2

recursive, 8.9

resolving external references, 8.8.4

restrictions on overloading, 8.6.2

standalone, 1.3.1.2

stored, 1.3.1.2

understanding PL/SQL, 8.1

using database links with invoker's-rights, 8.8.9

using recursion, 8.9

using triggers with invoker's-rights, 8.8.8

using views with invoker's-rights, 8.8.8

subqueries

correlated, 6.4.1

using in PL/SQL, 6.4

SUBSCRIPT_BEYOND_COUNT exception, 11.4

SUBSCRIPT_OUTSIDE_LIMIT exception, 11.4

SUBSTR function

using with SQLERRM, 11.9.4

subtypes

CHARACTER, 3.1.2.1.1

compatibility, 3.3.2.1

constrained and unconstrained, 3.3

defining, 3.3.1

STRING, 3.1.2.1.1

using, 3.3.2

VARCHAR, 3.1.2.1.1

synonyms

name resolution, 8.8.6

syntax

BEGIN, 13

collection method, 13

exception definition, 13

FETCH statement, 13

literal declaration, 13

LOOP statement, 13

NULL statement, 13

package declaration, 13

reading diagrams, 13

WHILE-LOOP statement, 13

syntax of PL/SQL language elements, 13

SYS_INVALID_ROWID exception, 11.4

SYS_REFCURSOR type, 12.12.8

T

TABLE datatype, 5.3

table functions

exception handling, 12.12.11

fetching from results of, 12.12.7

for querying, 12.12.1

organizing multiple calls to, 12.12.6

passing data with cursor variables, 12.12.8

performing DML operations inside, 12.12.9

performing DML operations on, 12.12.10

pipelining data between, 12.12.5

returning results from, 12.12.4

setting up transformation pipelines, 12.12

using transformation pipelines, 12.12.3

writing transformation pipelines, 12.12.2

table, mutating

definition, 9.5.4.4

tables

constraining, 9.5.4.4

mutating, 9.5.4.4

tabs, 2.1

terminator, statement, 2.1.1

THEN clause

using, 4.2.1

with IF statement, 4.2.1

TIMEOUT_ON_RESOURCE exception, 11.4

TIMESTAMP datatype, 3.1.4.2

TIMESTAMP WITH LOCAL TIME ZONE datatype, 3.1.4.4

TIMESTAMP WITH TIME ZONE datatype, 3.1.4.3

TIMEZONE_ABBR

datatype field, 3.1.4

TIMEZONE_HOUR

datatype field, 3.1.4

TIMEZONE_MINUTES

datatype field, 3.1.4

TIMEZONE_REGION

datatype field, 3.1.4

TO_NUMBER function, 7.3

TO_REFCURSOR function, 7.3

TOO_MANY_ROWS exception, 11.4

Trace API

tracing code for tuning, 12.6.2

tracking database events, 9.12.3.11

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

rolling back, 6.7.2

savepoints, 6.7.3

visibility, 6.8.2.3

trigger

disabled

definition, 9.1

enabled

definition, 9.1

triggering statement

definition, 9.4.2

Triggers

compound, 9.4.8

triggers

accessing column values, 9.5.1

AFTER, 9.4.3, 9.5.1, 9.12.1, 9.12.1

as a stored PL/SQL subprogram, 1.3.1.3

auditing with, 9.12.1, 9.12.1

autonomous, 6.8.4

BEFORE, 9.4.3, 9.5.1, 9.12.3.7, 9.12.3.9

body, 9.5, 9.5.1.4, 9.5.1.5, 9.5.4.2

check constraints, 9.12.3.6, 9.12.3.7

column list in UPDATE, 9.4.2.2, 9.5.1.4

compiled, 9.6

conditional predicates, 9.5, 9.5.1.4

constraints and, 9.2, 9.12.2

creating, 9.3, 9.4, 9.5.4

data access restrictions, 9.12.3.7

debugging, 9.8

designing, 9.2

disabling, 9.1

enabling, 9.1

error conditions and exceptions, 9.5.1.5

events, 9.4.2

examples, 9.12

FOR EACH ROW clause, 9.4.6

generating derived column values, 9.12.3.9

illegal SQL statements, 9.5.4.2

INSTEAD OF triggers, 9.4.5

listing information about, 9.11

modifying, 9.7

mutating tables and, 9.5.4.4

naming, 9.4.1

package variables and, 9.4.3

privileges

to drop, 9.7

procedures and, 9.5.4.2

recompiling, 9.6.2

REFERENCING option, 9.5.1.3

referential integrity and, 9.12.3

remote dependencies and, 9.5.4.2

remote exceptions, 9.5.3

restrictions, 9.4.7, 9.5.4

row, 9.4.6

row evaluation order, 9.4.4

scan order, 9.4.4

stored, 9.6

use of LONG and LONG RAW datatypes, 9.5.4.3

username reported in, 9.5.5

WHEN clause, 9.4.7

triggers on object tables, 9.5.2

TRIM method

collections, 5.10.7, 13

TRUE value, 2.1.3.4

TRUST option

RESTRICT_REFERENCES, 13

tuning

allocate large VARCHAR2 variables, 12.4.2.1

avoid memory overhead, 12.4.2

computation-intensive programs, 12.8

do not duplicate built-in functions, 12.4.1.4

dynamic SQL programs, 12.9

group related subprograms into a package, 12.4.2.2

guidelines for avoiding PL/SQL performance problems, 12.4

improve code to avoid compiler warnings, 12.4.2.4

make function calls efficient, 12.4.1.2

make loops efficient, 12.4.1.3

make SQL statements efficient, 12.4.1.1

optimizing PL/SQL programs, 12.2

pin packages in the shared memory pool, 12.4.2.3

PL/SQL code, 12.3

profiling and tracing, 12.6

reducing loop overhead, 12.7

reorder conditional tests to put least expensive first, 12.4.1.5

use BINARY_FLOAT or BINARY_DOUBLE for floating-point arithmetic, 12.4.1.8

use PLS_INTEGER for integer arithmetic, 12.4.1.7

using DBMS_PROFILE and DBMS_TRACE, 12.6

using FORALL, 12.7.1

using NOCOPY, 12.10

using transformation pipelines, 12.12

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.3.1

TYPE definition

associative arrays, 5.3

collection, 5.3

collection types, 5.3

nested tables, 5.3

RECORD, 5.12

REF CURSOR, 6.5.3

VARRAY, 5.3

U

underscores, 2.1.2

unhandled exceptions

catching, 11.9.5

propagating, 11.7

UNION ALL set operator, 6.1.5.2

UNION set operator, 6.1.5.2

universal rowids, 3.1.2.5

updatable view

definition, 9.4.5

UPDATE statement

column values and triggers, 9.5.1

links to examples, 13

syntax, 13

triggers and, 9.4.2.2, 9.5.1.4

triggers for referential integrity, 9.12.3.2, 9.12.3.3

with a record variable, 5.14.3

URL (uniform resource locator), 10.10.7

UROWID

pseudocolumn, 6.1.4.3

UROWID datatype, 3.1.2.5

USE ROLLBACK SEGMENT parameter

setting transactions, 13

user-defined

exceptions in PL/SQL, 11.5

records, 5.1

usernames

as reported in a trigger, 9.5.5

USING clause

EXECUTE IMMEDIATE, 13

with OPEN FOR statement, 13

UTL_FILE package, 10.10.6

UTL_HTTP package, 10.10.7

UTL_SMTP package, 10.10.8

utlrp.sql script

for PL/SQL native compilation, 12.11.6

V

V$RESERVED_WORDS view, D

validation checks

avoiding SQL injection with, 7.4.2.2

VALUE_ERROR exception, 11.4

VALUES clause

INSERT, 13

VALUES OF clause, 12.7.1

FORALL, 13

VARCHAR subtype, 3.1.2.1.1

VARCHAR2 datatype

differences with CHAR, 3.1.2.1

variables

assigning query result to, 2.5.2

assigning values, 1.2.2.2, 2.5

bind

see bind variables

declaring, 1.2.2.1, 2.2

declaring PL/SQL, 1.2.5

global, 10.7

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

nested tables and, 5.2.2

size limit, 5.3

syntax, 13

TYPE definition, 5.3

understanding, 5.1.1

views

containing expressions, 9.4.5.1

inherently modifiable, 9.4.5.1

modifiable, 9.4.5.1

pseudocolumns, 9.4.5.1

visibility

of package contents, 10.1

scope and, 2.4

transaction, 6.8.2.3

W

warning messages

controlling PL/SQL, 11.10.2

Web applications

creating with PL/SQL, 2.10

Web server pages

creating with PL/SQL, 2.10

WHEN clause, 9.4.7

cannot contain PL/SQL expressions, 9.4.7

correlation names, 9.5.1

examples, 9.4.6, 9.12.3

EXCEPTION examples, 9.5.3, 9.12.3, 9.12.3.6, 9.12.3.7

exception handling, 13

exceptions, 11.9

using, 4.3.3, 4.3.5

WHERE CURRENT OF clause

DELETE statement, 13

UPDATE, 13

WHILE-LOOP statement

overview, 1.2.6.2

syntax, 13

using, 4.3.7

wildcards, 2.6.2.6

WNDS option

RESTRICT_REFERENCES, 13

WNPS option

RESTRICT_REFERENCES, 13

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

limitations, A.1.2

obfuscating code, A

on-the-fly, A

recommendations, A.1.1

tips, A.1.1

understanding, A.1

viewing source, A.1, A.4

with CREATE_WRAPPED, A.4

WRITE parameter

with READ to set transactions, 13

Y

YEAR

datatype field, 3.1.4

Z

ZERO_DIVIDE exception, 11.4

ZONE

part of TIMESTAMP datatype, 3.1.4.3