PL/SQL User's Guide and Reference
Release 8.1.6
A77069-01

Index

A B C D E F G H I J L M N O P Q R S T U V W Z

Symbols

+ addition/identity operator, 2-3

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

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

% attribute indicator, 1-7, 2-3

' character string delimiter, 2-3

. component selector, 1-6, 2-3

|| concatenation operator, 2-4, 2-47

/ 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, 3-10

= relational operator, 2-3, 2-46

< relational operator, 2-3, 2-46

> relational operator, 2-3, 2-46

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

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

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

^= relational operator, 2-4

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

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

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

-- single-line comment delimiter, 2-4

; statement terminator, 2-3, 11-15

- subtraction/negation operator, 2-3

A

abstraction, 7-3, 9-2

ACCESS_INTO_NULL exception, 6-5

actual parameters, 5-8

aggregate assignment, 2-34

aggregate functions

treatment of nulls, 5-3

ALL row operator, 5-3, 5-5

anonymous PL/SQL block, 7-2

ANY comparison operator, 5-5

apostrophes, 2-9

architecture, 1-17

assignment operator, 1-4

assignment statement

assignments

aggregate, 2-34

character string, B-2

collection, 4-11

cursor variable, 5-32

association operator, 7-13

asterisk (*) row operator, 5-3

asynchronous operations, 8-17

atomically null, 9-23

attribute indicator, 1-7

attributes, 1-7

object, 9-3, 9-7

AUTHID clause, 7-4, 7-6, 7-31

autonomous transactions, 5-52

advantages, 5-52

controlling, 5-56

autonomous triggers, 5-58

AUTONOMOUS_TRANSACTION pragma, 5-53

AVG aggregate function, 5-3

B

C

call specification, 8-3

calls

inter-language, 7-44

subprogram, 7-13

carriage returns, 2-2

case sensitivity

identifier, 2-5

string literal, 2-9

CHAR

CHAR column

maximum width, 2-15

character literals, 2-8

character sets, 2-2

CHARACTER subtype, 2-15

character values

clauses

AUTHID, 7-4, 7-6, 7-31

BULK COLLECT, 4-37

client programs, 9-2

CLOB datatype, 2-23

CLOSE statement, 5-11, 5-24

collating sequence, 2-48

collection exceptions

when raised, 4-28

collection methods

applying to parameters, 4-27

DELETE, 4-26, 11-20

COLLECTION_IS_NULL exception, 6-5

collections, 4-2

assigning, 4-11

bulk binding, 4-29

comparing, 4-13

constructors, 4-8

element types, 4-5

initializing, 4-8

referencing, 4-10

column alias, 5-14

when needed, 2-35

COMMENT clause, 5-43

restrictions, 2-11

COMMIT statement, 5-43

comparison operators, 2-45, 5-5

comparisons

of character values, B-2

of collections, 4-13

of expressions, 2-47

compiler hint, NOCOPY, 7-17

component selector, 1-6

composite types, 2-11

compound symbols, 2-4

concatenation operator, 2-47

treatment of nulls, 2-51

concurrency, 5-41

conditional control, 3-2

constants

declaring, 2-31

constraints

where not allowed, 7-4

constructors

collection, 4-8

context

switching, 4-30

transaction, 5-55

control structures, 3-2

conditional, 3-2

sequential, 3-15

conventions

conversion functions

when needed, 2-29

conversion, datatype, 2-28

correlated subquery, 5-11

COUNT aggregate function, 5-3

COUNT collection method, 4-22

CURRENT OF clause, 5-49

current row, 1-5

CURRVAL pseudocolumn, 5-3

cursor attributes

%BULK_ROWCOUNT, 4-34

%FOUND, 5-35, 5-39

%ISOPEN, 5-35, 5-39

%NOTFOUND, 5-36

%ROWCOUNT, 5-36, 5-40

cursor FOR loop

passing parameters to, 5-15

cursor FOR loops, 5-13

cursor variables, 5-15

assignment, 5-32

declaring, 5-17

fetching from, 5-23

restrictions, 5-34

using to reduce network traffic, 5-31

using with dynamic SQL, 10-7

CURSOR_ALREADY_OPEN exception, 6-5

cursors, 1-5, 5-6

fetching from, 5-9

parameterized, 5-8

RETURN clause, 5-12

scope rules, 5-7

D

dangling refs, 9-33

data

abstraction, 9-2

encapsulation, 1-16

integrity, 5-41

database changes

making permanent, 5-43

database character set, 2-20

database triggers, 1-19

autonomous, 5-58

datatypes, 2-11

DATE datatype, 2-24

dates

converting, 2-29

TO_CHAR default format, 2-29

DBMS_ALERT package, 8-17

DBMS_OUTPUT package, 8-17

DBMS_PIPE package, 8-18

deadlocks, 5-42

effect on transactions, 5-44

how broken, 5-44

DEC subtype, 2-14

DECIMAL subtype, 2-14

declarations

collection, 4-7

cursor variable, 5-17

subprogram, 7-10

declarative part

of function, 7-7

of PL/SQL block, 1-3

of procedure, 7-4

DECODE function

treatment of nulls, 2-51

DEFAULT keyword, 2-31

default parameter values, 7-20

define variable, 10-3

definer rights, 7-29

versus invoker rights, 7-29

DELETE collection method, 4-26, 11-20

DELETE statement

RETURNING clause, 5-64

delimiters, 2-3

dense collections, 4-3

DEPT database table, xxi

DEREF function, 9-33

dereference, 9-33

DETERMINISTIC hint, 7-6

digits of precision, 2-13

DISTINCT operator, 5-3

DISTINCT row operator, 5-3, 5-6

distributed transactions, 5-42

dot notation, 1-6, 1-7

for collection methods, 4-21

for global variables, 3-13

for object attributes, 9-24

for object methods, 9-26

for package contents, 8-7

for record fields, 2-34

DOUBLE PRECISION subtype, 2-14

DUP_VAL_ON_INDEX exception, 6-5

dynamic FOR-loop range, 3-12

dynamic SQL, 10-2

tips and traps, 10-11

using EXECUTE IMMEDIATE statement, 10-3

using OPEN-FOR-USING statement, 10-7

dynamic string, 10-3

E

elaboration, 2-31

element types, collection, 4-5

ELSE clause, 3-3

ELSIF clause, 3-4

EMP database table, xxi

encapsulation, data, 1-16

END IF reserved words, 3-3

END LOOP reserved words, 3-9

entended rowids, 2-17

error messages

maximum length, 6-18

evaluation, 2-42

short-circuit, 2-45

EXAMPBLD script, A-3

EXAMPLOD script, A-3

exception handlers, 6-15

branching from, 6-17

OTHERS handler, 6-2

using RAISE statement in, 6-15

using SQLCODE function in, 6-18

using SQLERRM function in, 6-18

EXCEPTION_INIT pragma, 6-8

using with raise_application_error, 6-10

exception-handling part

of function, 7-7

of PL/SQL block, 1-3

of procedure, 7-4

exceptions, 6-2

predefined, 6-4

propagation, 6-12

raised in declaration, 6-16

raised in handler, 6-17

raising with RAISE statement, 6-11

reraising, 6-14

scope rules, 6-7

user-defined, 6-7

WHEN clause, 6-15

executable part

of function, 7-7

of PL/SQL block, 1-3

of procedure, 7-4

EXECUTE IMMEDIATE statement, 10-3

EXECUTE privilege, 7-35

execution environments, 1-17

EXISTS collection method, 4-22

EXISTS comparison operator, 5-5

EXIT statement, 3-6, 3-14

WHEN clause, 3-7

where allowed, 3-6

explicit cursors, 5-6

expressions

how evaluated, 2-42

parentheses in, 2-43

EXTEND collection method, 4-24

extensibility, 7-3

external references, 7-31

how resolved, 7-32

external routines, 7-44

F

FALSE value, 2-9

FETCH statement, 5-9, 5-23

fetching

across commits, 5-50

Fibonacci sequence, 7-39

FIRST collection method, 4-23

flag, PLSQL_V2_COMPATIBILITY, 5-69

FLOAT subtype, 2-14

FOR loops, 3-10

dynamic range, 3-12

iteration scheme, 3-10

loop counter, 3-10

FOR UPDATE clause, 5-8

restriction on, 5-19

when to use, 5-48

FORALL statement, 4-32

using with BULK COLLECT clause, 4-41

formal parameters, 5-8

format

of functions, 7-6

of packaged subprograms, 7-11

of procedures, 7-3

format masks

when needed, 2-29

forward declarations, 7-10

when needed, 7-10, 7-42

forward references, 2-36

forward type definitions, 9-29

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

functions, 7-1, 7-6

RETURN clause, 7-7

specification, 7-7

G

GOTO statement, 3-15

restriction, 6-17

GROUP BY clause, 5-3

GROUPING aggregate function, 5-3

H

handlers, exception, 6-2

handling exceptions, 6-1

raised in declaration, 6-16

raised in handler, 6-17

using OTHERS handler, 6-15

handling of nulls, 2-49

hidden declarations, 8-3

hint, DETERMINISTIC, 7-6

hint, NOCOPY, 7-17

host arrays

bulk binds, 4-41

hypertext markup language (HTML), 8-18

hypertext transfer protocol (HTTP), 8-18

I

identifiers

maximum length, 2-5

scope rules, 2-38

IF statement, 3-2

ELSE clause, 3-3

ELSIF clause, 3-4

THEN clause, 3-3

implicit cursors, 5-11

attributes, 5-39

implicit datatype conversion, 2-28

effect on performance, 5-68

implicit declarations

cursor FOR loop record, 5-13

FOR loop counter, 3-13

IN comparison operator, 2-47, 5-6

IN OUT parameter mode, 7-16

IN parameter mode, 7-14

incomplete object types, 9-29

index-by tables

versus nested tables, 4-3

infinite loops, 3-6

information hiding, 1-15, 8-5

initialization

collection, 4-8

using DEFAULT, 2-31

when required, 2-32

INSERT statement

RETURNING clause, 5-64

INT subtype, 2-14

INTEGER subtype, 2-14

inter-language calls, 7-44

INTERSECT set operator, 5-6

INTO clause, 5-23

INVALID_CURSOR exception, 6-5

INVALID_NUMBER exception, 6-5

invoker rights, 7-29

advantages, 7-30

versus definer rights, 7-29

IS DANGLING predicate, 9-33

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

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

iteration

versus recursion, 7-43

iterative control, 3-6

J

L

labels

GOTO statement, 3-15

large object (LOB) datatypes, 2-22

LAST collection method, 4-23

LEVEL pseudocolumn, 5-4

lexical units, 2-2

LIKE comparison operator, 2-46, 5-6

LIMIT clause, 4-39

LIMIT collection method, 4-22

limitations, PL/SQL, E-1

LOB (large object) datatypes, 2-22

lob locators, 2-22

local subprograms, 1-18

locator variables, 6-22

LOCK TABLE statement, 5-49

overriding, 5-48

using FOR UPDATE clause, 5-48

logical rowids, 2-17

LOGIN_DENIED exception, 6-5

LONG datatype, 2-16

maximum length, 2-16

restrictions, 2-16

LONG RAW datatype, 2-16

converting, 2-30

maximum length, 2-16

LOOP statement, 3-6

loops

M

maintainability, 7-3

map methods, 9-10

MAX aggregate function, 5-3

maximum precision, 2-13

maximum size

CHAR value, 2-15

identifier, 2-5

LONG RAW value, 2-16

LONG value, 2-16

NCHAR value, 2-20

NVARCHAR2 value, 2-21

Oracle error message, 6-18

RAW value, 2-16

VARCHAR2 value, 2-19

membership test, 2-47

method calls, chaining, 9-26

methods

collection, 4-21

DELETE, 4-26, 11-20

object, 9-3, 9-8

MIN aggregate function, 5-3

MINUS set operator, 5-6

mixed notation, 7-13

modes, parameter

modularity, 1-11, 7-3, 8-5

multi-line comments, 2-10

mutual recursion, 7-42

N

name resolution, 2-37, D-1

named notation, 7-13

names

qualified, 2-36

savepoint, 5-45

naming conventions, 2-36

national character datatypes, 2-20

national character set, 2-20

National Language Support (NLS), 2-20

native dynamic SQL. See dynamic SQL

NATURAL subtype, 2-13

NATURALN subtype, 2-13

NCHAR datatype, 2-20

NCLOB datatype, 2-23

nested tables

manipulating, 4-13

versus index-by tables, 4-3

nesting

network traffic

NEXT collection method, 4-23

NEXTVAL pseudocolumn, 5-3

NLS (National Language Support), 2-20

NO_DATA_FOUND exception, 6-5

NOCOPY compiler hint, 7-17

restrictions on, 7-19

non-blank-padding semantics, B-3

NOT logical operator

treatment of nulls, 2-50

NOT NULL constraint

effect on %TYPE declaration, 2-33

effect on performance, 5-67

restriction, 5-7, 7-4

using in collection declaration, 4-6

using in field declaration, 4-44

using in variable declaration, 2-32

NOT_LOGGED_ON exception, 6-5

notation

positional versus named, 7-13

%NOTFOUND cursor attribute, 5-36

NOWAIT parameter, 5-48

NVARCHAR2 datatype, 2-21

NVL function

treatment of nulls, 2-51

null handling, 2-49

in dynamic SQL, 10-13

NULL statement, 3-19

using in a procedure, 7-4

NUMBER datatype, 2-13

numeric literals, 2-7

NUMERIC subtype, 2-14

O

object attributes, 9-3, 9-7

accessing, 9-24

allowed datatypes, 9-7

maximum number, 9-7

object constructors

passing parameters to, 9-26

object methods, 9-3, 9-8

object tables, 9-30

object types, 9-1, 9-3

advantages, 9-5

object-oriented programming, 9-1

declaring, 9-22

initializing, 9-23

manipulating, 9-30

OPEN statement, 5-8

OPEN-FOR statement, 5-19

OPEN-FOR-USING statement, 10-7

operators

comparison, 2-45

precedence, 2-43

relational, 2-46

option, PARALLEL_ENABLE, 7-6

OR keyword, 6-16

order methods, 9-10

order of evaluation, 2-43, 2-44

OTHERS exception handler, 6-2, 6-15

OUT parameter mode, 7-14

overloading, 7-24

object method, 9-10

packaged subprogram, 8-15

restrictions, 7-25

using subtypes, 7-26

P

packaged cursors, 5-12

packaged subprograms, 1-18, 7-11

overloading, 8-15

packages, 8-1, 8-2

advantages, 8-5

initializing, 8-8

private versus public objects, 8-15

product-specific, 8-17

referencing, 8-7

serially reusable, 5-65

specification, 8-2

PARALLEL_ENABLE option, 7-6

parameter aliasing, 7-22

parameter passing

by reference, 7-22

in dynamic SQL, 10-6

parameters

actual versus formal, 7-12

default values, 7-20

parentheses, 2-43

pattern matching, 2-46

performance, 1-21

improving, 5-61

physical rowids, 2-17

placeholders, 10-2

duplicate, 10-12

PLS_INTEGER datatype, 2-14

PL/SQL

advantages, 1-20

architecture, 1-17

block structure, 1-2

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), 7-45

support for SQL, 1-20

PL/SQL blocks

anonymous, 1-2, 7-2

PL/SQL compiler

how calls are resolved, 7-27

PL/SQL engine, 1-17

in Oracle server, 1-18

in Oracle tools, 1-19

PLSQL_V2_COMPATIBILITY flag, 5-69

portability, 1-23

positional notation, 7-13

POSITIVE subtype, 2-13

POSITIVEN subtype, 2-13

AUTONOMOUS_TRANSACTION, 5-53

EXCEPTION_INIT, 6-8

RESTRICT_REFERENCES, 5-60, 7-9, 10-15

SERIALLY_REUSABLE, 5-65

precedence, operator, 2-43

precision of digits

specifying, 2-13

predefined exceptions

raising explicitly, 6-11

redeclaring, 6-10

predicates, 5-5

PRIOR collection method, 4-23

PRIOR row operator, 5-4, 5-6

private objects, 8-15

procedural abstraction, 9-2

procedures, 7-1, 7-3

specification, 7-4

productivity, 1-22

program units, 1-11

PROGRAM_ERROR exception, 6-5

propagation, exception, 6-12

pseudocolumns, 5-3

pseudoinstruction, 6-8

public objects, 8-15

purity rules, 7-9

Q

qualifiers

using subprogram names as, 2-38

when needed, 2-36, 2-40

query work areas, 5-16

quoted identifiers, 2-6

R

RAISE statement, 6-11

using in exception handler, 6-15

raise_application_error procedure, 6-9

raising an exception, 6-11

range operator, 3-10

RAW datatype, 2-16

converting, 2-30

maximum length, 2-16

read consistency, 5-42

READ ONLY parameter, 5-47

readability, 2-2, 3-19

read-only transaction, 5-47

REAL subtype, 2-14

RECORD datatype, 4-42

assigning, 4-47

comparing, 4-49

declaring, 4-43

implicit declaration, 5-13

initializing, 4-44

manipulating, 4-49

referencing, 4-45

recursion, 7-39

terminating condition, 7-39

versus iteration, 7-43

REF CURSOR datatype, 5-16

REF function, 9-32

REF type modifier, 9-28

reference datatypes, 2-11

references, external, 7-31

declaring, 9-28

dereferencing, 9-33

relational operators, 2-46

remote access indicator, 2-36

REPEAT UNTIL structure

mimicking, 3-10

REPLACE function

treatment of nulls, 2-52

reraising an exception, 6-14

reserved words, F-1

using as quoted identifier, 2-7

resolution, name, 2-37, D-1

RESTRICT_REFERENCES pragma, 7-9

using with autonomous functions, 5-60

using with dynamic SQL, 10-15

restricted rowids, 2-17

result set, 1-5, 5-8

RETURN clause

RETURN statement, 7-8

return type, 5-17, 7-26

return value, function, 7-7

RETURNING clause, 5-64, 9-36

reusability, 7-3

reusable packages, 5-65

REVERSE reserved word, 3-11

rollback segments, 5-42

ROLLBACK statement, 5-44

effect on savepoints, 5-45

rollbacks

of FORALL statement, 4-33

statement-level, 5-44

routines, external, 7-44

row locks, 5-48

row operators, 5-6

%ROWCOUNT cursor attribute, 5-36, 5-40

ROWID datatype, 2-17

ROWID pseudocolumn, 5-4

restricted, 2-17

universal, 2-17

ROWIDTOCHAR function, 5-4

ROWNUM pseudocolumn, 5-5

%ROWTYPE attribute, 2-33

ROWTYPE_MISMATCH exception, 6-6

RPC (remote procedure call), 6-12

RTRIM function

using to insert data, B-4

rules, purity, 7-9

run-time errors, 6-1

S

sample database tables

DEPT table, xxi

sample programs, A-1

savepoint names

SAVEPOINT statement, 5-45

scalar datatypes, 2-11

scale

specifying, 2-14

schemes, iteration, 3-10

scientific notation, 2-8

collection, 4-8

cursor parameter, 5-7

definition, 2-38

identifier, 2-38

loop counter, 3-13

SELECT INTO statement

SELF parameter, 9-9

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

sequential control, 3-15

serially reusable packages, 5-65

SERIALLY_REUSABLE pragma, 5-65

Server Pages, PL/SQL, 7-45

session-specific variables, 8-11

set operators, 5-6

SET TRANSACTION statement, 5-47

short-circuit evaluation, 2-45

side effects, 7-14

controlling, 7-9

significant characters, 2-5

SIGNTYPE subtype, 2-13

simple symbols, 2-3

single-line comments, 2-10

size limit, varray, 4-5

SMALLINT subtype, 2-14

snapshots, 5-42

SOME comparison operator, 5-5

spaces

where allowed, 2-2

spaghetti code, 3-15

sparse collections, 4-3

specification

SQL

comparison operators, 5-5

data manipulation statements, 5-2

pseudocolumns, 5-3

row operators, 5-6

set operators, 5-6

support in PL/SQL, 1-20

SQL cursor

SQLCODE function, 6-18

SQLERRM function, 6-18

standalone subprograms, 1-18

START WITH clause, 5-4

statement terminator, 11-15

statement-level rollbacks, 5-44

statements, PL/SQL

STDDEV aggregate function, 5-3

STEP clause

mimicking, 3-12

stepwise refinement, 1-2

STORAGE_ERROR exception, 6-6

when raised, 7-39

store tables, 4-4

stored subprograms, 1-18

string comparison semantics, B-2

string literals, 2-9

STRING subtype, 2-19

structure theorem, 3-2

stubs, 3-19, 7-3

subprograms, 7-2

advantages, 7-3

declaring, 7-10

how calls are resolved, 7-27

overloading, 7-24

packaged, 1-18, 7-11

procedure versus function, 7-6

recursive, 7-39

standalone, 1-18

SUBSCRIPT_BEYOND_COUNT exception, 6-6

SUBSCRIPT_OUTSIDE_LIMIT exception, 6-6

SUBSTR function, 6-19

subtypes, 2-13, 2-25

SUM aggregate function, 5-3

support for SQL, 5-2

symbols

syntax

definition, 11-1

diagram reading, 11-2

T

U

V

VALUE function, 9-31

VALUE_ERROR exception, 6-6

VARCHAR subtype, 2-19

VARCHAR2

variables

assigning values, 2-41

declaring, 2-30

initializing, 2-41

session-specific, 8-11

VARIANCE aggregate function, 5-3

VARRAY datatype, 4-4

varrays

size limit, 4-5

visibility

of package contents, 8-3

transaction, 5-55

versus scope, 2-38

W

WHEN clause, 3-7, 6-15

WHILE loop, 3-9

wildcards, 2-46

words, reserved, F-1

work areas, query, 5-16

Wrap Utility, C-1

input and output files, C-3

Z

ZERO_DIVIDE exception, 6-6

Oracle

Copyright © 1999 Oracle Corporation.
All Rights Reserved.