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
|| concatenation operator, 2.6.1
. item separator, 2.1.1
<< label delimiter, 2.1.1
.. range operator, 2.1.1, 4.3.8
@ 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 clause
of CREATE TRIGGER, 14
AFTER triggers, 14
auditing and, 9.12.1, 9.12.1
correlation names and, 9.5.1
specifying, 9.4.3
aggregate assignment, 2.2.6.1
aggregate functions
and PL/SQL, 6.1.3
user-defined, 14
aliases
using with a select list, 2.2.6.2
aliasing
bulk binds and, 12.6.2.6
for expression values in a cursor FOR loop, 6.3.6
subprogram parameters and, 8.11
ALL row operator, 6.1.3, 6.1.5.3
ALTER FUNCTION statement, 14
ALTER PACKAGE statement, 14
ALTER PROCEDURE statement, 14
ALTER statements, 14
triggers on, 14
ALTER TABLE statement
DISABLE ALL TRIGGERS clause, 9.10
ENABLE ALL TRIGGERS clause, 9.9
ALTER TRIGGER statement, 14
DISABLE clause, 9.10
ENABLE clause, 9.9
ALTER TYPE statement, 14
analytic functions
user-defined, 14
anonymous block
definition of, 1.2.1
ANSI/ISO SQL standard, 6.1
apostrophes, 2.1.3.3
architecture
PL/SQL, 1.3.1
ARRAY
VARYING, 5.3
arrays
associative, 5.1.1
characteristic of, 5.1
globalization and, 5.1.1
in other languages
simulating with varrays, 5.2
multidimensional, 5.9
variable-size (varrays)
characteristics of, 5.1
AS EXTERNAL clause
of CREATE FUNCTION, 14
of CREATE TYPE BODY, 14
AS OBJECT clause
of CREATE TYPE, 14
AS TABLE clause
of CREATE TYPE, 14
AS VARRAY clause
of CREATE TYPE, 14
assignment operator, 1.2.4.2
assignment statement
links to examples, 13
syntax, 13
assignments
aggregate, 2.2.6.1
collection, 5.7
field, 5.14
IN OUT parameters, 1.2.4.2
records, 5.14
variables, 1.2.4.2
associative arrays, 5.1.1
characteristic of, 5.1
compared to nested tables, 5.2.1
globalization and, 5.1.1
syntax, 13
asynchronous operations, 10.10.1
attributes
%ROWTYPE, 1.2.5.3, 2.2.6
%TYPE, 1.2.5.2
explicit cursors, 6.2.2.6, 6.2.2.6
of user-defined types
mapping to Java fields, 14
auditing
triggers and, 9.12.1
AUTHID clause
of ALTER TYPE, 14
AUTHID CURRENT_USER clause
of CREATE FUNCTION, 14, 14
of CREATE PACKAGE, 14
of CREATE PROCEDURE, 14
of CREATE TYPE, 14, 14
AUTHID DEFINER clause
of CREATE FUNCTION, 14, 14
of CREATE PACKAGE, 14
of CREATE PROCEDURE, 14
of CREATE TYPE, 14, 14
AUTHID property, 8.7
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

bags
simulating with nested tables, 5.2
basic loops, 4.3.1
BEFORE clause
of CREATE TRIGGER, 14
BEFORE triggers, 14
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.3.3.3
expressions, 13
BFILE data type, 3.2.1
BINARY_DOUBLE data type, 3.1.1.3
BINARY_FLOAT and BINARY_DOUBLE data types
for computation-intensive programs, 12.7
BINARY_FLOAT data type, 3.1.1.3
BINARY_INTEGER data type
See PLS_INTEGER data type
bind arguments
avoiding SQL injection with, 7.4.2.1
bind variables, 1.2.4.4
binding
bulk, 12.6
variables, 12.6
BLOB data type, 3.2.2
block
anonymous
definition of, 1.2.1
blocks
links to examples, 13
PL/SQL
syntax, 13
BODY
with SQL CREATE PACKAGE statement, 10.1
body
cursor, 10.12
package, 10.6
BODY clause
of ALTER PACKAGE, 14
Boolean
assigning values, 2.5.1
expressions, 2.6.4
literals, 2.1.3.4
BOOLEAN data type, 3.1.3
bounded collections, 5.1
bulk
fetches, 12.6.2.1
returns, 12.6.2.3
bulk binding, 12.6
limitations, 12.6
BULK clause
with COLLECT, 12.6.2
BULK COLLECT clause, 12.6.2
checking whether no results are returned, 12.6.2
FETCH, 13
retrieving DML results, 12.6.2.3
retrieving query results with, 12.6.2
returning multiple rows, 6.3.2
SELECT INTO, 13
using LIMIT clause, 12.6.2, 12.6.2.2
using ROWNUM pseudocolumn, 12.6.2
using SAMPLE clause, 12.6.2
using with FORALL statement, 12.6.2.4
BULK COLLECT INTO clause
in EXECUTE IMMEDIATE statement, 13
in RETURNING INTO clause, 13
bulk SQL
using to reduce loop overhead, 12.6
BULK_EXCEPTIONS cursor attribute
ERROR_CODE field, 12.6.1.3
ERROR_INDEX field, 12.6.1.3
example, 12.6.1.3
handling FORALL exceptions, 12.6.1.3
using ERROR_CODE field with SQLERRM, 12.6.1.3
BULK_ROWCOUNT cursor attribute
affected by FORALL, 12.6.1.2
by-reference parameter passing, 8.11
by-value parameter passing, 8.11

C

C clause
of CREATE TYPE, 14
of CREATE TYPE BODY, 14
C method
mapping to an object type, 14
call spec. See call specifications
call specification, 10.1
call specifications
in procedures, 14
of CREATE PROCEDURE, 14
of CREATE TYPE, 14
of CREATE TYPE BODY, 14
call stack
AUTHID property and, 8.7
DR and IR units and, 8.7
calls
inter-language, 8.9
resolving subprogram, 8.6
subprograms, 8.4.4
carriage returns, 2.1
CASE expressions, 2.6.5, 2.6.5
overview, 1.2.6.1
case sensitivity
in identifiers, 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
CHAR data type, 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
BULK COLLECT, 12.6.2
LIMIT, 12.6.2.2
CLOB data type, 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.4.2
COLLECT clause
with BULK, 12.6.2
collection exceptions
when raised, 5.11
collection methods, 5.10
syntax, 13
COLLECTION_IS_NULL exception, 11.4
collections, 5
allowed subscript ranges, 5.6
applying methods to parameters, 5.10.9
assigning, 5.7
avoiding exceptions, 5.11
bounded, 5.1
bulk binding, 5.14.5, 12.6
choosing the type to use, 5.2
comparing, 5.8
constructors, 5.5
declaring variables, 5.4
defining types, 5.3
DELETE method, 5.10.8
dense, 5.1
element types, 5.3
EXISTS method, 5.10.1
EXTEND method, 5.10.6
initializing, 5.5
links to examples, 13, 13
multidimensional, 5.9
NEXT method, 5.10.5
operators to transform nested tables, 5.7
overview, 1.2.5.4
PRIOR method, 5.10.5
referencing, 5.5
referencing elements, 5.6
scope, 5.3
sparse, 5.1
syntax, 13
testing for null, 5.8
three types of, 5.1
TRIM method, 5.10.7
unbounded, 5.1
column aliases
expression values in a cursor loop, 6.3.6
when needed, 2.2.6.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
syntax, 13
COMMIT statement, 6.7.1
comparison functions
MAP, 14, 14
ORDER, 14, 14
comparison operators, 6.1.5.1
comparisons
of character values, 3.1.2.1.4
of expressions, 2.6.4
of null collections, 5.8
operators, 2.6.3.3
PL/SQL, 2.6
with NULLs, 2.6.6
COMPILE clause
of ALTER PACKAGE, 14
of ALTER PROCEDURE, 14
of ALTER TRIGGER, 14
of ALTER TYPE, 14
compiler parameters
and REUSE SETTINGS clause, 1.3.2, 1.3.2
PL/SQL, 1.3.2
compiler switches
dropping and preserving, 14, 14, 14, 14, 14
compiling
conditional, 2.9
composite types, 5
composite variables, 5
Compound triggers, 9.4.8
compound triggers
creating, 14
concatenation operator, 2.6.1
treatment of nulls, 2.6.6.3
conditional compilation, 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
PLSQL_LINE flag, 2.9.1.5
PLSQL_UNIT flag, 2.9.1.5
restrictions, 2.9.3
static constants, 2.9.1.6.4
using static expressions with, 2.9.1.6
using with DBMS_DB_VERSION, 2.9.1.7
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
for declaring constants, 1.2.4.3, 2.2.2
constants
declaring, 1.2.4.3, 2.2, 2.2.2
links to examples, 13, 13
static, 2.9.1.6.4
syntax, 13, 13
understanding PL/SQL, 1.2.4
constraining tables, 9.5.4.4
constraints
NOT NULL, 2.2.4
triggers and, 9.2, 9.12.2
constructor methods
and object types, 14
constructors
collection, 5.5
defining for an object type, 14
user-defined, 14
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
data type, 3.4
correlated subqueries, 6.4.1
correlation names, 9.4.7
NEW, 9.5.1
OLD, 9.5.1
when preceded by a colon, 9.5.1
COUNT collection method, 5.10.2
COUNT method
collections, 13
CREATE
with PROCEDURE statement, 1.2.7.1
CREATE FUNCTION statement, 14
CREATE PACKAGE statement, 14
CREATE PROCEDURE statement, 1.2.7.1, 14
CREATE statement
packages, 10.1
CREATE statements, 14
triggers on, 14
CREATE TRIGGER statement, 9.4, 14
REFERENCING option, 9.5.1.3
CREATE TYPE BODY statement, 14
CREATE TYPE statement, 14
creating
packages, 10.1
procedures, 1.2.7.1
CURRENT OF clause
with UPDATE, 6.7.7.1
CURRENT_USER
value of AUTHID property, 8.7
CURRVAL
pseudocolumn, 6.1.4.1
cursor attributes
%BULK_EXCEPTIONS, 12.6.1.3
%BULK_ROWCOUNT, 12.6.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
syntax, 13
links to examples, 13
native dynamic SQL and, 7.2
SQL, 6.2.1.1
values of, 6.2.2.6.4
cursor declarations
links to examples, 13
syntax, 13
cursor expressions
REF CURSORs, 6.6
restrictions, 6.6
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.11.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 SQL, 6.2.1.1
closing explicit, 6.2.2.5
declaring explicit, 6.2.2.1
definition, 1.2.5.1
explicit, 1.2.5.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.5.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.11.8
variables, 6.5
CustomDatum Java storage format, 14

D

data abstraction
understanding PL/SQL, 1.2.5
data definition language (DDL)
events and triggers, 14
data manipulation language
triggers and, 9.1.3
data manipulation language (DML)
operations
and triggers, 14
data type conversion
SQL injection and, 7.4.1.3
data types
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 data types
RAW, 3.1.2.2
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.3
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.2.7.2
autonomous, 6.8.4
database triggers. See triggers
databases
events
and triggers, 14
auditing, 14
transparent logging of, 14
DATE data type, 3.1.4.1
datetime
arithmetic, 3.1.4.7
data types, 3.1.4
literals, 2.1.3.5
DAY
data type 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.7
DBMS_OUTPUT package
displaying output, 1.2.3
displaying output from PL/SQL, 10.10.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.5.1
DBMS_SQL package, 7.3
upgrade to dynamic SQL, 12.8
DBMS_SQL.TO_NUMBER function, 7.3
DBMS_SQL.TO_REFCURSOR function, 7.3
DBMS_TRACE package
tracing code for tuning, 12.5.2
DBMS_WARNING package
controlling warning messages in PL/SQL, 11.10.3
dbmsupbin.sql script
interpreted compilation, 12.10.5
dbmsupgnv.sql script
for PL/SQL native compilation, 12.10.5
deadlocks
how handled by PL/SQL, 6.7.4
DEBUG clause
of ALTER FUNCTION, 14
of ALTER PACKAGE, 14
of ALTER PROCEDURE, 14
of ALTER TRIGGER, 14
of ALTER TYPE, 14
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.4.3, 2.2.2
cursor variables, 6.5.3
exceptions in PL/SQL, 11.5.1
explicit cursor, 6.2.2.1
PL/SQL functions, 1.2.7
PL/SQL procedures, 1.2.7
PL/SQL subprograms, 1.2.7
restrictions, 2.2.7
using %ROWTYPE, 2.2.6
using DEFAULT, 2.2.3
using NOT NULL constraint, 2.2.4
variables, 1.2.4.1, 2.2
DECLARE
start of declarative part of a PL/SQL block, 13
syntax, 13
DECODE function
treatment of nulls, 2.6.6.4
DEFAULT keyword
for assignments, 2.2.3
DEFAULT option
RESTRICT_REFERENCES, 13
default parameter values, 8.4.3
DEFINE
limitations of use with wrap utility, A.4.3
DEFINER value of AUTHID property, 8.7
definer's rights functions, 14
definer's rights units
See DR units
DELETE method
collections, 5.10.8, 13
DELETE statement
column values and triggers, 9.5.1
triggers for referential integrity, 9.12.3.2, 9.12.3.3
triggers on, 14
delimiters, 2.1.1
dense collections, 5.1
dense nested tables, 5.1.2
dependencies
in stored triggers, 9.6.1
schema objects
trigger management, 9.5.4.2
DETERMINISTIC clause
of CREATE FUNCTION, 14
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.2
disabling
triggers, 9.1.2
displaying output
DBMS_OUTPUT package, 1.2.3
setting SERVEROUTPUT, 10.10.2
DISTINCT row operator, 6.1.3, 6.1.5.3
distributed databases
triggers and, 9.5.4.2
dot notation, 1.2.5.2, B.2
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
DR units
call stack and, 8.7
dynamic SQL statements and, 8.7
name resolution and, 8.7
privilege checking and, 8.7
static SQL statements and, 8.7
DROP PACKAGE BODY statement, 14
DROP statements, 14
triggers on, 14
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.8
dynamic SQL statements
AUTHID property and, 8.7

E

element types
collection, 5.3
ELSE clause
using, 4.2.2
ELSIF clause
using, 4.2.3
ENABLE clause
of ALTER TRIGGER, 14
enabled trigger
definition, 9.1.2
enabling
triggers, 9.1.2
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.2
error messages
maximum length, 11.9.4
ERROR_CODE
BULK_EXCEPTIONS cursor attribute field, 12.6.1.3
using with SQLERRM, 12.6.1.3
ERROR_INDEX
BULK_EXCEPTIONS cursor attribute field, 12.6.1.3
evaluation
short-circuit, 2.6.3.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, 13
exception handlers
OTHERS handler, 11.1
overview, 1.2.2
using RAISE statement in, 11.8, 11.9
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
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, 13
during trigger execution, 9.5.1.5
handling in PL/SQL, 11
links to examples, 13, 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 the DBMS_WARNING package, 11.10.3
using WHEN and OR, 11.9
WHEN clause, 11.9
EXECUTE IMMEDIATE statement, 7.2.1
links to examples, 13
syntax, 13
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 data type conversion, 3.4.1
explicit declarations
cursor FOR loop record, 6.3.5.2
explicit format modelsavoiding SQL injection with, 7.4.2.3
expressions
as default parameter values, 8.4.3
in cursors, 6.4.2
Boolean, 2.6.4
CASE, 2.6.5, 2.6.5
examples, 13
PL/SQL, 2.6
static, 2.9.1.6
syntax, 13
EXTEND method
collections, 5.10.6, 13
external
routines, 8.9
subprograms, 8.9
external functions, 14, 14, 14, 14
external procedures, 14

F

FALSE value, 2.1.3.4
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.6.2.1, 12.6.2.1
file I/O, 10.10.6
FINAL clause
of CREATE TYPE, 14, 14
FIRST collection method, 5.10.4, 13
FIRST method
collections, 13
FLOAT
NUMBER subtype, 3.1.1.4
FOR EACH ROW clause, 9.4.6
of CREATE TRIGGER, 14
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.6.1
using to improve performance, 12.6.1
using with BULK COLLECT clause, 12.6.2.4
with rollbacks, 12.6.1.1
FORCE clause
of DROP TYPE, 14
FOR-LOOP statement
syntax, 13
using, 4.3.8
formal parameters, 6.4.2
format models
explicitavoiding SQL injection with, 7.4.2.3
forward
references, 2.2.7
forward declaration of subprograms, 8.3
FOUND cursor attribute
explicit, 6.2.2.6.1
implicit, 6.2.1.1.1
function declaration
syntax, 13
function result cache, 8.12
functions
analytic
user-defined, 14
avoiding run-time compilation, 14
changing the declaration of, 14
changing the definition of, 14
data type of return value, 13, 14
declaration, 13
examples, 14
executing
from parallel query processes, 14
external, 14, 14, 14, 14
in PL/SQL, 8
invoking, 8.1
links to examples, 13
partitioning
among parallel query processes, 14
pipelined, 12.11
privileges executed with, 14, 14
recompiling invalid, 14
re-creating, 14
removing from the database, 14
RETURN statement, 8.2
returning collections, 14
returning results iteratively, 14
schema executed in, 14, 14
specifying schema and user privileges for, 14
SQL in PL/SQL, 2.8
stored, 14
table, 12.11.1, 14
user-defined
aggregate, 14
using a saved copy, 14

G

global identifiers, 2.4
globalization
associative arrays and, 5.1.1
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
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.6
hash tables
simulating with associative arrays, 5.2
hiding PL/SQL source code
PL/SQL source code
host arrays
bulk binds, 12.6.2.5
HOUR
data type 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
global, 2.4
local, 2.4
quoted, 2.1.2.3
scope and visibility of, 2.4
syntax and semantics of, 2.1.2
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-ELSIF statement
using, 4.2.3
implicit cursors
guidelines, 6.2.1.2
See SQL cursors
implicit data type conversion, 3.4.2
implicit data type conversions
performance, 12.3.1.6
implicit declarations
FOR loop counter, 4.3.8.3
IN comparison operator, 2.6.3.3.4
IN OUT parameter mode
subprograms, 8.4.2.3
IN parameter mode
subprograms, 8.4.2.1
incomplete object types, 14
creating, 14
INDEX BY
collection definition, 13
index-by tables
See associative arrays
INDICES OF clause
FORALL, 13
with FORALL, 12.6.1
infinite loops, 4.3.1
initialization
collections, 5.5
package, 10.6
using DEFAULT, 2.2.3
variable, 2.5
initialization parameters
PL/SQL compilation, 1.3.2
injection, SQL, 7.4.1.2
inline LOB locators, 3.2
INLINE pragma
syntax, 13
Inlining subprograms, 12.1
input, 1.2.3
input-output packages, 1.2.3
INSERT statement
column values and triggers, 9.5.1
triggers on, 14
with a record variable, 5.14.2
instance_num event attribute, 9.13.5
INSTANTIABLE clause
of CREATE TYPE, 14
INSTEAD OF clause
of CREATE TRIGGER, 14
INSTEAD OF triggers, 9.4.5, 14
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.9
interpreted compilation
dbmsupbin.sql script, 12.10.5
recompiling all PL/SQL modules, 12.10.5
INTERSECT set operator, 6.1.5.2
interval
arithmetic, 3.1.4.7
INTERVAL DAY TO SECOND data type, 3.1.4.6
INTERVAL YEAR TO MONTH data type, 3.1.4.5
intervals
data types, 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
altering for an object type, 14
defining for a function, 14
defining for a package, 14
defining for a procedure, 14
defining for an object type, 14
invoker's rights functions
defining, 14
invoker's rights subprograms
name resolution in, 8.7.4
invoker's rights units
See IR units
invoking Java stored procedures, 8.9
IR units
call stack and, 8.7
dynamic SQL statements and, 8.7
name resolution and, 8.7
privilege checking and, 8.7
static SQL statements and, 8.7
IS NULL comparison operator, 2.6.3.3.1
expressions, 13
is_alter_column event attribute, 9.13.5
ISOPEN cursor attribute
explicit, 6.2.2.6.2
implicit, 6.2.1.1.2

J

JAVA
use for invoking external subprograms, 8.9
Java
call specs, 8.9
methods
return type of, 14
storage formats
CustomDatum, 14
SQLData, 14
JAVA clause
of CREATE TYPE, 14
of CREATE TYPE BODY, 14
Java methods
mapping to an object type, 14
Java stored procedures
invoking from PL/SQL, 8.9

K

keywords, 2.1.2.1
use in PL/SQL, 2.1.2.1
keywords in PL/SQL, D

L

labels
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.9
LANGUAGE clause
of CREATE PROCEDURE, 14
of CREATE TYPE, 14
of CREATE TYPE BODY, 14
language elements
of PL/SQL, 13
large object (LOB) data types, 3.2
LAST collection method, 5.10.4, 13
LAST method
collections, 13
LEVEL
pseudocolumn, 6.1.4.2
lexical units
PL/SQL, 2.1
LIKE comparison operator, 2.6.3.3.2
expressions, 13
LIMIT clause
FETCH, 13
using to limit rows for a Bulk FETCH operation, 12.6.2.2
LIMIT collection method, 5.10.3
LIMIT method
collections, 13
limitations
bulk binding, 12.6
of PL/SQL programs, C
PL/SQL compiler, C
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 data type, 2.1.3.1
numeric, 2.1.3.1
numeric data types, 2.1.3.1
string, 2.1.3.3
syntax, 13
types of PL/SQL, 2.1.3
LNPLS99980|Using PL/SQL to Create Server Pages, 2.11
LOB (large object) data types, 3.2
use in triggers, 9.5.1.1
LOB locators, 3.2
local identifiers, 2.4
locator variables
used with exceptions, 11.9.6.3
LOCK TABLE statement
locking a table, 6.7.7.2
locks
modes, 6.7
overriding, 6.7.7
transaction processing, 6.7
using FOR UPDATE clause, 6.7.7.1
logical operators, 2.6.3
logical rowids, 3.1.2.5
LOGIN_DENIED exception, 11.4
LOGOFF database event
triggers on, 14
LOGON database event
triggers on, 14
LONG data type, 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
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

MAP MEMBER clause
of ALTER TYPE, 14
of CREATE TYPE, 14, 14
MAP methods
defining for a type, 14
specifying, 14
maximum precision, 3.1.1.4
maximum size
CHAR value, 3.1.2.1
LONG value, 3.1.2.4
Oracle error message, 11.9.4
RAW value, 3.1.2.2
MEMBER clause
of ALTER TYPE, 14
of CREATE TYPE, 14
membership test, 2.6.3.3.4
memory
avoid excessive overhead, 12.3.2
Method 4, 7.3
methods
collection, 5.10
overriding a method a supertype, 14
preventing overriding in subtypes, 14
static, 14
without implementation, 14
MINUS set operator, 6.1.5.2
MINUTE
data type field, 3.1.4
modularity
packages, 10.3
MONTH
data type field, 3.1.4
multidimensional collections, 5.9
multiline 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.9
NAME parameter
transactions, 6.7.6
name resolution, 2.3.3
AUTHID property and, 8.7
differences between PL/SQL and SQL, B.3
DR units and, 8.7
global and local variables, B.1
inner capture in DML statements, B.5
IR units and, 8.7
overriding in IR subprograms, 8.7.4
qualified names and dot notation, B.2
qualifying references to attributes and methods, B.5
understanding, B.1
understanding capture, B.4
names
explicit cursor, 6.2.2.1
qualified, 2.3
savepoint, 6.7.3
variable, 2.3.1
naming conventions
PL/SQL, 2.3
national character data types, 3.1.2.3
national character set, 2.1
native compilation
dbmsupgnv.sql script, 12.10.5
dependencies, 12.10.3
how it works, 12.10.2
invalidation, 12.10.3
modifying databases for, 12.10.5
revalidation, 12.10.3
setting up databases, 12.10.4
utlrp.sql script, 12.10.5
native dynamic SQL, 7.2
NATURAL
BINARY_INTEGER subtype, 3.1.1.1
NATURALN
BINARY_INTEGER subtype, 3.1.1.1
NCHAR data type, 3.1.2.3.2, 3.1.2.3.3
NCLOB data type, 3.2.4
nested cursors
using, 6.6
NESTED TABLE clause
of CREATE TRIGGER, 14
nested tables, 5.1.2
characteristics of, 5.1
compared to associative arrays, 5.2.1
compared to varrays, 5.2.2
creating, 14
dropping the body of, 14
dropping the specification of, 14
modifying, 14
of scalar types, 14
syntax, 13
transforming with operators, 5.7
update in a view, 14
nesting
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 parameters
SQL injection and, 7.4.1.3
NLS_COMP initialization parameter
associative arrays and, 5.1.1
NLS_LENGTH_SEMANTICS initialization parameter
setting with ALTER SYSTEM, 14
NLS_SORT initialization parameter
associative arrays and, 5.1.1
NO_DATA_FOUND exception, 11.4
NOCOPY compiler hint
for tuning, 12.9
restrictions on, 12.9
NOT FINAL clause
of CREATE TYPE, 14
NOT INSTANTIABLE clause
of CREATE TYPE, 14, 14
NOT logical operator
treatment of nulls, 2.6.6.1
NOT NULL
declaration, 13, 13
NOT NULL constraint
restriction on explicit cursors, 6.2.2.1
using in collection declaration, 5.4
using in variable declaration, 2.2.4
NOT NULL option
record definition, 13
NOT_LOGGED_ON exception, 11.4
notation
positional and named, 8.4.4
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.6
NULL statement
links to examples, 13
syntax, 13
using, 4.4.3
NULL value, 2.1.3.4
dynamic SQL and, 7.2.1
NUMBER data type, 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 data types, 2.1.3.1
NVL function
treatment of nulls, 2.6.6.4

O

obfuscating PL/SQL source code
See wrapping PL/SQL source code
object identifiers
specifying, 14
object types
adding methods to, 14
adding new member subprograms, 14
allowing object instances of, 14
allowing subtypes, 14
and subtypes, 14
and supertypes, 14
bodies
creating, 14
re-creating, 14
SQL examples, 14
compiling the specification and body, 14
creating, 14, 14
defining member methods of, 14
disassociating statistics types from, 14
dropping methods from, 14
dropping the body of, 14
dropping the specification of, 14
function subprogram
declaring, 14
function subprograms, 14, 14
handling dependent types, 14
incomplete, 14
inheritance, 14
invalidating dependent types, 14
MAP methods, 14
ORDER methods, 14
overview, 1.2.5.6
privileges, 14
procedure subprogram
declaring, 14
procedure subprograms, 14, 14
root, 14
SQL examples, 14
static methods of, 14
subtypes, 14
top-level, 14
user-defined
creating, 14
using with invoker's-rights subprograms, 8.7.7
values
comparing, 14, 14, 14
OBJECT_VALUE pseudocolumn, 9.5.2
objects. See object types or database objects
OLD correlation name, 9.5.1
ON DATABASE clause
of CREATE TRIGGER, 14
ON NESTED TABLE clause
of CREATE TRIGGER, 14
ON SCHEMA clause
of CREATE TRIGGER, 14
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.3.3
logical, 2.6.3
precedence, 2.6.2
relational, 2.6.3.3
optimizing
PL/SQL programs, 12.1
OR keyword
using with EXCEPTION, 11.9
OR REPLACE clause
of CREATE FUNCTION, 14
of CREATE PACKAGE, 14
of CREATE PACKAGE BODY, 14
of CREATE PROCEDURE, 14
of CREATE TRIGGER, 14
of CREATE TYPE, 14
of CREATE TYPE BODY, 14
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 MEMBER clause
of ALTER TYPE, 14
of CREATE TYPE BODY, 14
ORDER methods
defining for a type, 14
specifying, 14
order of evaluation, 2.6.2
OTHERS clause
exception handling, 13
OTHERS exception handler, 11.1, 11.9
OUT parameter mode
subprograms, 8.4.2.2
outlines
assigning to a different category, 14
rebuilding, 14
renaming, 14
out-of-line LOB locators, 3.2
output, 1.2.3
overloading
guidelines, 8.5.1
packaged subprograms, 10.7
restrictions, 8.5.2
subprogram names, 8.5
OVERRIDING clause
of ALTER TYPE, 14
of CREATE TYPE, 14

P

PACKAGE
with SQL CREATE statement, 10.1
package bodies
creating, 14
re-creating, 14
removing from the database, 14
PACKAGE BODY
with SQL CREATE statement, 10.1
packaged cursors, 10.12
packaged procedures
dropping, 14
packages
advantages, 10.3
avoiding run-time compilation, 14
bodiless, 10.4
body, 10.1, 10.6
call specification, 10.1
contents of, 10.2
creating, 10.1, 14
cursor specifications, 10.12
cursors, 10.12
disassociating statistics types from, 14
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
invoker's rights, 14
invoking subprograms, 10.5
modularity, 10.3
overloading subprograms, 10.7
overview, 1.2.8
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
recompiling explicitly, 14
redefining, 14
referencing, 10.5
removing from the database, 14
restrictions on referencing, 10.5
scope, 10.4
specification, 10.1
specifications, 10.4
specifying schema and privileges of, 14
STANDARD package, 10.9
understanding, 10.1
visibility of contents, 10.1
PARALLEL_ENABLE clause
of CREATE FUNCTION, 14
parameter passing
by reference, 8.11
by value, 8.11
parameters
actual, 6.4.2
actual and formal, 8.4.1
aliasing, 8.11
cursor, 6.4.2
default values, 8.4.3
formal, 6.4.2
IN mode, 8.4.2.1
IN OUT mode, 8.4.2.3
modes, 8.4.2
OUT mode, 8.4.2.2
summary of modes, 8.4.2.4
parentheses, 2.6.2
parse tree, 9.6
pattern matching, 2.6.3.3.2
performance
avoid memory overhead, 12.3.2
avoiding problems, 12.3
physical rowids, 3.1.2.5
pipe, 10.10.3
PIPE ROW statement
for returning rows incrementally, 12.11.4
PIPELINED
function option, 12.11.2, 13
PIPELINED clause
of CREATE FUNCTION, 14
pipelined functions
exception handling, 12.11.11
fetching from results of, 12.11.7
for querying a table, 12.11.1
overview, 12.11.1
passing data with cursor variables, 12.11.8
performing DML operations inside, 12.11.9
performing DML operations on, 12.11.10
returning results from, 12.11.4
transformation of data, 12.11
transformations, 12.11.3
writing, 12.11.2
pipelines
between table functions, 12.11.5
returning results from table functions, 12.11.4
support collection types, 12.11.2
using table functions, 12.11.3
writing table functions, 12.11.2
pipelining
definition, 12.11.1
PLS_INTEGER data type, 3.1.1.1
overflow condition, 3.1.1.1
PL/SQL
advantages, 1.1
architecture, 1.3.1
assigning Boolean values, 2.5.1
assigning query result to variable, 2.5.2
assigning values to variables, 2.5
blocks
syntax, 13
CASE expressions, 2.6.5
character sets, 2.1
collections
overview, 1.2.5.4
comments, 2.1.4
comparisons, 2.6
compiler limitations, C
compiler parameters, 1.3.2
compile-time warnings, 11.10
conditional compilation, 2.9
constants, 1.2.4
control structures, 1.2.6, 4.1
data abstraction, 1.2.5
declarations
constants, 2.2
displaying output, 10.10.2
engine, 1.3.1
environment, 10.9
error handling
overview, 1.2.2
errors, 11
exceptions, 11
expressions, 2.6
functions, 8
lexical units, 2.1
limitations of programs, C
limits on programs, C
literals, 2.1.3
logical operators, 2.6.3
name resolution, B.1
naming conventions, 2.3
new features, Preface
performance problems, 12.3
portability, 1.1.4
procedural aspects, 1.2
procedures, 8
profiling and tracing programs, 12.5
querying data, 6.3
records
overview, 1.2.5.5
Server Pages (PSPs), 2.11
statements, 13
subprograms, 8
syntax of language elements, 13
transaction processing, 6.7
trigger bodies, 9.5, 9.5.1
tuning code, 12.2
tuning computation-intensive programs, 12.7
tuning dynamic SQL programs, 12.8
using NOCOPY for tuning, 12.9
using transformation pipelines, 12.11
variables, 1.2.4
warning messages, 11.10.1
Web applications, 2.10
PL/SQL compiler
parameters, 14, 14, 14, 14, 14
PLSQL data types, 3
numeric literals, 2.1.3.1
predefined, 3
PL/SQL function result cache, 8.12
PL/SQL units
stored
SQL statements for, 14
what they are, 1.3.2
PLSQL_LINE flag
use with conditional compilation, 2.9.1.5
PLSQL_OPTIMIZE_LEVEL compilation parameter, 12.1
optimizing PL/SQL programs, 12.1
PLSQL_UNIT flag
use with conditional compilation, 2.9.1.5
PLSQL_WARNINGS initialization parameter, 11.10
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 SERIALLY_REUSABLE, 13
PRAGMA clause
of ALTER TYPE, 14
of CREATE TYPE, 14, 14
PRAGMA RESTRICT_REFERENCES, 14
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.10, 13
SERIALLY_REUSABLE, 13
precedence of operators, 2.6.2
precision of digits
specifying, 3.1.1.4
predefined exceptions
raising explicitly, 11.6
redeclaring, 11.5.5
predefined PL/SQL data types, 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
privilege checking
AUTHID property and, 8.7
DR units and, 8.7
IR units and, 8.7
privileges
creating triggers, 9.3
dropping triggers, 9.7
recompiling triggers, 9.6.2
See also privilege checking
PROCEDURE
with CREATE statement, 1.2.7.1
procedure declaration
syntax, 13
procedures
avoid run-time compilation, 14
compile explicitly, 14
creating, 1.2.7.1, 14
declaration, 13
declaring
as a Java method, 14
as C functions, 14
external, 14
in PL/SQL, 8
invalidating local objects dependent on, 14
invoked by triggers, 9.5.4.2
invoking, 8.1
links to examples, 13
privileges executed with, 14, 14
recompiling, 14
re-creating, 14
removing from the database, 14
schema executed in, 14, 14
specifying schema and privileges for, 14
productivity, 1.1.3
Profiler API
gathering statistics for tuning, 12.5.1
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.10

Q

qualifiers
using subprogram names as, 2.3.3
queries
multiple-row
dynamic, 7.2.2
triggers use of, 9.1.3
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 data type, 3.1.2.2
maximum length, 3.1.2.2
read consistency
triggers and, 9.1.3
READ ONLY parameter
transactions, 6.7.6
readability
with NULL statement, 4.4.3
read-only transaction, 6.7.6
REAL
NUMBER subtype, 3.1.1.4
record definition
syntax, 13
records, 5
%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.3, 13
implicit declaration, 6.3.5.2
inserting, 5.14.2
links to examples, 13
manipulating, 5.13
nesting, 5.12
overview, 1.2.5.5
passing as parameters, 5.13
restriction on assignments, 5.14
restrictions on inserts and updates of, 5.14.4
returning into, 5.14.3
updating, 5.14.3
using as function return values, 5.13
recursion
using with PL/SQL subprograms, 8.8
REF CURSOR data type, 6.5.1
cursor variables, 6.5
defining, 6.5.3
using with cursor subqueries, 6.6
REF CURSOR variables
as parameters to table functions, 12.11.8
predefined SYS_REFCURSOR type, 12.11.8
referencing
collections, 5.5
REFERENCING clause
of CREATE TRIGGER, 14, 14, 14
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.3.3
RELIES ON clause, 13
remote access indicator, 2.3
remote exception handling, 9.5.3
RENAME clause
of ALTER TRIGGER, 14
REPEAT UNTIL structure
PL/SQL equivalent, 4.3.7
REPLACE AS OBJECT clause
of ALTER TYPE, 14
REPLACE function
treatment of nulls, 2.6.6.4
reraising an exception, 11.8
reserved words, 2.1.2.1
reserved words in PL/SQL, D
resolution
name, 2.3.3
references to names, B.1
RESTRICT_REFERENCES pragma, 8.10
links to examples, 13
of ALTER TYPE, 14
syntax, 13
using with autonomous functions, 6.8.5
restrictions
cursor expressions, 6.6
cursor variables, 6.5.8
overloading subprograms, 8.5.2
system triggers, 9.5.4.6
result cache, 8.12
result sets, 6.2.2.2
RESULT_CACHE clause, 13
RETURN clause, 13
cursor, 10.12
cursor declaration, 13
of CREATE FUNCTION, 14
of CREATE TYPE, 14
of CREATE TYPE BODY, 14
RETURN statement
functions, 8.2
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.6.2.3
REUSE SETTINGS clause
of ALTER FUNCTION, 14
of ALTER PACKAGE, 14
of ALTER PROCEDURE, 14
of ALTER TRIGGER, 14
of ALTER TYPE, 14
with compiler parameters, 1.3.2, 1.3.2
REVERSE
with LOOP counter, 4.3.8
REVERSE option
LOOP, 13
RNDS attribute
of PRAGMA RESTRICT_REFERENCES, 14
RNDS option
RESTRICT_REFERENCES, 13
RNPS attribute
of PRAGMA RESTRICT_REFERENCES, 14
RNPS option
RESTRICT_REFERENCES, 13
ROLLBACK statement, 6.7.2
effect on savepoints, 6.7.3
rollbacks
implicit, 6.7.4
of FORALL statement, 12.6.1.1
routines
external, 8.9
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 data type, 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.3
links to examples, 13
records, 5.12
syntax, 13
using, 2.2.6
with SUBTYPE, 3.3.1
ROWTYPE_MISMATCH exception, 11.4
RPC (remote procedure call)
and exceptions, 11.7
rules
purity, 8.10
run-time compilation
avoiding, 14
run-time errors
PL/SQL, 11

S

SAVE EXCEPTIONS clause
FORALL, 13
SAVEPOINT statement, 6.7.3
savepoints
reusing names, 6.7.3
scalar data types, 3.1
scale
specifying, 3.1.1.4
scientific notation, 2.1.3.1
scope
collection, 5.3
exceptions in PL/SQL, 11.5.2
explicit cursor, 6.2.2.1
explicit cursor parameter, 6.2.2.1
loop counter, 4.3.8.3
package, 10.4
scope of identifier, 2.4
searched CASE expression, 2.6.5.2
searched CASE statement, 4.2.5
SECOND
data type field, 3.1.4
security
enforcing, 14
security risks, 7.4
SELECT INTO statement
links to examples, 13
returning one row, 6.3.1
syntax, 13
selector, 2.6.5.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.11
SERVERERROR event
triggers on, 14, 14
SERVEROUTPUT
setting ON to display output, 10.10.2
set operators, 6.1.5.2
SET TRANSACTION statement, 6.7.6
sets
simulating with nested tables, 5.2
short-circuit evaluation, 2.6.3.2
SHUTDOWN event
triggers on, 14
side effects, 8.4.2
controlling, 8.10
SIGNTYPE
BINARY_INTEGER subtype, 3.1.1.1
simple CASE expression, 2.6.5.1
SIMPLE_DOUBLE data type, 3.1.1.3
SIMPLE_FLOAT data type, 3.1.1.3
SIMPLE_INTEGER data type, 3.1.1.2
single-line comments, 2.1.4.1
size limit
varrays, 5.3
SMALLINT
NUMBER subtype, 3.1.1.4
sparse collections, 5.1
sparse nested tables, 5.1.2
specification
call, 10.1
cursor, 10.12
package, 10.4
SPECIFICATION clause
of ALTER PACKAGE, 14
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 cursors
attributes, 6.2.1.1
SQL functions in PL/SQL, 2.8
SQL injection, 7.4
SQL reserved words, D
SQL statements
ALTER, 14
CREATE, 14
DROP, 14
for stored PL/SQL units, 14
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
SQLData Java storage format, 14
SQLERRM function
links to examples, 13
syntax, 13
using with BULK_EXCEPTIONS ERROR_CODE field, 12.6.1.3
SQLJ object types
creating, 14
mapping a Java class to, 14
standalone procedures
dropping, 14
STANDARD package
defining PL/SQL environment, 10.9
START WITH clause, 6.1.4.2
STARTUP event
triggers on, 14
statement injection (SQL injection), 7.4.1.2
statement modification (SQL injection), 7.4.1.1
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
CONTINUE, 13
EXECUTE IMMEDIATE, 13
EXIT, 13
FETCH, 6.2.2.3, 6.5.5.3, 13
FORALL, 12.6.1, 13
FOR-LOOP, 13
GOTO, 13
IF, 13
LOOP, 4.3, 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
SELECT INTO, 13
WHILE-LOOP, 13
STATIC clause
of ALTER TYPE, 14
of CREATE TYPE, 14
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
static SQL statements
AUTHID property and, 8.7
statistics
user-defined
dropping, 14, 14
STEP clause
equivalent in PL/SQL, 4.3.8.1
STORAGE_ERROR exception, 11.4
raised with recursion, 8.8
store tables, 5.2.2
stored functions, 14
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.1
subprograms
actual and formal parameters, 8.4.1
advantages in PL/SQL, 8.1
controlling side effects, 8.10
declaring PL/SQL, 1.2.7
default parameter modes, 8.4.3
forward declaration of, 8.3
guidelines for overloading, 8.5.1
how calls are resolved, 8.6
IN OUT parameter mode, 8.4.2.3
IN parameter mode, 8.4.2.1
in PL/SQL, 8
invoking external, 8.9
invoking from SQL*Plus, 1.2.7.1
invoking with parameters, 8.4.4
mixed notation parameters, 8.4.4
named parameters, 8.4.4
OUT parameter mode, 8.4.2.2
overloading names, 8.5
parameter aliasing, 8.11
parameter modes, 8.4.2, 8.4.2.4
passing parameter by value, 8.11
passing parameters by reference, 8.11
positional parameters, 8.4.4
recursive, 8.8
restrictions on overloading, 8.5.2
using database links with invoker's-rights, 8.7.6
using recursion, 8.8
using triggers with invoker's-rights, 8.7.5
using views with invoker's-rights, 8.7.5
subqueries
correlated, 6.4.1
using in PL/SQL, 6.4
SUBSCRIPT_BEYOND_COUNT exception, 11.4
SUBSCRIPT_OUTSIDE_LIMIT exception, 11.4
subtypes, 14
CHARACTER, 3.1.2.1.1
compatibility, 3.3.2.1
constrained and unconstrained, 3.3
defining, 3.3.1
dropping safely, 14
STRING, 3.1.2.1.1
using, 3.3.2
VARCHAR, 3.1.2.1.1
supertypes, 14
syntax
BEGIN, 13
collection method, 13
exception definition, 13, 13
FETCH statement, 13
literal declaration, 13
LOOP statement, 13
NULL statement, 13
reading diagrams, 13, 14
WHILE-LOOP statement, 13
syntax of PL/SQL language elements, 13
SYS_INVALID_ROWID exception, 11.4
SYS_REFCURSOR type, 12.11.8
system events
triggers on, 14

T

table
mutating, 9.5.4.4
TABLE data type, 5.3
table functions
creating, 14
exception handling, 12.11.11
fetching from results of, 12.11.7
for querying, 12.11.1
organizing multiple calls to, 12.11.6
passing data with cursor variables, 12.11.8
performing DML operations inside, 12.11.9
performing DML operations on, 12.11.10
pipelining data between, 12.11.5
returning results from, 12.11.4
setting up transformation pipelines, 12.11
using transformation pipelines, 12.11.3
writing transformation pipelines, 12.11.2
tables
constraining, 9.5.4.4
hash
simulating with associative arrays, 5.2
index-by
See associative arrays
mutating, 9.5.4.4
nested, 5.1.2
characteristics of, 5.1
creating, 14
store, 5.2.2
tabs, 2.1
terminators, 2.1.1
THEN clause
using, 4.2.1
with IF statement, 4.2.1
TIMEOUT_ON_RESOURCE exception, 11.4
TIMESTAMP data type, 3.1.4.2
TIMESTAMP WITH LOCAL TIME ZONE data type, 3.1.4.4
TIMESTAMP WITH TIME ZONE data type, 3.1.4.3
TIMEZONE_ABBR
data type field, 3.1.4
TIMEZONE_HOUR
data type field, 3.1.4
TIMEZONE_MINUTES
data type field, 3.1.4
TIMEZONE_REGION
data type 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.5.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
triggers and, 9.1.3
visibility, 6.8.2.3
trigger
disabled
definition, 9.1.2
enabled
definition, 9.1.2
trigger body
defining, 14
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, 14
as a stored PL/SQL subprogram, 1.2.7.2
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, 14
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
compiling, 14
compound, 14
conditional predicates, 9.5, 9.5.1.4
constraints and, 9.2, 9.12.2
creating, 9.3, 9.4, 9.5.4, 14
creating enabled or disabled, 14
data access and, 9.1.3
data access restrictions, 9.12.3.7
database
altering, 14
dropping, 14
debugging, 9.8
designing, 9.2
disabling, 9.1.2, 14, 14
enabling, 9.1.2, 14, 14, 14
error conditions and exceptions, 9.5.1.5
events, 9.4.2
examples, 9.12
executing
with a PL/SQL block, 14
following other triggers, 14
FOR EACH ROW clause, 9.4.6
generating derived column values, 9.12.3.9
illegal SQL statements, 9.5.4.2
INSTEAD OF, 14
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
on database events, 14
on DDL events, 14
on DML operations, 14, 14
on views, 14
package variables and, 9.4.3
privileges
to drop, 9.7
procedures and, 9.5.4.2
recompiling, 9.6.2
re-creating, 14
REFERENCING option, 9.5.1.3
referential integrity and, 9.12.3
remote dependencies and, 9.5.4.2
remote exceptions, 9.5.3
removing from the database, 14
renaming, 14
restrictions, 9.4.7, 9.5.4
restrictions on, 14
row, 9.4.6, 14
row evaluation order, 9.4.4
row values
old and new, 14
sequential, 14
SQL examples, 14
statement, 14
stored, 9.6
use of LONG and LONG RAW data types, 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 attribute
of PRAGMA RESTRICT_REFERENCES, 14
TRUST option
RESTRICT_REFERENCES, 13
tuning
allocate large VARCHAR2 variables, 12.3.2.1
avoid memory overhead, 12.3.2
computation-intensive programs, 12.7
do not duplicate built-in functions, 12.3.1.4
dynamic SQL programs, 12.8
group related subprograms into a package, 12.3.2.2
guidelines for avoiding PL/SQL performance problems, 12.3
improve code to avoid compiler warnings, 12.3.2.4
make function calls efficient, 12.3.1.2
make loops efficient, 12.3.1.3
make SQL statements efficient, 12.3.1.1
optimizing PL/SQL programs, 12.1
pin packages in the shared memory pool, 12.3.2.3
PL/SQL code, 12.2
profiling and tracing, 12.5
reducing loop overhead, 12.6
reorder conditional tests to put least expensive first, 12.3.1.5
use BINARY_FLOAT or BINARY_DOUBLE for floating-point arithmetic, 12.3.1.8
use PLS_INTEGER for integer arithmetic, 12.3.1.7
using DBMS_PROFILE and DBMS_TRACE, 12.5
using FORALL, 12.6.1
using NOCOPY, 12.9
using transformation pipelines, 12.11
TYPE attribute, 2.2.5
declaring, 1.2.5.2
links to examples, 13
syntax, 13
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
type methods
return type of, 14
types
composite, 5
types. See object types or data types

U

unbounded collections, 5.1
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
triggers and, 9.4.2.2, 9.5.1.4
triggers for referential integrity, 9.12.3.2, 9.12.3.3
triggers on, 14
with a record variable, 5.14.3
URL (uniform resource locator), 10.10.7
UROWID
pseudocolumn, 6.1.4.3
UROWID data type, 3.1.2.5
user-defined
exceptions in PL/SQL, 11.5
user-defined aggregate functions, 14
user-defined statistics
dropping, 14, 14
user-defined types
defining, 14
mapping to Java classes, 14
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.10.5

V

V$RESERVED_WORDS view, D
VALIDATE clause
of DROP TYPE, 14
validation checks
avoiding SQL injection with, 7.4.2.2
VALUE_ERROR exception, 11.4
VALUES OF clause, 12.6.1
FORALL, 13
VARCHAR subtype, 3.1.2.1.1
VARCHAR2 data type
differences with CHAR, 3.1.2.1
variables
assigning query result to, 2.5.2
assigning values, 1.2.4.2, 2.5
bind
See bind variables
composite, 5
declaring, 1.2.4.1, 2.2
global, 10.7
initializing, 2.5
links to examples, 13, 13
passing as IN OUT parameter, 1.2.4.2
REF CURSOR data type, 6.5
syntax, 13, 13
understanding PL/SQL, 1.2.4
variable-size arrays (varrays)
characteristics of, 5.1
VARRAY data type, 5.3
varrays
compared to nested tables, 5.2.2
creating, 14, 14
dropping the body of, 14
dropping the specification of, 14
increasing size of, 14
See variable-size arrays
size limit, 5.3
syntax, 13
TYPE definition, 5.3
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
transaction, 6.8.2.3
visibility of identifier, 2.4

W

warning messages
controlling PL/SQL, 11.10.2
WHEN clause, 9.4.7
cannot contain PL/SQL expressions, 9.4.7
correlation names, 9.5.1
examples, 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
of CREATE TRIGGER, 14
using, 4.3.3, 4.3.5
WHILE-LOOP statement
overview, 1.2.6.2
syntax, 13
using, 4.3.7
wildcards, 2.6.3.3.2
WNDS attribute
of PRAGMA RESTRICT_REFERENCES, 14
WNDS option
RESTRICT_REFERENCES, 13
WNPS attribute
of PRAGMA RESTRICT_REFERENCES, 14
WNPS option
RESTRICT_REFERENCES, 13
work areas
queries, 6.5.1
wrap utility
running, A.4.2
wrapping PL/SQL source code, A

Y

YEAR
data type field, 3.1.4

Z

ZERO_DIVIDE exception, 11.4
ZONE
part of TIMESTAMP data type, 3.1.4.3
CREATE PACKAGE BODY statement, 14