PL/SQL User's Guide and Reference
Release 8.1.5
A67842-01

Index

A B C D E F G H I J K L M N O P Q R S T U V W X Y 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-46

/ 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-45

< relational operator, 2-3, 2-45

> relational operator, 2-3, 2-45

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

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

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

^= relational operator, 2-4

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

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

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

-- single-line comment delimiter, 2-4

; statement terminator, 2-3, 11-12

- subtraction/negation operator, 2-3

A

abstraction, 7-3, 9-2

ACCESS_INTO_NULL exception, 6-5

actual parameter, 5-8

aggregate assignment, 2-33

aggregate function

treatment of nulls, 5-3

ALL comparison operator, 5-5

ALL option, 5-3

ALL row operator, 5-6

anonymous PL/SQL block, 7-2

ANY comparison operator, 5-5

apostrophe, 2-8

architecture, 1-17

assignment

aggregate, 2-33

character string, B-2

collection, 4-11

cursor variable, 5-31

assignment operator, 1-4

assignment statement

association operator, 7-13

asterisk (*) option, 5-3

asynchronous operation, 8-16

atomically null, 9-23

attribute indicator, 1-7

attributes, 1-7

object, 9-3, 9-7

AUTHID clause, 7-32

autonomous transaction, 5-52

advantages, 5-52

controlling, 5-56

AUTONOMOUS_TRANSACTION pragma, 5-53

AVG aggregate function, 5-3

B

C

call, inter-language, 7-27

call, subprogram, 7-13

carriage return, 2-3

case sensitivity

identifier, 2-5

string literal, 2-8

CHAR column

maximum width, 2-14

CHAR datatype, 2-14

CHAR semantics, B-1

CHAR_CS value, 2-29

character literal, 2-8

character set, 2-2

CHARACTER subtype, 2-14

character value

clause

BULK COLLECT, 4-34

client program, 9-2

CLOB datatype, 2-22

CLOSE statement, 5-10, 5-24

collating sequence, 2-47

collection, 4-2

assigning, 4-11

bulk binds, 4-29

comparing, 4-13

constructor, 4-8

element type, 4-5

initializing, 4-8

referencing, 4-10

collection exceptions

when raised, 4-28

collection method

applying to parameters, 4-27

collection types, 4-1

COLLECTION_IS_NULL exception, 6-5

column alias, 5-14

when needed, 2-34

restrictions, 2-10

COMMENT clause, 5-42

COMMIT statement, 5-42

comparison

of character values, B-2

of collections, 4-13

of expressions, 2-46

operators, 2-44, 5-5

compiler hint, NOCOPY, 7-17

component selector, 1-6

composite type, 2-10

compound symbol, 2-4

concatenation operator, 2-46

treatment of nulls, 2-50

concurrency, 5-40

conditional control, 3-2

constants

declaring, 2-30

constraints

where not allowed, 2-25, 7-4

constructor

collection, 4-8

context switch, 4-30

context, transaction, 5-55

control structure, 3-2

conditional, 3-2

sequential, 3-15

conventions

conversion function

when needed, 2-28

conversion, datatype, 2-27

correlated subquery, 5-11

COUNT aggregate function, 5-3

COUNT collection method, 4-22

CURRENT OF clause, 5-48

current row, 1-5

CURRVAL pseudocolumn, 5-3

cursor attribute

%BULK_ROWCOUNT, 4-36

%FOUND, 5-34, 5-38

%ISOPEN, 5-34, 5-38

%NOTFOUND, 5-35

%ROWCOUNT, 5-35, 5-39

cursor FOR loop, 5-13

passing parameters to, 5-15

cursor variable, 5-15

assignment, 5-31

declaring, 5-17

fetching from, 5-23

restrictions, 5-33

using to reduce network traffic, 5-30

using with dynamic SQL, 10-5

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 ref, 9-33

data abstraction, 9-2

data encapsulation, 1-16

data integrity, 5-40

data lock, 5-41

database changes

making permanent, 5-42

database character set, 2-19

datatypes, 2-10

DATE datatype, 2-23

dates

converting, 2-28

TO_CHAR default format, 2-28

DBMS_ALERT package, 8-16

DBMS_OUTPUT package, 8-16

DBMS_PIPE package, 8-17

DBMS_STANDARD package, 8-16

effect on transactions, 5-43

how broken, 5-43

DEC subtype, 2-13

DECIMAL subtype, 2-13

declaration

collection, 4-7

cursor variable, 5-17

subprogram, 7-9

declarative part

PL/SQL block, 1-3

DECODE function

treatment of nulls, 2-50

DEFAULT keyword, 2-30

default parameter value, 7-19

definer rights, 7-29

versus invoker rights, 7-29

DELETE collection method, 4-26

DELETE statement

RETURNING clause, 5-63

dense collection, 4-3

DEPT table, xxi

DEREF operator, 9-33

dereference, 9-33

digits of precision, 2-12

DISTINCT option, 5-3

DISTINCT row operator, 5-6

distributed transaction, 5-41

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

for record fields, 2-33

DOUBLE PRECISION subtype, 2-13

DUP_VAL_ON_INDEX exception, 6-5

dynamic FOR-loop range, 3-12

dynamic SQL, 10-2

handling nulls, 10-12

using duplicate placeholders, 10-10

using EXECUTE IMMEDIATE statement, 10-3

using the names of schema objects, 10-10

using OPEN-FOR-USING statement, 10-5

dynamic SQL, native, 10-1

E

elaboration, 2-30

element type

collection, 4-5

ELSE clause, 3-3

ELSIF clause, 3-4

encapsulation, data, 1-16

END IF reserved words, 3-3

END LOOP reserved words, 3-9

entended rowid, 2-16

error messages

maximum length, 6-18

evaluation, 2-41

short-circuit, 2-44

EXAMPBLD script, A-3

EXAMPLOD script, A-3

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

exception handler, 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

PL/SQL block, 1-3

executable part

PL/SQL block, 1-3

EXECUTE IMMEDIATE statement, 10-3

EXECUTE privilege, 7-35

execution environment, 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 cursor, 5-6

expression

how evaluated, 2-41

parentheses in, 2-42

EXTEND collection method, 4-24

extensibility, 7-3

external reference, 7-32

how resolved, 7-32

external routine, 7-28

F

FALSE value, 2-8

FETCH statement, 5-9, 5-23

fetching

across commits, 5-49

Fibonacci sequence, 7-37

field type, 4-38

FIRST collection method, 4-23

flag, PLSQL_V2_COMPATIBILITY, 5-68

FLOAT subtype, 2-13

dynamic range, 3-12

iteration scheme, 3-10

loop counter, 3-10

FOR loop, cursor, 5-13

FOR UPDATE clause, 5-8

restriction on, 5-19

when to use, 5-47

FORALL statement, 4-32

using with BULK COLLECT clause, 4-35

formal parameter, 5-8

format

packaged procedure, 7-10

format mask

when needed, 2-28

forward declaration, 7-9

when needed, 7-9, 7-40

forward reference, 2-35

forward type definition, 9-29

%FOUND cursor attribute, 5-34, 5-38

function, 7-1, 7-5

RETURN clause, 7-6

specification, 7-6

G

GOTO statement, 3-15

restriction, 6-17

GROUP BY clause, 5-3

GROUPING aggregate function, 5-3

H

handler, 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-48

hidden declaration, 8-3

hiding, information, 1-15

hint, NOCOPY, 7-17

host array

bulk binds, 4-36

host variable, 5-16

hypertext markup language (HTML), 8-17

hypertext transfer protocol (HTTP), 8-17

I

identifier

maximum length, 2-5

scope rules, 2-37

IF statement, 3-2

ELSE clause, 3-3

ELSIF clause, 3-4

THEN clause, 3-3

implicit cursor, 5-11

attribute, 5-38

implicit datatype conversion, 2-27

effect on performance, 5-67

implicit declaration

cursor FOR loop record, 5-13

FOR loop counter, 3-13

IN comparison operator, 2-46, 5-5

IN OUT parameter mode, 7-16

IN parameter mode, 7-14

incomplete object type, 9-29

index, cursor FOR loop, 5-13

index-by table

versus nested table, 4-3

infinite loop, 3-6

information hiding, 1-15, 8-4

initialization

collection, 4-8

using DEFAULT, 2-30

when required, 2-31

INSERT statement

RETURNING clause, 5-63

INT subtype, 2-13

INTEGER subtype, 2-13

inter-language call, 7-27

interoperability, cursor, 5-16

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-45, 5-6

%ISOPEN cursor attribute, 5-34, 5-38

iteration

versus recursion, 7-41

iterative control, 3-6

J

L

label

GOTO statement, 3-15

large object (LOB) datatypes, 2-21

LAST collection method, 4-23

LEVEL pseudocolumn, 5-4

lexical unit, 2-2

LIKE comparison operator, 2-45, 5-6

LIMIT collection method, 4-22

LOB (large object) datatypes, 2-21

lob locator, 2-21

local subprogram, 1-18

locator variable, 6-22

overriding, 5-47

using FOR UPDATE clause, 5-47

LOCK TABLE statement, 5-48

logical rowid, 2-16

LOGIN_DENIED exception, 6-5

LONG datatype, 2-15

maximum length, 2-15

restrictions, 2-15

LONG RAW datatype, 2-15

converting, 2-29

maximum length, 2-15

loop

LOOP statement, 3-6

M

maintainability, 7-3

map method, 9-10

MAX aggregate function, 5-3

maximum length

CHAR value, 2-14

identifier, 2-5

LONG RAW value, 2-15

LONG value, 2-15

NCHAR value, 2-19

NVARCHAR2 value, 2-20

Oracle error message, 6-18

RAW value, 2-15

VARCHAR2 value, 2-18

maximum precision, 2-12

maximum size

membership test, 2-46

method

object, 9-3, 9-8

method calls

method, collection, 4-21

MIN aggregate function, 5-3

MINUS set operator, 5-6

mixed notation, 7-13

mode, parameter

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

multi-line comment, 2-9

mutual recursion, 7-40

N

name

qualified, 2-35

savepoint, 5-44

name resolution, 2-36, D-1

named notation, 7-13

naming conventions, 2-35

national character set, 2-19

National Language Support (NLS), 2-19

native dynamic SQL. See dynamic SQL

NATURAL subtype, 2-12

NATURALN subtype, 2-12

NCHAR datatype, 2-19

NCHAR_CS value, 2-29

NCLOB datatype, 2-22

nested table

manipulating, 4-13

versus index-by table, 4-3

nesting

network traffic

NEXT collection method, 4-23

NEXTVAL pseudocolumn, 5-3

NLS (National Language Support), 2-19

NLS datatype, 2-19

NLS_CHARSET_ID function, 2-29

NLS_CHARSET_NAME function, 2-29

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

NOT NULL constraint

effect on %TYPE declaration, 2-32

effect on performance, 5-66

restriction, 5-7, 7-4

using in collection declaration, 4-6

using in field declaration, 4-40

using in variable declaration, 2-31

NOT_LOGGED_ON exception, 6-5

notation

positional versus named, 7-13

%NOTFOUND cursor attribute, 5-35

NOWAIT parameter, 5-47

NVARCHAR2 datatype, 2-20

NVL function

treatment of nulls, 2-50

null handling, 2-48

in dynamic SQL, 10-12

NULL statement, 3-19

using in a procedure, 7-4

NUMBER datatype, 2-12

numeric literal, 2-7

NUMERIC subtype, 2-13

O

declaring, 9-22

initializing, 9-23

manipulating, 9-30

object attribute, 9-3, 9-7

accessing, 9-24

allowed datatypes, 9-7

maximum number, 9-7

object constructor

passing parameters to, 9-26

object method, 9-3, 9-8

object table, 9-30

object type, 9-1, 9-3

advantages, 9-5

object-oriented programming, 9-1

OPEN statement, 5-8

OPEN-FOR statement, 5-19

OPEN-FOR-USING statement, 10-5

operator

comparison, 2-44

concatenation, 2-46

precedence, 2-42

relational, 2-45

OR keyword, 6-16

Oracle, Trusted, 2-10

order method, 9-10

order of evaluation, 2-42, 2-43

OTHERS exception handler, 6-2, 6-15

OUT parameter mode, 7-14

overloading, 7-23

object method, 9-10

packaged subprogram, 8-14

restrictions, 7-24

using subtypes, 7-24

P

package, 8-1, 8-2

advantages, 8-4

initializing, 8-8

private versus public objects, 8-14

referencing, 8-6

serially reusable, 5-64

specification, 8-2

package, product-specific, 8-16

packaged cursor, 5-12

packaged subprogram, 1-18, 7-10

overloading, 8-14

parameter

actual versus formal, 7-12

default values, 7-19

parameter aliasing, 7-21

parameter passing

by reference, 7-21

in dynamic SQL, 10-9

parentheses, 2-42

pattern matching, 2-45

performance, 1-21

improving, 5-60

physical rowid, 2-16

placeholder, 10-2

duplicate, 10-10

PLS_INTEGER datatype, 2-13

PL/SQL

advantages, 1-20

architecture, 1-17

block structure, 1-2

execution environments, 1-17

performance, 1-21

portability, 1-22

procedural aspects, 1-2

reserved words, E-1

sample programs, A-1

support for SQL, 1-20

PL/SQL block

anonymous, 1-2, 7-2

maximum size, 5-50

PL/SQL compiler

how calls are resolved, 7-25

PL/SQL engine, 1-17

in Oracle Server, 1-18

in Oracle tools, 1-19

PL/SQL syntax, 11-1

PL/SQL Wrapper, C-1

input and output files, C-3

PLSQL_V2_COMPATIBILITY flag, 5-68

portability, 1-22

positional notation, 7-13

POSITIVE subtype, 2-12

POSITIVEN subtype, 2-12

AUTONOMOUS_TRANSACTION, 5-53

EXCEPTION_INIT, 6-8

RESTRICT_REFERENCES, 5-59, 7-7, 10-13

SERIALLY_REUSABLE, 5-64

precedence, operator, 2-42

precision of digits

specifying, 2-12

predefined exception

raising explicitly, 6-11

redeclaring, 6-10

PRIOR collection method, 4-23

PRIOR row operator, 5-4, 5-6

private object, 8-14

procedural abstraction, 9-2

procedure, 7-1, 7-3

specification, 7-4

productivity, 1-22

program unit, 1-11

PROGRAM_ERROR exception, 6-5

propagation, exception, 6-12

pseudocolumn, 5-3

pseudoinstruction, 6-8

public object, 8-14

purity rules, 7-7

Q

qualifier

using subprogram name as, 2-37

when needed, 2-35, 2-39

query work area, 5-16

quoted identifier, 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-15

converting, 2-29

maximum length, 2-15

read consistency, 5-41

READ ONLY parameter, 5-46

readability, 2-2, 3-19

read-only transaction, 5-46

REAL subtype, 2-13

assigning, 4-42

comparing, 4-44

declaring, 4-39

implicit declaration, 5-13

initializing, 4-40

manipulating, 4-44

referencing, 4-40

RECORD datatype, 4-37

recursion, 7-37

terminating condition, 7-38

versus iteration, 7-41

declaring, 9-28

dereferencing, 9-33

REF CURSOR datatype, 5-16

REF operator, 9-32

REF type modifier, 9-28

reference type, 2-10

reference, external, 7-32

relational operator, 2-45

remote access indicator, 2-35

REPEAT UNTIL structure

mimicking, 3-10

REPLACE function

treatment of nulls, 2-51

reraising an exception, 6-14

reserved words, E-1

using as quoted identifier, 2-6

resolution, name, 2-36, D-1

RESTRICT_REFERENCES pragma, 5-59, 7-7, 10-13

restricted rowid, 2-16

result set, 1-5, 5-8

result value, function, 7-6

RETURN clause

RETURN statement, 7-8

return type, 5-17, 7-25

RETURNING clause, 5-63, 9-36

reusability, 7-3

reusable packages, 5-64

REVERSE reserved word, 3-11

rollback

of FORALL statement, 4-33

statement-level, 5-43

rollback segment, 5-41

ROLLBACK statement, 5-43

effect on savepoints, 5-44

routine, external, 7-28

row operator, 5-6

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

restricted, 2-16

universal, 2-16

ROWID datatype, 2-16

ROWID pseudocolumn, 5-4

ROWIDTOCHAR function, 5-4

ROWNUM pseudocolumn, 5-5

%ROWTYPE attribute, 2-32

ROWTYPE_MISMATCH exception, 6-6

RPC (remote procedure call), 6-12

RTRIM function

using to insert data, B-4

rules, purity, 7-7

runtime error, 6-1

S

sample database table

DEPT table, xxi

sample programs, A-1

savepoint name

SAVEPOINT statement, 5-44

scalar type, 2-10

scale

specifying, 2-13

scheme, iteration, 3-10

scientific notation, 2-7

collection, 4-8

cursor parameter, 5-7

definition, 2-37

identifier, 2-37

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

sequential control, 3-15

serially reusable package, 5-64

SERIALLY_REUSABLE pragma, 5-64

server

integration with PL/SQL, 1-23

session-specific variables, 8-11

set operator, 5-6

SET TRANSACTION statement, 5-46

short-circuit evaluation, 2-44

side effects, 7-14

controlling, 7-7

significant characters, 2-5

SIGNTYPE subtype, 2-12

simple symbol, 2-3

single-line comment, 2-9

size constraint, subtype, 2-25

size limit, varray, 4-5

SMALLINT subtype, 2-13

SOME comparison operator, 5-5

spaces

where allowed, 2-2

spaghetti code, 3-15

sparse collection, 4-3

specification

SQL

comparison operators, 5-5

data manipulation statements, 5-2

pseudocolumn, 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 subprogram, 1-18

START WITH clause, 5-4

statement

statement terminator, 11-12

statement-level rollback, 5-43

STDDEV aggregate function, 5-3

STEP clause

mimicking, 3-12

stepwise refinement, 1-2

STORAGE_ERROR exception, 6-6

when raised, 7-38

store table, 4-4

stored subprogram, 1-18, 7-11

string comparison semantics, B-2

string literal, 2-8

STRING subtype, 2-18

structure theorem, 3-2

stub, 3-19, 7-3

subprogram, 7-2

advantages, 7-3

how calls are resolved, 7-25

overloading, 7-23

packaged, 1-18, 7-10

procedure versus function, 7-5

recursive, 7-38

standalone, 1-18

stored, 1-18, 7-11

SUBSCRIPT_BEYOND_COUNT exception, 6-6

SUBSCRIPT_OUTSIDE_LIMIT exception, 6-6

SUBSTR function, 6-19

subtype, 2-12, 2-24

SUM aggregate function, 5-3

support for SQL, 5-2

symbol

syntax definition, 11-1

syntax diagram, reading, 11-2

T

U

V

VALUE operator, 9-31

VALUE_ERROR exception, 6-6

VARCHAR subtype, 2-18

VARCHAR2 datatype, 2-18

VARCHAR2 semantics, B-1

variables

assigning values, 2-40

declaring, 2-29

initializing, 2-40

session-specific, 8-11

VARIANCE aggregate function, 5-3

varray

size limit, 4-5

VARRAY datatype, 4-4

visibility

of package contents, 8-3

transaction, 5-55

versus scope, 2-37

W

WHEN clause, 3-7, 6-15

WHILE loop, 3-9

words, reserved, E-1

work area, query, 5-16

Wrapper utility, C-1

Z

ZERO_DIVIDE exception, 6-6

Copyright © 1999 Oracle Corporation.
All Rights Reserved.