Index

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

Symbols

$$PLSQL_LINE inquiry directive, 2.10.1.4.1
$$PLSQL_UNIT inquiry directive, 2.10.1.4.1
% wildcard character, 2.7.5.3
%BULK_EXCEPTIONS cursor attribute, 12.4.1.4
%BULK_ROWCOUNT cursor attribute, 12.4.1.5
%FOUND cursor attribute
for implicit cursor, 6.2.1.2
for named cursor, 6.2.2.7.2
%ISOPEN cursor attribute
for implicit cursor, 6.2.1.1
for named cursor, 6.2.2.7.1
%NOTFOUND cursor attribute
for implicit cursor, 6.2.1.3
for named cursor, 6.2.2.7.3
%ROWCOUNT cursor attribute
for implicit cursor, 6.2.1.4
for named cursor, 6.2.2.7.4
%ROWTYPE attribute, 5.11.4
column alias and, 6.2.2.5
explicit cursor and, 6.2.2.5
syntax diagram, 13
virtual columns and, 5.11.4.3
%TYPE attribute, 2.3.5
initial value and, 2.3.5
NOT NULL constraint and, 2.3.5
syntax diagram, 13
_ wildcard character, 2.7.5.3

A

Abstract Data Type (ADT), 1.2.8.5
creating, 14
editioned, 14
for use in any schema, 8.13.7
accent-insensitive comparison, 2.7.5.2.3
ACCESS_INTO_NULL exception, 11.4
ADT
See Abstract Data Type (ADT)
aggregate function, 14
in PL/SQL expression, 2.7.8
pipelined table function as, 12.5.6
SQL%NOTFOUND attribute and, 6.2.1.3
alias
column
in cursor FOR LOOP, 6.3.2
in explicit cursor, 6.2.2.5
table
for avoiding inner capture, B.6
for row expression, B.6.2
for table element attribute or method, B.6.1
aliasing (problem)
SELECT BULK COLLECT INTO statement and, 12.4.2.1.1
subprogram parameter, 8.6.4
ALTER FUNCTION statement, 14
ALTER LIBRARY statement, 14
ALTER PACKAGE statement, 14
ALTER PROCEDURE statement, 14
ALTER TRIGGER statement, 14
ALTER TYPE statement, 14
AND operator, 2.7.3
anonymous block, 1.2.2
AUTHID property and, 8.13
ANYDATA data type, 12.5.2
ANYDATASET data type, 12.5.2
ANYTYPE data type, 12.5.2
architecture of PL/SQL, 1.3
array
associative
See associative array
non-PL/SQL, 5.1
assignment of value
to composite variable
collection, 5.6
record, 5.12
to scalar variable, 2.6
assignment statement, 2.6.1
syntax diagram, 13
associative array, 5.2
characteristics of, 5.1
comparisons, 5.8
declaring constant, 5.2.1
FIRST and LAST methods for, 5.9.5.1
in FORALL statement, 13
NLS parameters and, 5.2.2
pipelined table function and, 12.5.2
See also collection
atomic (lexical) unit, 2.2
atomically null collection
See null collection
attribute
%ROWTYPE
See %ROWTYPE attribute
%TYPE
See %TYPE attribute
cursor
See cursor attribute
AUTHID property, 8.13
autonomous transaction, 6.7
pipelined table function in, 13
autonomous trigger, 6.7.6
AUTONOMOUS_TRANSACTION pragma, 13
for pipelined table function, 12.5.2

B

bag data structure, 5.1
base type, 3
basic LOOP statement, 4.2.1
syntax diagram, 13
BETWEEN operator, 2.7.5.4
BINARY_DOUBLE data type
predefined constants for, 3.1.2
subtype of, 3.1.3
tuning code and, 12.3.5.1
BINARY_FLOAT data type
predefined constants for, 3.1.2
subtype of, 3.1.3
tuning code and, 12.3.5.1
BINARY_INTEGER data type
See PLS_INTEGER data type
bind variable
avoiding SQL injection with, 7.4.2.1
placeholder for
See placeholder for bind variable
blank-padding
in assignment, 3.1.4.3.2
in comparison, 3.1.4.3.3
block, 1.2.2
syntax diagram, 13
BOOLEAN data type, 3.2
BOOLEAN expression, 2.7.6
BOOLEAN static expression, 2.10.1.5.2
BOOLEAN variable, 2.6.4
built-in function
See SQL function
bulk binding, 12.4
BULK COLLECT clause, 12.4.2
aliasing and, 12.4.2.1.1
of FETCH statement, 12.4.2.2
of RETURNING INTO clause, 12.4.2.3
FORALL statement and, 12.4.3
of SELECT INTO statement, 12.4.2.1
query result set processing and, 6.3.1.2
that returns no rows, 12.4.2
bulk SQL, 12.4
in compound DML trigger, 9.3.5.3

C

C procedure, invoking, 8.14
cache, function result, 8.11
calculated column
See virtual column
call specification, 8.14
in CREATE FUNCTION statement, 14
in CREATE PROCEDURE statement, 14
in package, 10.1
call stack, AUTHID property and, 8.13
capture, B.5
cascading triggers, 9.10
CASE expression
searched, 2.7.7.2
simple, 2.7.7.1
case sensitivity
character comparison and, 2.7.5.2.3
character literal and, 2.2.3
identifier and, 2.2.2
quoted user-defined identifier, 2.2.2.3.2
keyword and, D
LIKE operator and, 2.7.5.3
reserved word and, D
CASE statement, 4.1
searched, 4.1.5
syntax diagram, 13
simple, 4.1.4
IF THEN ELSIF statement and, 4.1.3
syntax diagram, 13
CASE_NOT_FOUND exception, 11.4
case-insensitive comparison, 2.7.5.2.3
CHAR data type, 3.1.4
CHAR data type family, E
character code, 2.1
character literal, 2.2.3
See also string
character set, 2.1
CLOB data type and comparison operator, 2.7.5
CLOB data type family, E
CLOSE statement, 13
collating sequence, 2.7.5.2.3
collection, 5
as public package item, 5.10
assigning one to another, 5.6
comparing one to another, 5.8
cursor variable and, 13
declaration syntax, 13
empty, 5.1
creating with constructor, 5.5
index
See index collection
internal size of
DELETE method and, 5.9.1
EXTEND method and, 5.9.3
TRIM method and, 5.9.2
multidimensional, 5.7
null, 5.1
assigning to collection variable, 5.6.2
pipelined table function and, 12.5.1
retrieving query results into, 12.4.2
types of, 5.1
collection constructor, 5.5
collection method, 5.9
as subprogram parameter, 5.9
invocation syntax, 13
null collection and, 5.9
COLLECTION_IS_NULL exception, 11.4
column alias
in cursor FOR LOOP, 6.3.2
in explicit cursor, 6.2.2.5
comment, 2.2.4
nested, 2.2.4.2
PL/SQL Wrapper utility and, A.3
syntax diagram, 13
COMMIT statement, 6.6.1
FOR UPDATE cursor and, 6.6.6.3
in autonomous transaction, 6.7.5.2
comparison
of collections, 5.8
of records, 5.13
comparison operator, 2.7.5
cursor variable and, 13
compatible data type
for collection variables, 5.6.1
for scalar variables, 2.6
compilation
conditional, 2.10
for native execution, 12.9
interpreted, 12.9.5
compilation parameter, 1.3.2
displaying value of, 2.10.1.4.1
predefined inquiry directive for, 2.10.1.4.1
compiler directive
See pragma
compile-time warning, 11.1
composite data type, 5
composite variable, 5
compound DML trigger, 9.3.5
computation-intensive code, 12.3.5
concatenation operator (||), 2.7.1
concurrent transactions, 6.7.5.4
condition, SQL multiset, 5.8.3
conditional compilation, 2.10
conditional compilation directive, 2.10.1
error, 2.10.1.3
inquiry, 2.10.1.4
restrictions on, 2.10.4
selection, 2.10.1.2
conditional predicate, 9.3.1
conditional selection statement, 4.1
conditional trigger, 9.1
constant
declaring, 2.3.2
associative array, 5.2.1
record, 5.11.2
syntax diagram, 13
initial value of, 2.3.3
predefined, 3.1.2
static, 2.10.1.5.4
in DBMS_DB_VERSION package, 2.10.1.5.5
constrained subtype, 3.4.2
in performance-critical code, 12.3.5.2
subprogram parameter and, 8.6.1
constraint
cursor parameter and, 13
NOT NULL
See NOT NULL constraint
trigger compared to, 9.2
constructor
See collection constructor
context of transaction, 6.7.2
CONTINUE statement, 4.2.4
syntax diagram, 13
CONTINUE WHEN statement, 4.2.5
syntax diagram, 13
control statement, 4
control token, 2.10.1.1
correlated subquery, 6.3.4
correlation name, 9.3.2
with LONG or LONG RAW column, 9.9.2
COUNT collection method, 5.9.6
CREATE FUNCTION statement, 14
CREATE LIBRARY statement, 14
CREATE PACKAGE statement, 14
CREATE TRIGGER statement, 14
CREATE TYPE BODY statement, 14
CREATE TYPE statement, 14
CREATE_WRAPPED procedure, A.4
crossedition trigger, 9.3
CURRENT OF clause, 6.6.6.2
FOR UPDATE cursor and, 6.6.6.2
ROWID pseudocolumn instead of, 6.6.6.3
CURRENT_USER, 8.13.1
CURRVAL pseudocolumn, 6.1.2.1
cursor, 6.2
explicit
See explicit cursor
FOR UPDATE, 6.6.6.2
after COMMIT or ROLLBACK, 6.6.6.3
implicit
See implicit cursor
in SERIALLY_REUSABLE package, 10.7.2
named, 6.2.2
pipelined table function and, 12.5.5
See also explicit cursor and cursor variable
nested, 6.5
cursor attribute
for cursor variable, 6.4.6
for explicit cursor, 6.2.2.7
%FOUND, 6.2.2.7.2
%ISOPEN, 6.2.2.7.1
%NOTFOUND, 6.2.2.7.3
%ROWCOUNT, 6.2.2.7.4
for implicit cursor, 6.2.1
DBMS_SQL package and, 7.3
native dynamic SQL and, 7.2
SQL%BULK_EXCEPTIONS, 12.4.1.4
SQL%BULK_ROWCOUNT, 12.4.1.5
SQL%FOUND, 6.2.1.2
SQL%ISOPEN, 6.2.1.1
SQL%NOTFOUND, 6.2.1.3
SQL%ROWCOUNT, 6.2.1.4
where you can use, 6.2
CURSOR expression, 6.5
passing to pipelined table function, 12.5.6
cursor FOR LOOP statement
query result set processing with, 6.3.2
recursive invocation in, 8.9
syntax diagram, 13
cursor parameter, 6.2.2.6
cursor specification, 13
cursor variable, 6.4
declaration syntax diagram, 13
CURSOR_ALREADY_OPEN exception, 11.4

D

data abstraction, 1.2.8
data definition language statement
See DDL statement
Data Pump Import and triggers, 9.13
data type
collection
See collection
compatible
for collection variables, 5.6.1
for scalar variables, 2.6
composite, 5
object
See Abstract Data Type (ADT)
of expression, 2.7
predefined, E
RECORD
See record
scalar, 3
SQL, 3.1
user-defined
See Abstract Data Type (ADT)
what it determines, 3
See also subtype
data type conversion, 3.1
implicit
See implicit data type conversion
SQL injection and, 7.4.1.3
data type family, 3
overloaded subprogram and, 8.8
predefined data types grouped by, E
subtypes with base types in same, 3.4.3
database character set, 2.1.1
database link, IR, 8.13.6
DATABASE trigger, 9.4.2
DATE data type family, E
DBMS_ASSERT package, 7.4.2.2
DBMS_DB_VERSION package, 2.10.1.5.5
DBMS_DDL package, A.4
DBMS_PARALLEL_EXECUTE package, 12.6
DBMS_PREPROCESSOR package, 2.10.3
DBMS_PROFILE package, 12.8.1
DBMS_SQL package, 7.3
switching to native dynamic SQL from, 7.3
DBMS_SQL.TO_CURSOR_NUMBER function, 7.3.2
DBMS_SQL.TO_REFCURSOR function, 7.3.1
DBMS_STANDARD package, 2.4
DBMS_TRACE package, 12.8.2
DBMS_WARNING package, 11.1.1
dbmsupgin.sql script, 12.9.5
dbmsupgnv.sql script, 12.9.5
DDL statement
dynamic SQL for, 7
in trigger, 6.7.6
subprogram side effects and, 8.12
deadlock
autonomous transaction and, 6.7.5.4
implicit rollback and, 6.6.4
declaration, 2.3
exception raised in, 11.8.1
default value
of cursor parameter, 6.2.2.6.1
of subprogram parameter, 8.6.5
See also initial value
DEFINE
binding category, 12.4
PL/SQL Wrapper utility and, A.3
DEFINER, 8.13.1
definer’s rights unit
See DR unit
DELETE collection method, 5.9.1
COUNT method and, 5.9.6
EXISTS method and, 5.9.4
EXTEND method and, 5.9.3
FIRST method and, 5.9.5
LAST method and, 5.9.5
NEXT method and, 5.9.8
PRIOR method and, 5.9.8
TRIM method and, 5.9.2
DELETE statement
BEFORE statement trigger and, 9.8
PL/SQL extension to, 13
See also DML statement
DELETING conditional predicate, 9.3.1
delimiter, 2.2.1
dense collection, 5.1
Descriptive Intermediate Attributed Notation for Ada (DIANA), C
DETERMINISTIC option, 13
for pipelined table function, 12.5.2
directive
compiler
See pragma
error, 2.10.1.3
inquiry, 2.10.1.4
selection, 2.10.1.2
See also conditional compilation directive
DML statement
in FORALL statement, 12.4.1
inside pipelined table function, 12.5.2
on pipelined table function result, 12.5.7
PL/SQL syntax of, 6.1.1
repeating efficiently, 12.4.1
DML statement statement
avoiding inner capture in, B.6
DML trigger, 9.3
dot notation
for collection method, 5.9
for identifier in named PL/SQL unit, 2.4
for pseudocolumn, 6.1.2.1
for record field, 5
name resolution and, B.1
double quotation mark ("), 2.1.1
DR unit
call stack and, 8.13
dynamic SQL and, 8.13
name resolution and, 8.13
privilege checking and, 8.13
SCHEMA trigger and, 9.4.1
SET ROLE command and, 8.13.2
static SQL and, 8.13
See also AUTHID property
DROP FUNCTION statement, 14
DROP LIBRARY statement, 14
DROP PACKAGE statement, 14
DROP PROCEDURE statement, 14
DROP TRIGGER statement, 14
DROP TYPE BODY statement, 14
DUP_VAL_ON_INDEX exception, 11.4
dynamic SQL, 7
AUTHID property and, 8.13
native, 7.2
switching to DBMS_SQL package from, 7.3
placeholder for bind variable in
EXECUTE IMMEDIATE statement and, 7.2.1
repeated, 7.2.3
tuning, 12.2

E

editioned Abstract Data Type (ADT), 14
element of collection, 5
embedded SQL
See static SQL
empty collection, 5.1
creating with constructor, 5.5
error directive, 2.10.1.3
error handling, 11
error-reporting function
SQLCODE, 13
SQLERRM, 13
SQL%BULK_EXCEPTIONS and, 12.4.1.4
escape character, 2.7.5.3
escaped identifier, B.4
evaluation order, 2.7.2
events publication, 9.14
evolution of type, 14
exception
handling, 11.2
in FORALL statement, 12.4.1.2
in trigger, 9.7
See also exception handler
internally defined
See internally defined exception
predefined
See predefined exception
raised in cursor FOR LOOP statement, 6.3.2
raised in declaration, 11.8.1
raised in exception handler, 11.8.2
raising explicitly, 11.7
reraising, 11.7.1.3
unhandled, 11.9
in FORALL statement, 12.4.1.2
user-defined
See user-defined exception
exception handler, 11.2
continuing execution after, 11.11
exception raised in, 11.8.2
for NO_DATA_NEEDED, 12.5.8
GOTO statement and, 13
locator variables for, 11.2.2
retrieving error code and message in, 11.10
retrying transaction after, 11.12
syntax diagram, 13
EXCEPTION_INIT pragma, 13
for giving error code to user-defined exception, 11.7.2
for giving name to internally defined exception, 11.3
EXECUTE IMMEDIATE statement, 7.2.1
syntax diagram, 13
EXISTS collection method, 5.9.4
EXIT statement, 4.2.2
syntax diagram, 13
EXIT WHEN statement, 4.2.3
in basic LOOP statement, 4.2.3
syntax diagram, 13
exiting a loop, 4.2
explicit cursor, 6.2.2
declaration syntax diagram, 13
in package
declaring, 10.8
opening and closing, 10.7.3
query result processing with
in FOR LOOP statement, 6.3.2
with OPEN, FETCH, and CLOSE statements, 6.3.3
explicit format model, 7.4.2.3
expression, 2.7
CURSOR, 6.5
passing to pipelined table function, 12.5.6
data type of, 2.7
in explicit cursor, 6.2.2.5
SQL function in PL/SQL, 2.7.8
static, 2.10.1.5, 2.10.1.5
syntax diagram, 13
EXTEND collection method, 5.9.3
external subprogram, 8.14

F

FETCH statement
across COMMIT, 6.6.6.3
record variable and, 5.12.2.2
syntax diagram, 13
that returns no row, 6.2.2.3
with BULK COLLECT clause, 12.4.2.2
with cursor variable, 6.4.3
with explicit cursor, 6.2.2.3
field of record, 5
FIRST collection method, 5.9.5
FOR LOOP statement, 4.2.6
bounds of, 4.2.6.2
FORALL statement and, 12.4.1
STEP clause and, 4.2.6
syntax diagram, 13
See also cursor FOR LOOP statement
FOR UPDATE cursor, 6.6.6.2
after COMMIT or ROLLBACK, 6.6.6.3
FORALL statement, 12.4.1, 12.4.1
associative array in, 13
bulk binding and, 12.4
BULK COLLECT clause and, 12.4.3
for sparse collection, 12.4.1.1
SQL%BULK_EXCEPTIONS and, 12.4.1.4.1
handling exception raised in
after FORALL completes, 12.4.1.4
immediately, 12.4.1.3
number of rows affected by, 12.4.1.5
syntax diagram, 13
unhandled exception in, 12.4.1.2
format model, 7.4.2.3
forward declaration of subprogram, 8.5
function
aggregate
See aggregate function
built-in
See SQL function
declaration syntax diagram, 13
error-reporting
SQLCODE, 13
SQLERRM, 12.4.1.4
invoking, 8.3
in SQL statement, 8.12
options for, 8.4.1
SQL
See SQL function
structure of, 8.4.1
table
See table function
See also subprogram
function result cache, 8.11
function specification, 13

G

generated column
See virtual column
global identifier, 2.5
GOTO statement, 4.3.1
restrictions on, 13
syntax diagram, 13

H

hardware arithmetic, 12.3.5.1
hash table, 5.1
hiding PL/SQL source text
See wrapping PL/SQL source text
host variable
bulk-binding, 12.4.4
cursor variable as, 6.4.8
packages and, 10.2

I

identifier, 2.2.2
ambiguous reference to, B
escaped, B.4
global, 2.5
in static SQL, 6.1.1
local, 2.5
reference to, 2.4
scope of, 2.5
user-defined, 2.2.2.3
collecting data about, 12.7
visibility of, 2.5
See also name
IDL, C
IF statement, 4.1
IF THEN form, 4.1.1
IF THEN ELSE form, 4.1.2
IF THEN ELSIF form, 4.1.3
nested IF THEN ELSE statement and, 4.1.3
simple CASE statement and, 4.1.3
nested, 4.1.2
syntax diagram, 13
imp and triggers, 9.13
implicit cursor, 6.2.1
CURSOR expression with, 6.5
declaration syntax, 13
dynamic SQL and, 7.3
query result processing with
with cursor FOR LOOP statement, 6.3.2
with SELECT INTO statement, 6.3.1
implicit data type conversion
minimizing, 12.3.5.3
of subprogram parameter, 8.6.2
causing overload error, 8.8.3
of subtypes
constrained, 3.4.2
unconstrained, 3.4.1
with base types in same family, 3.4.3
implicit ROLLBACK statement, 6.6.4
Import and triggers, 9.13
IN operator, 2.7.5.5
IN OUT parameter mode, 8.6.3
IN parameter mode, 8.6.3
in-bind, 12.4
independent transaction
See autonomous transaction
index collection, 12.4.1.1
representing subset with, 12.4.1.1
index of collection, 5
index-by table
See associative array
infinite loop, 4.2.1
INFORMATIONAL compile-time warning, 11.1
initial value
%TYPE attribute and, 2.3.5
NOT NULL constraint and, 2.3.4
of constant, 2.3.3
of variable
nested table, 5.4
record, 5.11.1
scalar, 2.3.3
varray, 5.3
See also default value
initialization parameter, 1.3.2
INLINE pragma, 12.1.1
syntax diagram, 13
inner capture, B.5.3
avoiding, B.6
input, 1.2.7
inquiry directive, 2.10.1.4
INSERT statement
inserting record with, 5.14
restrictions on, 5.16
PL/SQL extension to, 13
See also DML statement
INSERTING conditional predicate, 9.3.1
instance method, IR, 8.13.8
INSTEAD OF trigger, 9.1
for CREATE statement, 9.4.3
on DML statement, 9.3.4
compound, 9.3.5.1
for pipelined table function result, 12.5.7
on nested table column, 9.3.4
Interface Definition Language (IDL), C
internally defined exception, 11.3
giving name to, 11.3
raising explicitly, 11.7.1.2
interpreted compilation, 12.9.5
INVALID_CURSOR exception, 11.4
INVALID_NUMBER exception, 11.4
invoker’s rights unit
See IR unit
IR unit
Abstract Data Type (ADT), 8.13.7
call stack and, 8.13
database link, 8.13.6
dynamic SQL and, 8.13
instance method, 8.13.8
invoked by trigger, 8.13.5
invoked by view, 8.13.5
name resolution and, 8.13
overriding name resolution in, 8.13.4
privilege checking and, 8.13
static SQL and, 8.13
template objects in, 8.13.3
See also AUTHID property
IS [NOT] NULL operator, 2.7.5.1
collections and, 5.8.1
isolation level of transaction, 6.7.3

J

Java class method invocation, 8.14

K

key-value pair
See associative array
keywords, 2.2.2.1
list of, D

L

labeled LOOP statement, 4.2.3
LAST collection method, 5.9.5
LEVEL pseudocolumn, 6.1.2
lexical unit, 2.2
library
creating, 14
dropping, 14
explicitly recompiling, 14
library arithmetic, 12.3.5.1
LIKE operator, 2.7.5.3
LIMIT clause, 12.4.2.2.1
LIMIT collection method, 5.9.7
line-continuation character, 2.2.3
literal, 2.2.3
local identifier, 2.5
locator variable, 11.2.2
lock mode, 6.6.6.1
LOCK TABLE statement, 6.6.6.1
locking
overriding default, 6.6.6
result set row, 6.6.6.2
table, 6.6.6.1
logical operator, 2.7.3
logical value, 3.2
LOGIN_DENIED exception, 11.4
LONG data type, 3.1.5
in trigger, 9.9.2
LONG RAW data type, 3.1.5
in trigger, 9.9.2
LOOP statement
exiting, 4.2
kinds of, 4.2
labeled, 4.2, 4.2.3
nested, 4.2.3
optimizing, 12.3.4
LOOP UNTIL structure, 4.2.7

M

MALFORMED_WRAP_INPUT exception, A.4
manageability, 1.1.6
materialized view, trigger and, 14
membership test, 2.7.5.5
Method 4, 7.3
method, collection
See collection method
mixed parameter notation, 8.6.6
mode
lock, 6.6.6.1
subprogram parameter, 8.6.3
multibyte character set
as database character set, 2.1.1
variables for values from, 3.1.4.2
multidimensional collection, 5.7
multiline comment, 2.2.4.2
multiple data transformations, 12.5
multiset condition, 5.8.3
mutating table, 9.9.3
mutating-table error
for function, 8.12
for trigger, 9.9.3

N

name
qualified
See dot notation
qualified remote, 2.4
remote, 2.4
simple, 2.4
See also identifier
name resolution, B
AUTHID property and, 8.13
in static SQL, B.4
overriding in IR unit, 8.13.4
PL/SQL and SQL differences, B.3
named cursor, 6.2.2
pipelined table function and, 12.5.5
See also explicit cursor and cursor variable
named parameter notation, 8.6.6
national character set, 2.1.2
native dynamic SQL, 7.2
switching to DBMS_SQL package from, 7.3
native execution, compilation for, 12.9
NATURAL subtype, 3.3.2
NATURALN subtype, 3.3.2
nested comment, 2.2.4.2
nested cursor, 6.5
nested IF statement, 4.1.2
IF THEN ELSIF form and, 4.1.3
nested LOOP statement, 4.2.3
nested record
assignment example, 5.12.1
declaration example, 5.11.3
nested subprogram, 8.2
declaration and definition of, 8.2
forward declaration for, 8.5
nested table, 5.4
assigning null value to, 5.6.2
assigning set operation result to, 5.6.3
characteristics of, 5.1
column in view, trigger on, 9.3.4
comparing to NULL, 5.8.1
comparing two, 5.8.2
correlation names and, 9.3.2
COUNT method for, 5.9.6.2
FIRST and LAST methods for, 5.9.5.3
returned by function, 12.5.1
SQL multiset conditions and, 5.8.3
See also collection
nested transaction, 6.7
NEW correlation name, 9.3.2
with LONG or LONG RAW column, 9.9.2
new features, Preface
NEXT collection method, 5.9.8
NEXTVAL pseudocolumn, 6.1.2.1
NLS parameters
associative array and, 5.2.2
character comparison and, 2.7.5.2.3
SQL injection and, 7.4.1.3
NO_DATA_FOUND exception, 11.4
NO_DATA_NEEDED exception, 12.5.8
SQLCODE for, 11.4
NOCOPY hint, 13
subprogram parameter aliasing and, 8.6.4.1
tuning subprogram invocation with, 12.3.3
nonpadded comparison semantics, 3.1.4.3.3
no-op (no operation) statement, 4.3.2
NOT NULL constraint, 2.3.4
%TYPE attribute and, 2.3.5
EXTEND method and, 5.9.3
NOT operator, 2.7.3
NOT_LOGGED_ON exception, 11.4
null collection, 5.1
assigning to collection variable, 5.6.2
collection method and, 5.9
NULL statement
syntax diagram, 13
uses for, 4.3.2
null string, 2.2.3
NULL value
assigning to record variable, 5.12.3
comparing to collection
associative array, 5.8
nested table, 5.8.1
varray, 5.8.1
comparison operator and, 2.7.5
concatenation operator and, 2.7.1
for $$PLSQL_UNIT inquiry directive, 2.10.1.4.1
for collection variable, 5.6.2
for subprogram parameter, 8.6.5
for unresolvable inquiry directive, 2.10.1.4.3
in control statement, 2.7.3
IN operator and, 2.7.5.5
in set, 2.7.5.5
simple CASE expression and, 2.7.7.1
simple CASE statement and, 4.1.4
NUMBER data type family
inefficiency of, 12.3.5.1
members of, E

O

obfuscating PL/SQL source text
See wrapping PL/SQL source text
object type
See Abstract Data Type (ADT)
OBJECT_VALUE pseudocolumn, 9.3.3
OCI
associative array and, 5.2.3
cursor variable and, 6.4.8
OLD correlation name, 9.3.2
OPEN FOR statement, 13
recursive invocation and, 8.9
OPEN statement, 13
recursive invocation and, 8.9
operation, 2.7.2
operator
comparison, 2.7.5
cursor variable and, 13
logical, 2.7.3
relational, 2.7.5.2
collection and, 5.8
operator precedence, 2.7.2
optimizer
PL/SQL, 12.1
SQL, 12.5.5
OR operator, 2.7.3
Oracle Call Interface (OCI)
associative array and, 5.2.3
cursor variable and, 6.4.8
Oracle RAC environment, result caches in, 8.11.5.6
ORA-n error
See internally defined exception
ordinary user-defined identifier, 2.2.2.3.1
Original Import and triggers, 9.13
OUT parameter mode, 8.6.3
out-bind, 12.4
outer capture, B.5.1
output, 1.2.7
overloaded subprogram, 8.8
INLINE pragma and, 12.1.1

P

package, 10.1
as application, 10.1
body of
See package body
DBMS_STANDARD, 2.4
explicitly recompiling, 14
features of, 10.2
guidelines for writing, 10.8
initialization of, 10.5
of static constants, 2.10.1.5.4
private items in, 10.4
product-specific, 10
public items in
See public package item
reasons to use, 10.2
SERIALLY_REUSABLE, 10.7
specification of
See package specification
STANDARD
See STANDARD package
state of, 10.6
supplied by Oracle, 10
wrapping, A
guideline for, A.2
package body, 10.1
creating, 14
dropping, 14
initialization part of, 10.4
assigning initial values in, 10.8
replacing, 14
package specification, 10.1
creating, 14
cursor variable in, 13
dropping, 14
replacing, 14
See also public package item
package subprogram, 8.2
parallel DML
bulk binding and, 12.4
for large table, 12.6
PARALLEL_ENABLE option, 13
for pipelined table function, 12.5.2
for table function, 12.5.1
parameter
compilation
See compilation parameter
explicit cursor, 6.2.2.6
initialization, 1.3.2
subprogram
See subprogram parameter
parameter mode, 8.6.3
PARENT correlation name, 9.3.2
with LONG or LONG RAW column, 9.9.2
parentheses
nested, 2.7.2
to control evaluation order, 2.7.2
to improve readability, 2.7.2
pattern matching, 2.7.5.3
percent sign (%) wildcard character, 2.7.5.3
PERFORMANCE compile-time warning, 11.1
PIPE ROW statement, 12.5.2
PIPELINED option, 12.5.1
where to specify, 12.5.2
pipelined table function, 12.5.1
as aggregate function, 12.5.6
in autonomous transaction, 13
See also table function
placeholder for bind variable
in conditional compilation directive, 2.10.4
in dynamic SQL
EXECUTE IMMEDIATE statement and, 7.2.1
repeated, 7.2.3
in static SQL, 6.1.1
OPEN FOR statement and, 6.4.2
in trigger body, 9.3.2
PLS_INTEGER data type, 3.3
tuning code and, 12.3.5.1
PLS_INTEGER static expression, 2.10.1.5.1
PL/Scope tool, 12.7
PL/SQL architecture, 1.3
PL/SQL block
See block
PL/SQL engine, 1.3.1, 1.3.1
PL/SQL function result cache, 8.11
PL/SQL language
advantages of, 1.1
high performance of, 1.1.2
high productivity with, 1.1.3
lexical units of, 2.2
limits of, C
main features of, 1.2
manageability and, 1.1.6
new features of, Preface
portability of, 1.1.4
program limits of, C
scalability of, 1.1.5
SQL integration in, 1.1.1
syntax and semantics, 13
PL/SQL optimizer, 12.1
PL/SQL table
See associative array
PL/SQL unit, 1.3.2
stored
See stored PL/SQL unit
PL/SQL Wrapper utility, A.3
PLSQL_CCFLAGS compilation parameter, 2.10.1.4.2
PLSQL_OPTIMIZE_LEVEL compilation parameter, 12.1
PLSQL_WARNINGS compilation parameter
displaying value of
with ALL_PLSQL_OBJECT_SETTINGS view, 11.1
with DBMS_WARNING subprogram, 11.1.1
setting value of
with ALTER statements, 11.1
with PLSQL_WARNINGS subprogram, 11.1.1
portability, 1.1.4
positional parameter notation, 8.6.6
POSITIVE subtype, 3.3.2
POSITIVEN subtype, 3.3.2
post-processed source text, 2.10.3
pragma, 2.9
AUTONOMOUS_TRANSACTION, 13
for pipelined table function, 12.5.2
EXCEPTION_INIT, 13
INLINE, 12.1.1
syntax diagram, 13
RESTRICT_REFERENCES, 13
SERIALLY_REUSABLE, 13
precedence, operator, 2.7.2
predefined constant, 3.1.2
predefined data type, E
predefined exception, 11.4
raising explicitly, 11.7.1.2
redeclared, 11.6
predefined inquiry directive, 2.10.1.4.1
predefined subtype, E
preprocessor control token, 2.10.1.1
PRIOR collection method, 5.9.8
privilege checking and AUTHID property, 8.13
procedure
declaration syntax, 13
invoking, 8.3
structure of, 8.4.1
See also subprogram
procedure specification, 13
product-specific package, 10
Profiler API, 12.8.1
profiling and tracing programs, 12.8
program limits, C
PROGRAM_ERROR exception, 11.4
pseudocolumn, 6.1.2
pseudoinstruction
See pragma
pseudorecord, 9.3.2
See also correlation name
public package item
appropriate, 10.3.1
collection type as, 5.10
cursor variable as, 13
declaring, 10.3
RECORD type as, 5.11.3, 5.11.3
referencing, 10.3
remote variable, 10.3.1
scope of, 10.3
visibility of, 10.3
publishing events, 9.14
purity rules for subprograms, 8.12

Q

qualified name
See dot notation
qualified remote name, 2.4
query
invoking function in, 12.3.2
processing result set of, 6.3
multiple-row dynamic query, 7.2.2
See also SELECT INTO statement
quotation mark, single or double, 2.1.1
quoted user-defined identifier, 2.2.2.3.2

R

RAISE statement, 11.7.1
syntax diagram, 13
RAISE_APPLICATION_ERROR procedure, 11.7.2
raising exception explicitly, 11.7
range test, 2.7.5.4
read-only transaction, 6.6.5
read-write transaction, 6.6.5
recompiling stored PL/SQL unit, 14
record, 5
as public package item, 5.11.3
assigning value to, 5.12
creating, 5.11
syntax diagram, 13
declaring constant, 5.11.2
nested
See nested record
representing row, 5.11.4
types of, 5.11.3
recursive subprogram, 8.9
result-cached, 8.11.4.2
recursive trigger, 9.8
REF CURSOR
See cursor variable
REF CURSOR type, 6.4.1
relational operator, 2.7.5.2
collection and, 5.8
RELEASE constant, 2.10.1.5.5
remote exception handling
subprograms and, 11.8
triggers and, 9.7
remote name, 2.4
remote public package variable, 10.3.1
remote subprogram
with composite parameter, 5
REPEAT UNTIL structure, 4.2.7
replacing stored PL/SQL unit, 14
reraising exception, 11.7.1.3
reserved preprocessor control token, 2.10.1.1
reserved words
information about, 2.2.2.1
list of, D
RESTRICT_REFERENCES pragma, 13
result cache, 8.11
RESULT_CACHE clause, 8.11.1
RESULT_CACHE option for function, 13
RETURN clause of function, 8.4.1
RETURN INTO clause
See RETURNING INTO clause
RETURN statement, 8.4.2
RETURNING INTO clause, 13
BULK COLLECT clause of, 12.4.2.3
FORALL statement and, 12.4.3
REUSE SETTINGS clause, 1.3.2
ROLLBACK statement, 6.6.2
FOR UPDATE cursor and, 6.6.6.3
implicit, 6.6.4
in autonomous transaction, 6.7.5.2
transparent, 9.8
rowid, 3.1.6
ROWID data type, 3.1.6
ROWID pseudocolumn, 6.1.2
instead of CURRENT OF clause, 6.6.6.3
row-level trigger, 9.3
ROWNUM pseudocolumn
bulk SELECT operation and, 12.4.2.1.2
single-row result set and, 6.3.1.1
ROWTYPE_MISMATCH exception, 11.4
runtime error
See exception

S

same-scope capture, B.5.2
SAMPLE clause, 12.4.2.1.2
SAVEPOINT statement, 6.6.3
in autonomous transaction, 6.7.5.3
scalability
SERIALLY_REUSABLE packages and, 10.7
subprograms and, 1.1.5
scalar data type, 3
scalar variable
assigning value to, 2.6
declaration, 2.3.1
syntax diagram, 13
initial value of, 2.3.3
SCHEMA trigger, 9.4.1
scope of identifier, 2.5
searched CASE expression, 2.7.7.2
searched CASE statement, 4.1.5
syntax diagram, 13
security mechanism
against SQL injection, 7.4
PL/SQL source text wrapping
benefit of, A
limitations of, A.1
trigger as, 9.2
SELECT FOR UPDATE statement, 6.6.6.2
SELECT INTO statement
assigning values with
to record variable, 5.12.2.1
to scalar variables, 2.6.2
avoiding inner capture in, B.6
query result set processing with, 6.3.1
SQL%NOTFOUND attribute and, 6.2.1.3
SQL%ROWCOUNT attribute and, 6.2.1.4
syntax diagram, 13
with BULK COLLECT clause, 12.4.2.1
See also query
selection directive, 2.10.1.2
selector
in simple CASE expression, 2.7.7.1
in simple CASE statement, 4.1.4
SELF_IS_NULL exception, 11.4
sequence, 6.1.2.1
sequential control statement, 4.3
SERIALLY_REUSABLE package, 10.7
SERIALLY_REUSABLE pragma, 13
session cursor, 6.2
set data structure, 5.1
set membership test, 2.7.5.5
SET ROLE command and AUTHID property, 8.13.2
SET TRANSACTION statement, 6.6.5
SEVERE compile-time warning, 11.1
short-circuit evaluation
how it works, 2.7.4
tuning code and, 12.3.7
side effects of subprogram, 8.10
SIGNTYPE subtype, 3.3.2
simple CASE expression, 2.7.7.1
simple CASE statement, 4.1.4
IF THEN ELSIF statement and, 4.1.3
syntax diagram, 13
simple DML trigger, 9.3
simple name, 2.4
SIMPLE_DOUBLE subtype, 3.1.3
tuning code and, 12.3.5.1
SIMPLE_FLOAT subtype, 3.1.3
tuning code and, 12.3.5.1
SIMPLE_INTEGER subtype, 3.3.3
tuning code and, 12.3.5.1
single quotation mark (’), 2.1.1
single-line comment, 2.2.4.1
sparse collection, 5.1
FORALL statement for, 12.4.1.1
SQL%BULK_EXCEPTIONS and, 12.4.1.4.1
traversing, 5.9.8
specification
cursor, 13
function, 13
package
See package specification
procedure, 13
SQL
bulk, 12.4
in compound DML trigger, 9.3.5.3
dynamic
See dynamic SQL
static
See static SQL
SQL cursor
See implicit cursor
SQL data type, 3.1
SQL function
in PL/SQL expression, 2.7.8
tuning and, 12.3.6
SQL injection, 7.4
SQL integration in PL/SQL, 1.1.1
SQL multiset condition, 5.8.3
SQL MULTISET operator, 5.6.3
SQL optimizer, 12.5.5
SQL statement
for stored PL/SQL unit, 14
in trigger, 9.1
invoking collection method in, 5.9
invoking PL/SQL function in, 8.12
tuning, 12.3.1
See also anonymous block
SQL%BULK_EXCEPTIONS cursor attribute, 12.4.1.4
SQL%BULK_ROWCOUNT cursor attribute, 12.4.1.5
SQL%FOUND cursor attribute, 6.2.1.2
SQL%NOTFOUND cursor attribute, 6.2.1.3
SQL%ROWCOUNT cursor attribute, 6.2.1.4
SQL*Loader and triggers, 9.13
SQLCODE function, 13
SQLERRM function, 13
SQL%BULK_EXCEPTIONS and, 12.4.1.4
SQLJ object type, creating, 14
standalone subprogram, 8.2
function
creating, 14
dropping, 14
explicitly recompiling, 14
replacing, 14
procedure
creating, 14
dropping, 14
explicitly recompiling, 14
replacing, 14
STANDARD package
data type defined in
See predefined data type
exception defined in
See predefined exception
how it defines PL/SQL environment, 10.10
listing identifiers defined in, 2.2.2.2
referencing item defined in, 2.4
statement injection, 7.4.1.2
statement modification, 7.4.1.1
statement-level trigger, 9.3
static constant, 2.10.1.5.4
in DBMS_DB_VERSION package, 2.10.1.5.5
static expression, 2.10.1.5, 2.10.1.5
static SQL, 6
AUTHID property and, 8.13
name resolution in, B.4
placeholder for bind variable in, 6.1.1
OPEN FOR statement and, 6.4.2
PL/SQL identifier in, 6.1.1
STORAGE_ERROR exception, 11.4
recursive invocation and, 8.9
store table, 5.4.2
stored PL/SQL unit, 1.3.2
creating, 14
recompiling, 14
replacing, 14
wrapping, A
stored subprogram, 8.2
unhandled exception in, 11.9
wrapping, A
string
null, 2.2.3
zero-length, 2.2.3
See also character literal
STRING subtype, 3.1.4.3.1
strong REF CURSOR type
creating, 6.4.1
FETCH statement and, 6.4.3
subprogram, 8
inlining, 12.1.1
invoked by trigger, 9.5
remote
See remote subprogram
unhandled exception in, 11.9
subprogram invocation
optimization of, 12.1.1
resolution of, 8.7
syntax of, 8.3
tuning, 12.3.3
subprogram parameter, 8.6
collection as, 5.9
CURSOR expression as actual, 6.5
cursor variable as, 6.4.7
optional, 8.6.5
pseudorecord as, 9.3.2
query result as, 6.4.7
required, 8.6.5
subquery
correlated, 6.3.4
result set processing with, 6.3.4
SUBSCRIPT_BEYOND_COUNT exception, 11.4
SUBSCRIPT_OUTSIDE_LIMIT exception, 11.4
subtype, 3
constrained, 3.4.2
subprogram parameter and, 8.6.1
of BINARY_DOUBLE data type, 3.1.3
of BINARY_FLOAT data type, 3.1.3
of PLS_INTEGER data type, 3.3.2
predefined, E
unconstrained, 3.4.1
user-defined, 3.4
See also data type
synonym, 2.4
SYS_INVALID_ROWID exception, 11.4
SYS_REFCURSOR type, 6.4.1
system trigger, 9.4

T

table
hash, 5.1
index-by
See associative array
mutating, 9.9.3
nested, 5.4
characteristics of, 5.1
parallel DML for large, 12.6
PL/SQL
See associative array
store, 5.4.2
unordered, 5.1
updating large in parallel, 12.6
table alias
for avoiding inner capture, B.6
for row expression, B.6.2
for table element attribute or method, B.6.1
table function, 12.5.1
pipelined
See pipelined table function
weak cursor variable argument to, 6.4.1
TCL statement, 6.1.1
in subprogram invoked by trigger, 9.5
in trigger, 6.7.6
TIMEOUT_ON_RESOURCE exception, 11.4
timing point
of compound DML trigger, 9.3.5.1
of simple DML trigger, 9.3
of system trigger, 9.4
trigger firing order and, 9.10
TO_CURSOR_NUMBER function, 7.3.2
TO_REFCURSOR function, 7.3.1
TOO_MANY_ROWS exception, 11.4
Trace API, 12.8.2
trace file, 11
tracing and profiling programs, 12.8
transaction
autonomous, 6.7
pipelined table function in, 13
ending
with COMMIT statement, 6.6.1
with ROLLBACK statement, 6.6.2
nested, 6.7
read-only, 6.6.5
read-write, 6.6.5
retrying after handling exception, 11.12
SQL%ROWCOUNT cusor attribute and, 6.2.1.4
Transaction Control Language
See TCL statement
TRANSACTIONS initialization parameter, 6.7.5.4
trigger, 9
as security mechanism, 9.2
AUTHID property and, 8.13
autonomous, 6.7.6
cascading, 9.10
DDL statement in, 6.7.6
hiding implementation details of, A.1
IR subprogram invoked by, 8.13.5
materialized view and, 14
recursive, 9.8
TCL statement in, 6.7.6
TRIM collection method, 5.9.2
tri-state logic, 2.7.3
tuning PL/SQL code, 12
type
See data type
type-compatible data type
for collection variables, 5.6.1
for scalar variables, 2.6

U

unconstrained subtype, 3.4.1
underscore (_) wildcard character, 2.7.5.3
unhandled exception, 11.9
in FORALL statement, 12.4.1.2
unordered table, 5.1
UPDATE statement
BEFORE statement trigger and, 9.8
PL/SQL extensions to, 13
with values in record, 5.15
restrictions on, 5.16
See also DML statement
UPDATING conditional predicate, 9.3.1
UROWID data type, 3.1.6
USER_DUMP_DEST initialization parameter, 11
user-defined exception, 11.5
giving error code to, 11.7.2
raising
with RAISE statement, 11.7.1.1
with RAISE_APPLICATION_ERROR procedure, 11.7.2
user-defined identifier, 2.2.2.3
collecting data about, 12.7
user-defined subtype, 3.4
user-defined type
See Abstract Data Type (ADT)
utlrp.sql script, 12.9.5

V

V$RESERVED_WORDS view, D
validation check for avoiding SQL injection, 7.4.2.2
VALUE_ERROR exception, 11.4
VARCHAR subtype, 3.1.4.3.1
VARCHAR2 data type, 3.1.4
VARCHAR2 static expression, 2.10.1.5.3
variable
binding of, 12.4
BOOLEAN, 2.6.4
collection
See collection
composite, 5
cursor
See cursor variable
host
cursor variable as, 6.4.8
packages and, 10.2
in cursor variable query, 6.4.5
in explicit cursor query, 6.2.2.4
locator, 11.2.2
record
See record
remote public package, 10.3.1
scalar
See scalar variable
with undefined value, 6.1.1
variable-size array
See varray
varray, 5.3
assigning null value to, 5.6.2
characteristics of, 5.1
comparing to NULL, 5.8.1
COUNT method for, 5.9.6.1
FIRST and LAST methods for, 5.9.5.2
returned by function, 12.5.1
See also collection
VERSION constant, 2.10.1.5.5
view
AUTHID property and, 8.13
INSTEAD OF trigger and, 14
IR subprogram invoked by, 8.13.5
materialized, trigger and, 14
virtual column
%ROWTYPE attribute and, 5.11.4.3
explicit cursors and, 6.2.2.5
visibility
of identifier, 2.5
of transaction, 6.7.3

W

warning, compile-time, 11.1
weak REF CURSOR type
creating, 6.4.1
FETCH statement and, 6.4.3
WHILE LOOP statement, 4.2.7
syntax diagram, 13
whitespace character
between lexical units, 2.2.5
in character literal, 2.2.3
in database character set, 2.1.1
wildcard character, 2.7.5.3
WRAP function, A.4
wrap utility
See PL/SQL Wrapper utility
wrapping PL/SQL source text, A
inquiry directives and, 2.10.1.4.3

Z

ZERO_DIVIDE exception, 11.4
zero-length string, 2.2.3