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

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

in comparison, 3.1.4.3.4

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

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

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

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

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

no-op (no operation) statement, 4.3.2

IS [NOT] NULL operator

collections and, 5.8.1

IS [NOT] NULL operator, 2.7.5.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.1

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

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

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

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

IR subprogram invoked by, 8.13.5

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