PL/SQL User's Guide and Reference -- Index

%BULK_EXCEPTIONS cursor attribute, 5-44

%BULK_ROWCOUNT cursor attribute, 5-43

%FOUND cursor attribute, 6-35, 6-39

%ISOPEN cursor attribute, 6-35, 6-39

%NOTFOUND cursor attribute, 6-35

%ROWCOUNT cursor attribute, 6-36, 6-39

+ addition/identity operator, 2-3

:= assignment operator, 1-4, 2-4

=> association operator, 2-4, 8-13

% attribute indicator, 1-7, 2-3

' character string delimiter, 2-3

. component selector, 1-6, 2-3

|| concatenation operator, 2-4, 2-28

/ division operator, 2-3

** exponentiation operator, 2-4

( expression or list delimiter, 2-3

) expression or list delimiter, 2-3

: host variable indicator, 2-3

. item separator, 2-3

<< label delimiter, 2-4

>> label delimiter, 2-4

/* multi-line comment delimiter, 2-4

*/ multi-line comment delimiter, 2-4

* multiplication operator, 2-3

" quoted identifier delimiter, 2-3

.. range operator, 2-4, 4-13

= relational operator, 2-3, 2-27

< relational operator, 2-3, 2-27

> relational operator, 2-3, 2-27

<> relational operator, 2-4, 2-27

!= relational operator, 2-4, 2-27

~= relational operator, 2-4, 2-27

^= relational operator, 2-4

<= relational operator, 2-4, 2-27

>= relational operator, 2-4, 2-27

@ remote access indicator, 2-3, 2-17

-- single-line comment delimiter, 2-4

; statement terminator, 2-3, 13-17

- subtraction/negation operator, 2-3

abstraction, 8-3, 10-2

ACCESS_INTO_NULL exception, 7-5

actual parameters, 6-8

address, 6-16

aggregate assignment, 2-15

aggregate functions

AVG, 6-2

COUNT, 6-2

GROUPING, 6-2

MAX, 6-2

MIN, 6-2

STDDEV, 6-2

SUM, 6-2

treatment of nulls, 6-2

VARIANCE, 6-2

AL16UTF16 character encoding, 3-11

aliasing, 8-21

ALL row operator, 6-3, 6-5

ALTER TYPE statement

for type evolution, 10-12

anonymous PL/SQL block, 8-2

ANY comparison operator, 6-5

apostrophes, 2-9

assignment operator, 1-4

assignment statement

syntax, 13-4

assignments

aggregate, 2-15

character string, B-2

collection, 5-15

cursor variable, 6-31

field, 5-56

record, 5-56

semantics, B-2

association operator, 8-13

associative arrays, 5-4

syntax, 13-29

versus nested tables, 5-6

asterisk (*) row operator, 6-3

asynchronous operations, 9-16

atomically null, 10-25

attribute indicator, 1-7

attributes, 1-7

%ROWTYPE, 2-14

%TYPE, 2-13

cursor, 6-34

object, 10-3, 10-7

AUTHID clause, 8-4, 8-6, 8-52

autonomous transactions, 6-53

advantages, 6-53

controlling, 6-57

autonomous triggers, 6-59

AUTONOMOUS_TRANSACTION pragma, 6-54

syntax, 13-8

AVG aggregate function, 6-2

base types, 3-3, 3-21

basic loops, 4-9

BETWEEN comparison operator, 2-27, 6-5

BFILE datatype, 3-14

binary operators, 2-23

BINARY_INTEGER datatype, 3-3

bind argument, 11-3

binding, 5-38

blank-padding semantics, B-3

BLOB datatype, 3-14

blocks

anonymous, 8-2

label, 2-21

PL/SQL, 13-11

structure, 1-2

body

cursor, 6-12

function, 8-7

method, 10-8

object, 10-5

package, 9-8

procedure, 8-4

BOOLEAN datatype, 3-15

Boolean expressions, 2-28

Boolean literals, 2-9

built-in functions, 2-35

bulk binds, 5-37

BULK COLLECT clause, 5-46

bulk fetches, 5-47

bulk returns, 5-49

%BULK_EXCEPTIONS cursor attribute, 5-44

%BULK_ROWCOUNT cursor attribute, 5-43

by-reference parameter passing, 8-21

by-value parameter passing, 8-21

call specification, 9-3

calls

inter-language, 8-65

subprogram, 8-13

carriage returns, 2-2

CASE expressions, 2-31

case sensitivity

identifier, 2-5

string literal, 2-9

CASE statement, 4-5

syntax, 13-19

CASE_NOT_FOUND exception, 7-5

CHAR

datatype, 3-5

maximum width of column, 3-5

semantics, B-1

character literals, 2-8

character sets, 2-2

CHARACTER subtype, 3-6

character values

assigning, B-2

comparing, B-2

inserting, B-4

selecting, B-4

clauses

AUTHID, 8-4, 8-6, 8-52

BULK COLLECT, 5-46

LIMIT, 5-48

client programs, 10-2

CLOB datatype, 3-14

CLOSE statement, 6-10, 6-24

syntax, 13-22

collating sequence, 2-29

collection exceptions

when raised, 5-36

collection methods

applying to parameters, 5-35

COUNT, 5-29

DELETE, 5-34, 13-25

EXISTS, 5-29

EXTEND, 5-32

FIRST, 5-30

LAST, 5-30

LIMIT, 5-30

NEXT, 5-31

PRIOR, 5-31

syntax, 13-24

TRIM, 5-33

COLLECTION_IS_NULL exception, 7-5

collections, 5-2

assigning, 5-15

bulk binding, 5-37

bulk-binding of, 5-64

comparing, 5-17

constructors, 5-12

declaring, 5-10

defining, 5-7

element types, 5-7

initializing, 5-12

kinds, 5-1

multilevel, 5-26

referencing, 5-14

scope, 5-7

syntax, 13-29

column alias, 6-15

when needed, 2-16

COMMENT clause, 6-45

comments, 2-10

restrictions, 2-11

syntax, 13-36

COMMIT statement, 6-44

syntax, 13-37

comparison operators, 2-26, 6-5

comparisons

of character values, B-2

of collections, 5-17

of expressions, 2-28

compiler hint, NOCOPY, 8-17

compiling PL/SQL procedures for native execution, 12-14

component selector, 1-6

composite types, 3-2

compound symbols, 2-4

concatenation operator, 2-28

treatment of nulls, 2-34

concurrency, 6-43

conditional control, 4-2

constants

declaring, 2-12

syntax, 13-39

constraints

NOT NULL, 2-13

where not allowed, 8-4

constructors

collection, 5-12

defining, 10-28

object, 10-12

context

switching, 5-38

transaction, 6-56

control structures, 4-2

conditional, 4-2

iterative, 4-9

sequential, 4-17

conventions

naming, 2-17

conversion

functions, 3-25

conversion, datatype, 3-24

correlated subquery, 6-11

COUNT aggregate function, 6-2

COUNT collection method, 5-29

CURRENT OF clause, 6-50

current row, 1-5

CURRVAL pseudocolumn, 6-3

cursor attributes

%BULK_EXCEPTIONS, 5-44

%BULK_ROWCOUNT, 5-43

%FOUND, 6-35, 6-39

%ISOPEN, 6-35, 6-39

%NOTFOUND, 6-35

%ROWCOUNT, 6-36, 6-39

implicit, 6-38

syntax, 13-43

values, 6-36

cursor expressions, 6-41

cursor FOR loops, 6-13

passing parameters to, 6-15

cursor subqueries, 6-41

cursor variables, 6-16

as parameters to table functions, 8-39

assignment, 6-31

closing, 6-24

declaring, 6-18

fetching from, 6-23

opening, 6-19

restrictions, 6-33

syntax, 13-48

using to reduce network traffic, 6-30

using with dynamic SQL, 11-7

CURSOR_ALREADY_OPEN exception, 7-5

cursors, 1-5, 6-6

analogy, 1-5

closing, 6-10

declaring, 6-6

explicit, 6-6

fetching from, 6-8

implicit, 6-11

opening, 6-7

packaged, 6-12

parameterized, 6-8

RETURN clause, 6-12

scope rules, 6-7

syntax, 13-54

dangling refs, 10-36

data

abstraction, 10-2

encapsulation, 1-16

integrity, 6-43

locks, 6-43

data streaming

definition, 8-47

database changes

making permanent, 6-44

undoing, 6-45

database character set, 3-11

database triggers, 1-19

autonomous, 6-59

datatypes, 3-1

BFILE, 3-14

BINARY_INTEGER, 3-3

BLOB, 3-14

BOOLEAN, 3-15

CHAR, 3-5

CLOB, 3-14

constrained, 8-4

DATE, 3-16

families, 3-2

implicit conversion, 3-24

INTERVAL DAY TO SECOND, 3-20

INTERVAL YEAR TO MONTH, 3-19

LONG, 3-6

LONG RAW, 3-6

national character, 3-10

NCHAR, 3-11

NCLOB, 3-15

NUMBER, 3-3

NVARCHAR2, 3-12

PLS_INTEGER, 3-5

RAW, 3-7

RECORD, 5-51

REF CURSOR, 6-16

ROWID, 3-7

scalar versus composite, 3-1

TABLE, 5-2

TIMESTAMP, 3-17

TIMESTAMP WITH LOCAL TIME ZONE, 3-18

TIMESTAMP WITH TIME ZONE, 3-17

UROWID, 3-7

VARCHAR2, 3-9

VARRAY, 5-3

DATE datatype, 3-16

dates

converting, 3-26

TO_CHAR default format, 3-26

datetime literals, 2-9

DBMS_ALERT package, 9-16

DBMS_OUTPUT package, 9-16

DBMS_PIPE package, 9-17

deadlocks, 6-43

effect on transactions, 6-46

how broken, 6-46

DEC subtype, 3-4

DECIMAL subtype, 3-4

declarations

collection, 5-10

constant, 2-12

cursor, 6-6

cursor variable, 6-18

exception, 7-7

forward, 8-10

object, 10-25

record, 5-53

subprogram, 8-10

variable, 2-11

declarative part

of function, 8-7

of PL/SQL block, 1-3

of procedure, 8-4

DECODE function

treatment of nulls, 2-34

DEFAULT keyword, 2-12

default parameter values, 8-19

define variable, 11-3

definer rights, 8-49

versus invoker rights, 8-49

DELETE collection method, 5-34, 13-25

DELETE statement

RETURNING clause, 12-12

syntax, 13-58

delimiters, 2-3

dense collections, 5-3

DEPT database table, xxviii

DEREF function, 10-36

dereference, 10-36

DETERMINISTIC hint, 8-6

digits of precision, 3-4

DISTINCT operator, 6-3

DISTINCT row operator, 6-3, 6-6

distributed transactions, 6-44

dot notation, 1-6, 1-7

for collection methods, 5-28

for global variables, 4-16

for object attributes, 10-27

for object methods, 10-30

for package contents, 9-7

for record fields, 2-15

DOUBLE PRECISION subtype, 3-4

DUP_VAL_ON_INDEX exception, 7-5

dynamic FOR-loop range, 4-15

dynamic SQL, 11-2

tips and traps, 11-14

using EXECUTE IMMEDIATE statement, 11-3

using OPEN-FOR-USING statement, 11-7

dynamic string, 11-3

elaboration, 2-12

element types, collection, 5-7

ELSE clause, 4-3

ELSIF clause, 4-4

EMP database table, xxviii

encapsulation, data, 1-16

END IF reserved words, 4-3

END LOOP reserved words, 4-11

entended rowids, 3-8

error messages

maximum length, 7-18

evaluation, 2-23

short-circuit, 2-26

EXAMPBLD script, A-3

EXAMPLOD script, A-3

exception handlers, 7-15

branching from, 7-17

OTHERS handler, 7-2

using RAISE statement in, 7-15

using SQLCODE function in, 7-18

using SQLERRM function in, 7-18

EXCEPTION_INIT pragma, 7-8

syntax, 13-62

using with raise_application_error, 7-9

exception-handling part

of function, 8-7

of PL/SQL block, 1-3

of procedure, 8-4

exceptions, 7-2

declaring, 7-7

predefined, 7-4

propagation, 7-11

raised in declaration, 7-16

raised in handler, 7-17

raising with RAISE statement, 7-11

reraising, 7-14

scope rules, 7-7

syntax, 13-64

user-defined, 7-7

WHEN clause, 7-15

executable part

of function, 8-7

of PL/SQL block, 1-3

of procedure, 8-4

EXECUTE IMMEDIATE statement, 11-3

EXECUTE privilege, 8-55

execution environments, 1-17

EXISTS collection method, 5-29

EXISTS comparison operator, 6-5

EXIT statement, 4-9, 4-16

syntax, 13-71

WHEN clause, 4-10

where allowed, 4-9

explicit cursors, 6-6

expressions

Boolean, 2-28

CASE, 2-31

how evaluated, 2-23

parentheses in, 2-24

syntax, 13-73

EXTEND collection method, 5-32

extensibility, 8-3

external references, 8-52

how resolved, 8-53

external routines, 8-65

FALSE value, 2-9

features, new, xxxi

FETCH statement, 6-8, 6-23

syntax, 13-83

fetching

across commits, 6-51

bulk, 5-47

Fibonacci sequence, 8-60

fields, 5-51

file I/O, 9-17

FIRST collection method, 5-30

flag, PLSQL_V2_COMPATIBILITY, 6-62

FLOAT subtype, 3-4

FOR loops, 4-13

cursor, 6-13

dynamic range, 4-15

iteration scheme, 4-13

loop counter, 4-13

nested, 4-16

FOR UPDATE clause, 6-7

restriction on, 6-20

when to use, 6-50

FORALL statement, 5-41

syntax, 13-88

using with BULK COLLECT clause, 5-50

formal parameters, 6-8

format

masks, 3-26

of functions, 8-6

of packaged subprograms, 8-11

of procedures, 8-3

forward declarations, 8-10

when needed, 8-10, 8-63

forward references, 2-17

forward type definitions, 10-33

%FOUND cursor attribute, 6-35, 6-39

functions, 8-1, 8-6

body, 8-7

built-in, 2-35

call, 8-8

parameter, 8-6

parts, 8-7

RETURN clause, 8-7

specification, 8-7

syntax, 13-92

gigabyte, 3-14

GOTO statement, 4-17

label, 4-17

misuse, 4-19

restriction, 7-17

syntax, 13-99

GROUP BY clause, 6-3

GROUPING aggregate function, 6-2

guess, 3-9

handlers, exception, 7-2

handling exceptions, 7-1

raised in declaration, 7-16

raised in handler, 7-17

using OTHERS handler, 7-15

handling of nulls, 2-33

hash tables

simulating with associative arrays, 5-6

hidden declarations, 9-3

hint, DETERMINISTIC, 8-6

hint, NOCOPY, 8-17

host arrays

bulk binds, 5-50

hypertext markup language (HTML), 9-18

hypertext transfer protocol (HTTP), 9-18

identifiers

forming, 2-5

maximum length, 2-5

quoted, 2-6

scope rules, 2-19

IF statement, 4-2

ELSE clause, 4-3

ELSIF clause, 4-4

syntax, 13-101

THEN clause, 4-3

implicit cursors, 6-11

attributes, 6-38

implicit datatype conversion, 3-24

effect on performance, 12-3

implicit declarations

cursor FOR loop record, 6-13

FOR loop counter, 4-15

IN comparison operator, 2-28, 6-5

IN OUT parameter mode, 8-16

IN parameter mode, 8-14

incomplete object types, 10-33

index-by tables

See Also associative arrays

infinite loops, 4-9

information hiding, 1-16, 9-5

inheritance

and overloading, 8-28

in PL/SQL, 10-14

initialization

collection, 5-12

object, 10-25

package, 9-8

record, 5-53

using DEFAULT, 2-12

variable, 2-22

when required, 2-13

INSERT statement

RETURNING clause, 12-12

syntax, 13-104

with a record variable, 5-61

instances, 10-4

INT subtype, 3-4

INTEGER subtype, 3-4

inter-language calls, 8-65

INTERSECT set operator, 6-6

INTERVAL DAY TO SECOND datatype, 3-20

INTERVAL YEAR TO MONTH datatype, 3-19

INTO clause, 6-23

INTO list, 6-9

INVALID_CURSOR exception, 7-5

INVALID_NUMBER exception, 7-5

invoker rights, 8-50

advantages, 8-50

versus definer rights, 8-49

IS DANGLING predicate, 10-36

IS NULL comparison operator, 2-27, 6-5

IS OF predicate, 10-14

%ISOPEN cursor attribute, 6-35, 6-39

iteration

scheme, 4-13

versus recursion, 8-64

iterative control, 4-9

joins, 8-62

labels

block, 2-21

GOTO statement, 4-17

loop, 4-11

large object (LOB) datatypes, 3-13

LAST collection method, 5-30

LEVEL pseudocolumn, 6-4

lexical units, 2-2

LIKE comparison operator, 2-27, 6-5

LIMIT clause, 5-48

LIMIT collection method, 5-30

limitations, PL/SQL, E-1

literals, 2-7

Boolean, 2-9

character, 2-8

datetime, 2-9

numeric, 2-7

string, 2-9

syntax, 13-107

LOB (large object) datatypes, 3-13

lob locators, 3-13

local subprograms, 1-19

locator variables, 7-22

LOCK TABLE statement, 6-51

syntax, 13-110

locks, 6-43

modes, 6-43

overriding, 6-49

using FOR UPDATE clause, 6-50

logical rowids, 3-7

LOGIN_DENIED exception, 7-5

LONG datatype, 3-6

maximum length, 3-6

restrictions, 3-7

LONG RAW datatype, 3-6

converting, 3-27

maximum length, 3-6

LOOP statement, 4-9

forms, 4-9

syntax, 13-112

loops

counters, 4-13

kinds, 4-9

labels, 4-11

maintainability, 8-3

makefile

for native compilation of PL/SQL procedures, 12-14

map methods, 10-10

MAX aggregate function, 6-2

maximum precision, 3-4

maximum size

CHAR value, 3-5

identifier, 2-5

LOB, 3-13

LONG RAW value, 3-6

LONG value, 3-6

NCHAR value, 3-12

NVARCHAR2 value, 3-12

Oracle error message, 7-18

RAW value, 3-7

VARCHAR2 value, 3-9

membership test, 2-28

MERGE statement

syntax, 13-119

method calls, chaining, 10-30

methods

collection, 5-28

COUNT, 5-29

DELETE, 5-34, 13-25

EXISTS, 5-29

EXTEND, 5-32

FIRST, 5-30

LAST, 5-30

LIMIT, 5-30

map, 10-10

NEXT, 5-31

object, 10-3, 10-7

order, 10-10

PRIOR, 5-31

TRIM, 5-33

MIN aggregate function, 6-2

MINUS set operator, 6-6

mixed notation, 8-13

modes, parameter

IN, 8-14

IN OUT, 8-16

OUT, 8-14

modularity, 1-12, 8-3, 9-5

multilevel collections, 5-26

multi-line comments, 2-10

mutual recursion, 8-63

name resolution, 2-18, D-1

named notation, 8-13

names

cursor, 6-7

qualified, 2-17

savepoint, 6-47

variable, 2-18

naming conventions, 2-17

national character datatypes, 3-10

national character set, 3-11

National Language Support (NLS), 3-11

native dynamic SQL. See dynamic SQL

native execution

compiling PL/SQL procedures for, 12-14

NATURAL subtype, 3-3

NATURALN subtype, 3-3

NCHAR datatype, 3-11

NCLOB datatype, 3-15

nested collections, 5-26

nested cursors, 6-41

nested tables

manipulating, 5-18

syntax, 13-29

versus associative arrays, 5-6

nesting

block, 1-3

FOR loop, 4-16

object, 10-7

record, 5-52

network traffic

reducing, 1-22

new features, xxxi

NEXT collection method, 5-31

NEXTVAL pseudocolumn, 6-3

nibble, 3-27

NLS (National Language Support), 3-11

NO_DATA_FOUND exception, 7-5

NOCOPY compiler hint, 8-17

restrictions on, 8-19

non-blank-padding semantics, B-3

NOT logical operator

treatment of nulls, 2-33

NOT NULL constraint

effect on %TYPE declaration, 2-14

effect on performance, 12-4

restriction, 6-7, 8-4

using in collection declaration, 5-11

using in field declaration, 5-54

using in variable declaration, 2-13

NOT_LOGGED_ON exception, 7-5

notation

mixed, 8-13

positional versus named, 8-13

%NOTFOUND cursor attribute, 6-35

NOWAIT parameter, 6-50

NVARCHAR2 datatype, 3-12

NVL function

treatment of nulls, 2-35

null handling, 2-33

in dynamic SQL, 11-16

NULL statement, 4-21

syntax, 13-119, 13-121

using in a procedure, 8-4

nullity, 2-27

NUMBER datatype, 3-3

numeric literals, 2-7

NUMERIC subtype, 3-4

object attributes, 10-3, 10-7

accessing, 10-27

allowed datatypes, 10-7

maximum number, 10-7

object constructors

calling, 10-29

passing parameters to, 10-29

object methods, 10-3, 10-7

calling, 10-30

object tables, 10-34

object types, 10-1, 10-3

advantages, 10-5

defining, 10-13

examples, 10-13

structure, 10-5

syntax, 13-122

object-oriented programming, 10-1

objects, 10-4

declaring, 10-25

initializing, 10-25

manipulating, 10-33

sharing, 10-31

OPEN statement, 6-7

syntax, 13-131

OPEN-FOR statement, 6-19

syntax, 13-133

OPEN-FOR-USING statement, 11-7

syntax, 13-136

operators

comparison, 2-26

precedence, 2-24

relational, 2-27

option, PARALLEL_ENABLE, 8-6

OR keyword, 7-16

order methods, 10-10

order of evaluation, 2-24, 2-25

OTHERS exception handler, 7-2, 7-15

OUT parameter mode, 8-14

overloading, 8-23

and inheritance, 8-28

object method, 10-10

packaged subprogram, 9-15

restrictions, 8-24

using subtypes, 8-25

overriding methods, 10-14

packaged cursors, 6-12

packaged subprograms, 1-19, 8-11

calling, 9-7

overloading, 9-15

packages, 9-1, 9-2

advantages, 9-5

bodiless, 9-6

body, 9-2

initializing, 9-8

private versus public objects, 9-14

product-specific, 9-16

referencing, 9-7

scope, 9-6

serially reusable, 12-6

specification, 9-2

syntax, 13-139

parallel execution of table functions, 8-43

PARALLEL_ENABLE option, 8-6

parameter aliasing, 8-21

parameter passing

by reference, 8-21

by value, 8-21

in dynamic SQL, 11-6

parameters

actual versus formal, 8-12

cursor, 6-8

default values, 8-19

modes, 8-14

SELF, 10-8

parentheses, 2-24

PARTITION BY clause

of CREATE FUNCTION statement, 8-44

partitioned data

and table functions, 8-43

pattern matching, 2-27

performance, 1-21

physical rowids, 3-7

pipe, 9-17

PIPE ROW statement

for returning rows incrementally, 8-36

pipelining

definition, 8-30

placeholders, 11-2

duplicate, 11-15

PLS_INTEGER datatype, 3-5

PL/SQL

advantages, 1-20

architecture, 1-17

block structure, 1-2

blocks

anonymous, 1-2

syntax, 13-11

compiler

how calls are resolved, 8-26

engine

in Oracle server, 1-18

in Oracle tools, 1-20

execution environments, 1-17

limitations, E-1

performance, 1-21

portability, 1-23

procedural aspects, 1-2

reserved words, F-1

sample programs, A-1

Server Pages (PSPs), 8-66

support for SQL, 1-20

syntax, 13-1

PL/SQL blocks

anonymous, 8-2

PLSQL_COMPILER_FLAGS initialization parameter, 12-15

PLSQL_NATIVE_LIBRARY_DIR initialization parameter, 12-15

PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT initialization parameter, 12-15

PLSQL_NATIVE_MAKE_FILE_NAME initialization parameter, 12-15

PLSQL_NATIVE_MAKE_UTILITY initialization parameter, 12-15

PLSQL_V2_COMPATIBILITY flag, 6-62

pointers, 6-16

portability, 1-23

positional notation, 8-13

POSITIVE subtype, 3-3

POSITIVEN subtype, 3-3

pragmas, 7-8

AUTONOMOUS_TRANSACTION, 6-54

EXCEPTION_INIT, 7-8

RESTRICT_REFERENCES, 6-61, 8-9, 11-18

SERIALLY_REUSABLE, 12-6

precedence, operator, 2-24

precision of digits

specifying, 3-4

predefined exceptions

list of, 7-4

raising explicitly, 7-11

redeclaring, 7-10

predicates, 6-5

PRIOR collection method, 5-31

PRIOR row operator, 6-4, 6-6

private objects, 9-14

procedural abstraction, 10-2

procedures, 8-1, 8-3

body, 8-4

calling, 8-5

parameter, 8-3

parts, 8-4

specification, 8-4

syntax, 13-145

productivity, 1-22

program units, 1-12

PROGRAM_ERROR exception, 7-6

propagation, exception, 7-11

pseudocolumns, 6-3

CURRVAL, 6-3

LEVEL, 6-4

NEXTVAL, 6-3

ROWID, 6-4

ROWNUM, 6-4

public objects, 9-14

purity rules, 8-9

qualifiers

using subprogram names as, 2-19

when needed, 2-17, 2-21

query work areas, 6-16

quoted identifiers, 2-6

RAISE statement, 7-11

syntax, 13-150

using in exception handler, 7-15

raise_application_error procedure, 7-9

raising an exception, 7-10

range operator, 4-13

RAW datatype, 3-7

converting, 3-27

maximum length, 3-7

read consistency, 6-44

READ ONLY parameter, 6-49

readability, 2-2, 4-21

read-only transaction, 6-48

REAL subtype, 3-4

RECORD datatype, 5-51

records, 5-51

%ROWTYPE, 6-13

assigning, 5-56

bulk-binding collections of, 5-64

comparing, 5-58

declaring, 5-53

defining, 5-51

implicit declaration, 6-13

initializing, 5-53

inserting, 5-61

manipulating, 5-58

nesting, 5-52

referencing, 5-54

restrictions on inserts/updates of, 5-64

returning into, 5-63

syntax, 13-152

updating, 5-61

recursion, 8-60

infinite, 8-60

mutual, 8-63

terminating condition, 8-60

versus iteration, 8-64

REF CURSOR datatype, 6-16

defining, 6-17

REF CURSOR variables

as parameters to table functions, 8-39

predefined SYS_REFCURSOR type, 8-39

REF function, 10-35

REF type modifier, 10-31

reference datatypes, 3-2

references, external, 8-52

refs, 10-31

dangling, 10-36

declaring, 10-31

dereferencing, 10-36

relational operators, 2-27

remote access indicator, 2-17

REPEAT UNTIL structure

mimicking, 4-12

REPLACE function

treatment of nulls, 2-35

reraising an exception, 7-14

reserved words, F-1

misuse of, 2-6

using as quoted identifier, 2-7

resolution, name, 2-18, D-1

RESTRICT_REFERENCES pragma, 8-9

syntax, 13-156

using with autonomous functions, 6-61

using with dynamic SQL, 11-18

restricted rowids, 3-8

result set, 1-5, 6-7

RETURN clause

cursor, 6-12

function, 8-7

RETURN statement, 8-8

syntax, 13-159

return type, 6-17, 8-25

return value, function, 8-7

RETURNING clause, 10-39, 12-12

with a record variable, 5-63

reusability, 8-3

reusable packages, 12-6

REVERSE reserved word, 4-13

rollback segments, 6-44

ROLLBACK statement, 6-45

effect on savepoints, 6-47

syntax, 13-161

rollbacks

implicit, 6-47

of FORALL statement, 5-42

statement-level, 6-46

routines, external, 8-65

row locks, 6-50

row operators, 6-6

%ROWCOUNT cursor attribute, 6-36, 6-39

ROWID datatype, 3-7

ROWID pseudocolumn, 6-4

rowids, 3-7

extended, 3-8

guess, 3-9

logical, 3-7

physical, 3-7

restricted, 3-8

universal, 3-7

ROWIDTOCHAR function, 6-4

ROWNUM pseudocolumn, 6-4

%ROWTYPE attribute, 2-14

syntax, 13-163

ROWTYPE_MISMATCH exception, 7-6

RPC (remote procedure call), 7-12

RTRIM function

using to insert data, B-4

rules, purity, 8-9

run-time errors, 7-1

sample database tables

DEPT table, xxviii

EMP table, xxviii

sample programs, A-1

savepoint names

reusing, 6-47

SAVEPOINT statement, 6-46

syntax, 13-165

scalar datatypes, 3-2

scale

specifying, 3-4

schemes, iteration, 4-13

scientific notation, 2-8

scope, 2-19

collection, 5-7

cursor, 6-7

cursor parameter, 6-7

definition, 2-19

exception, 7-7

identifier, 2-19

loop counter, 4-15

package, 9-6

searched CASE expression, 2-32

SELECT INTO statement

syntax, 13-166

selector, 2-31

SELF parameter, 10-8

semantics

assignment, B-2

blank-padding, B-3

CHAR versus VARCHAR2, B-1

non-blank-padding, B-3

string comparison, B-2

separators, 2-3

sequence, 6-3

sequential control, 4-17

serially reusable packages, 12-6

SERIALLY_REUSABLE pragma, 12-6

syntax, 13-171

Server Pages, PL/SQL, 8-66

session, 6-43

session-specific variables, 9-11

set operators, 6-6

SET TRANSACTION statement, 6-48

syntax, 13-173

short-circuit evaluation, 2-26

side effects, 8-14

controlling, 8-9

significant characters, 2-5

SIGNTYPE subtype, 3-3

simple symbols, 2-3

single-line comments, 2-10

size limit, varray, 5-8

SMALLINT subtype, 3-4

snapshots, 6-44

SOME comparison operator, 6-5

spaces

where allowed, 2-2

spaghetti code, 4-17

sparse collections, 5-3

specification

call, 9-3

cursor, 6-12

function, 8-7

method, 10-8

object, 10-5

package, 9-6

procedure, 8-4

SQL

comparison operators, 6-5

data manipulation statements, 6-2

dynamic, 11-2

pseudocolumns, 6-3

row operators, 6-6

set operators, 6-6

support in PL/SQL, 1-20

SQL cursor

syntax, 13-175

SQLCODE function, 7-18

syntax, 13-178

SQLERRM function, 7-18

syntax, 13-180

stack, 10-16

standalone subprograms, 1-19

START WITH clause, 6-4

statement terminator, 13-17

statement-level rollbacks, 6-46

statements, PL/SQL

assignment, 13-4

CASE, 13-19

CLOSE, 6-10, 6-24, 13-22

COMMIT, 13-37

DELETE, 13-58

dynamic SQL, 11-2

EXECUTE IMMEDIATE, 11-3

EXIT, 13-71

FETCH, 6-8, 6-23, 13-83

FORALL, 5-41

GOTO, 13-99

IF, 13-101

INSERT, 13-104

LOCK TABLE, 13-110

LOOP, 13-112

MERGE, 13-119

NULL, 13-119, 13-121

OPEN, 6-7, 13-131

OPEN-FOR, 6-19, 13-133

OPEN-FOR-USING, 11-7

RAISE, 13-150

RETURN, 13-159

ROLLBACK, 13-161

SAVEPOINT, 13-165

SELECT INTO, 13-166

SET TRANSACTION, 13-173

UPDATE, 13-184

STDDEV aggregate function, 6-2

STEP clause

mimicking, 4-14

stepwise refinement, 1-2

STORAGE_ERROR exception, 7-6

when raised, 8-60

store tables, 5-7

stored subprograms, 1-18

streaming, data

definition, 8-47

string comparison semantics, B-2

string literals, 2-9

STRING subtype, 3-10

structure theorem, 4-2

stubs, 4-21, 8-3

subprograms, 8-2

advantages, 8-3

declaring, 8-10

how calls are resolved, 8-26

local, 1-19

overloading, 8-23

packaged, 1-19, 8-11

parts, 8-2

procedure versus function, 8-6

recursive, 8-60

standalone, 1-19

stored, 1-18

subquery, 6-11

SUBSCRIPT_BEYOND_COUNT exception, 7-6

SUBSCRIPT_OUTSIDE_LIMIT exception, 7-6

substitutability of object types, 8-28

SUBSTR function, 7-19

subtypes, 3-3, 3-21, 10-14

CHARACTER, 3-6

compatibility, 3-23

constrained versus unconstrained, 3-22

DEC, 3-4

DECIMAL, 3-4

defining, 3-22

DOUBLE PRECISION, 3-4

FLOAT, 3-4

INT, 3-4

INTEGER, 3-4

NATURAL, 3-3

NATURALN, 3-3

NUMERIC, 3-4

overloading, 8-25

POSITIVE, 3-3

POSITIVEN, 3-3

REAL, 3-4

SIGNTYPE, 3-3

SMALLINT, 3-4

STRING, 3-10

VARCHAR, 3-10

SUM aggregate function, 6-2

supertypes, 10-14

support for SQL, 6-2

symbols

compound, 2-4

simple, 2-3

syntax

definition, 13-1

diagram reading, 13-3

SYS_REFCURSOR type, 8-39

system-specific Oracle documentation

PL/SQL wrapper, 12-15

TABLE datatype, 5-2

table functions, 8-30

doing transformations with, 8-34

parallel execution, 8-43

pipelined, 8-33

querying, 8-37

TABLE operator, 5-22

tabs, 2-2

terminating condition, 8-60

terminator, statement, 2-3

ternary operators, 2-23

THEN clause, 4-3

TIMEOUT_ON_RESOURCE exception, 7-6

TIMESTAMP datatype, 3-17

TIMESTAMP WITH LOCAL TIME ZONE datatype, 3-18

TIMESTAMP WITH TIME ZONE datatype, 3-17

TOO_MANY_ROWS exception, 7-6

top-down design, 1-16

trailing blanks

how handled, B-4

transactions, 6-2

autonomous, 6-53

committing, 6-44

context, 6-56

distributed, 6-44

ending properly, 6-48

processing, 6-2, 6-43

read-only, 6-48

rolling back, 6-45

visibility, 6-56

TREAT operator, 10-14

triggers, 1-19

autonomous, 6-59

TRIM collection method, 5-33

TRUE value, 2-9

%TYPE attribute, 2-13

syntax, 13-182

type definitions

collection, 5-7

forward, 10-33

RECORD, 5-51

REF CURSOR, 6-17

type evolution

in PL/SQL, 10-12

type inheritance

in PL/SQL, 10-14

unary operators, 2-23

underscores, 2-5

unhandled exceptions, 7-12, 7-19

uninitialized object

how treated, 10-26

UNION ALL set operator, 6-6

UNION set operator, 6-6

universal rowids, 3-7

UPDATE statement

RETURNING clause, 12-12

syntax, 13-184

with a record variable, 5-61

URL (uniform resource locator), 9-18

UROWID datatype, 3-7

user session, 6-43

user-defined exceptions, 7-7

user-defined records, 5-51

user-defined subtypes, 3-21

USING clause, 11-3, 13-68

UTF8 character encoding, 3-11

UTL_FILE package, 9-17

UTL_HTTP package, 9-18

VALUE function, 10-35

VALUE_ERROR exception, 7-6

VARCHAR subtype, 3-10

VARCHAR2

datatype, 3-9

semantics, B-1

variables

assigning values, 2-22

declaring, 2-11

initializing, 2-22

session-specific, 9-11

syntax, 13-39

VARIANCE aggregate function, 6-2

VARRAY datatype, 5-3

varrays

size limit, 5-8

syntax, 13-29

visibility

of package contents, 9-3

transaction, 6-56

versus scope, 2-19

WHEN clause, 4-10, 7-15

WHILE loop, 4-11

wildcards, 2-27

words, reserved, F-1

work areas, query, 6-16

Wrap Utility, C-1

input and output files, C-3

running, C-2

ZERO_DIVIDE exception, 7-6

Index

Symbols

A

B

C

D

E

F

G

H

I

J

L

M

N

O

P

Q

R

S

T

U

V

W

Z