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