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

in general, 5.11.4

column alias and, 6.2.2.5

explicit cursor and, 6.2.2.5

syntax diagram, 13

%TYPE attribute

in general, 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)

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

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

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

in general, 2.6.1

syntax diagram, 13

associative array

in general, 5.2

as standalone stored subprogram parameter, 5.2.3

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

in general, 6.7

pipelined table function in, 13

autonomous trigger, 6.7.6

AUTONOMOUS_TRANSACTION pragma

in general, 13

for pipelined table function, 12.5.2

---

B

bag data structure, 5.1

base type, 3

basic LOOP statement

in general, 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 argument

avoiding SQL injection with, 7.4.2.1

placeholder for

See placeholder for bind argument

blank-padding

in assignment, 3.1.4.3.3

in comparison, 3.1.4.3.4

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

in general, 12.4.2

aliasing and, 12.4.2.1.1

of FETCH statement, 12.4.2.2

of RETURNING INTO clause

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

in general, 12.4

in compound DML trigger, 9.3.5.3

---

C

C procedure, invoking, 8.14

cache, function result, 8.11

calculated column, 6.2.2.5

call specification

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

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

in general, 4.1

searched

in general, 4.1.5

syntax diagram, 13

simple

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

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

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

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

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

in general, 2.2.4

nested, 2.2.4.2

PL/SQL Wrapper utility and, A.3

syntax diagram, 13

COMMIT statement

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

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

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

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

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

in general, 2.10.1.5.4

in DBMS_DB_VERSION package, 2.10.1.5.5

constrained subtype

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

in general, 4.2.4

syntax diagram, 13

CONTINUE WHEN statement

in general, 4.2.5

syntax diagram, 13

control statement, 4

control token, 2.10.1.1

correlated subquery, 6.3.4

correlation name

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

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

in general, 6.2

explicit

See explicit cursor

FOR UPDATE

in general, 6.6.6.2

after COMMIT or ROLLBACK, 6.6.6.3

implicit

See implicit cursor

in SERIALLY_REUSABLE package, 10.7.2

named

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

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

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

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

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

in general, 3.1

implicit

See implicit data type conversion

SQL injection and, 7.4.1.3

data type family

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

in general, 7.3

switching to native dynamic SQL from, 7.3

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

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

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

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

in general, 7

AUTHID property and, 8.13

native

in general, 7.2

switching to DBMS_SQL package from, 7.3

placeholder for bind argument 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

in general, 5.1

creating with constructor, 5.5

error directive, 2.10.1.3

error handling, 11

error-reporting function

SQLCODE, 13

SQLERRM

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

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

in general, 11.9

in FORALL statement, 12.4.1.2

OUT and IN OUT parameters and, 8.6.3

user-defined

See user-defined exception

exception handler

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

in general, 13

for giving error code to user-defined exception, 11.7.2

for giving name to internally defined exception, 11.3

EXECUTE IMMEDIATE statement

in general, 7.2.1

syntax diagram, 13

EXISTS collection method, 5.9.4

EXIT statement

in general, 4.2.2

syntax diagram, 13

EXIT WHEN statement

in general, 4.2.3

in basic LOOP statement, 4.2.3

syntax diagram, 13

exiting a loop, 4.2

explicit cursor

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

in general, 2.7

CURSOR

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

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

in general, 6.6.6.2

after COMMIT or ROLLBACK, 6.6.6.3

FORALL statement

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

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

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

global identifier, 2.5

GOTO statement

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

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

in general, 2.2.2.3

collecting data about, 12.7

visibility of, 2.5

See also name

IDL, C

IF statement

in general, 4.1

IF THEN form, 4.1.1

IF THEN ELSE form, 4.1.2

IF THEN ELSIF form

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

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

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

in general, 12.1.1

syntax diagram, 13

inner capture

in general, B.5.3

avoiding, B.6

input, 1.2.7

inquiry directive, 2.10.1.4

INSERT statement

inserting record with

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

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

in general, 11.3

giving name to, 11.3

raising explicitly, 11.7.1.2

interpreted compilation, 12.9.5

INVALID_CURSOR exception, 11.4

INVALID_NUMBER exception, 11.4

invoker's rights unit

See IR unit

IR unit

Abstract Data Type (ADT), 8.13.7

call stack and, 8.13

database link, 8.13.6

dynamic SQL and, 8.13

instance method, 8.13.8

invoked by trigger, 8.13.5

invoked by view, 8.13.5

name resolution and, 8.13

overriding name resolution in, 8.13.4

privilege checking and, 8.13

static SQL and, 8.13

template objects in, 8.13.3

See also AUTHID property

IS [NOT] NULL operator

collections and, 5.8.1

in generalNULL operator

aaa], 2.7.5.2

isolation level of transaction, 6.7.3

---

J

Java class method invocation, 8.14

---

K

key-value pair

See associative array

keywords

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

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

in general, 3.1.5

in trigger, 9.9.2

LONG RAW data type

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

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

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

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

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

in general, 8.2

declaration and definition of, 8.2

forward declaration for, 8.5

nested table

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

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

in general, 12.5.8

SQLCODE for, 11.4

NOCOPY hint

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

NOT NULL constraint

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

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

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

in general, 13

recursive invocation and, 8.9

OPEN statement

in general, 13

recursive invocation and, 8.9

operation, 2.7.2

operator

comparison

in general, 2.7.5

cursor variable and, 13

logical, 2.7.3

relational

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

in general, 8.8

INLINE pragma and, 12.1.1

---

P

package

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

in general, A

guideline for, A.2

package body

in general, 10.1

creating, 14

dropping, 14

initialization part of

in general, 10.4

assigning initial values in, 10.8

replacing, 14

package specification

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

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

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

in general, 12.5.1

where to specify, 12.5.2

pipelined table function

in general, 12.5.1

as aggregate function, 12.5.6

in autonomous transaction, 13

See also table function

placeholder for bind argument

in conditional compilation directive, 2.10.4

in dynamic SQL

EXECUTE IMMEDIATE statement and, 7.2.1

repeated, 7.2.3

in static SQL

in general, 6.1.1

OPEN FOR statement and, 6.4.2

in trigger body, 9.3.2

PLS_INTEGER data type

in general, 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 syntax, 13

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

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

in general, 2.9, 13

AUTONOMOUS_TRANSACTION

in general, 13

for pipelined table function, 12.5.2

INLINE

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

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

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

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

in general, 5

as public package item, 5.11.3

assigning value to, 5.12

creating

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

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

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

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

in general, 13

BULK COLLECT clause of

in general, 12.4.2.3

FORALL statement and, 12.4.3

REUSE SETTINGS clause, 1.3.2

ROLLBACK statement

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

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

run-time error

See exception

---

S

same-scope capture, B.5.2

SAMPLE clause, 12.4.2.1.2

SAVEPOINT statement

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

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

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

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

in general, 3.1.3

tuning code and, 12.3.5.1

SIMPLE_FLOAT subtype

in general, 3.1.3

tuning code and, 12.3.5.1

SIMPLE_INTEGER subtype

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

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

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

in general, 13

SQL%BULK_EXCEPTIONS and, 12.4.1.4

SQLJ object type, creating, 14

standalone stored subprogram

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

in general, 2.10.1.5.4

in DBMS_DB_VERSION package, 2.10.1.5.5

static expression, 2.10.1.5

static SQL

in general, 6

AUTHID property and, 8.13

name resolution in, B.4

placeholder for bind argument in

in general, 6.1.1

OPEN FOR statement and, 6.4.2

PL/SQL identifier in, 6.1.1

STORAGE_ERROR exception

in general, 11.4

recursive invocation and, 8.9

store table, 5.4.2

stored PL/SQL unit

in general, 1.3.2

creating, 14

recompiling, 14

replacing, 14

wrapping, A

stored subprogram

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

in general, 8

inlining, 12.1.1

invoked by trigger, 9.5

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

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

in general, 3

constrained

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

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

in general, 12.5.1

pipelined

See pipelined table function

weak cursor variable argument to, 6.4.1

TCL statement

in general, 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_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

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

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

in general, 11.9

in FORALL statement, 12.4.1.2

OUT and IN OUT parameters and, 8.6.3

unordered table, 5.1

UPDATE statement

BEFORE statement trigger and, 9.8

PL/SQL extensions to, 13

with values in record

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

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

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

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

See calculated column

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

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

in general, A

inquiry directives and, 2.10.1.4.3

---

Z

ZERO_DIVIDE exception, 11.4

zero-length string, 2.2.3