PL/SQL User's Guide and Reference
Release 8.0
A58236-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-5

=> association operator, 2-5, 7-12

% attribute indicator, 1-7, 2-3

' character string delimiter, 2-3

. component selector, 1-6, 2-3

|| concatenation operator, 2-5, 2-53

/ division operator, 2-3

** exponentiation operator, 2-5

( expression or list delimiter, 2-3

) expression or list delimiter, 2-3

: host variable indicator, 2-3

. item separator, 2-3

<< label delimiter, 2-5

>> label delimiter, 2-5

/* multi-line comment delimiter, 2-5

*/ multi-line comment delimiter, 2-5

* multiplication operator, 2-3

" quoted identifier delimiter, 2-3

.. range operator, 2-5, 3-10

= relational operator, 2-3, 2-52

< relational operator, 2-3, 2-52

> relational operator, 2-3, 2-52

<> relational operator, 2-5, 2-52

!= relational operator, 2-5, 2-52

~= relational operator, 2-5, 2-52

<= relational operator, 2-5, 2-52

>= relational operator, 2-5, 2-52

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

-- single-line comment delimiter, 2-5

; statement terminator, 2-3, 11-12

- subtraction/negation operator, 2-3

A

B

C

call, subprogram, 7-12

callback, 10-22

restrictions, 10-23

CALLING STANDARD clause, 10-4

carriage return, 2-3

case sensitivity

identifier, 2-7

string literal, 2-11

case, lower, xxi

case, upper, xxi

CHAR column

maximum width, 2-18

CHAR datatype, 2-18

CHAR semantics, C-1

CHAR_CS value, 2-33

character literal, 2-10

character set, 2-2

CHARACTER subtype, 2-18

character value

CHARSETFORM property, 10-15

CHARSETID property, 10-15

client program, 9-2

CLOB datatype, 2-24

CLOSE statement, 5-13, 5-27

collating sequence, 2-55

collection, 4-2

assigning, 4-13

comparing, 4-14

constructor, 4-10

element type, 4-5

initializing, 4-10

referencing, 4-12

collection exceptions

when raised, 4-30

collection method

applying to parameters, 4-29

collection types, 4-1

COLLECTION_IS_NULL exception, 6-5

column alias, 5-17

when needed, 2-38

restrictions, 2-13

COMMENT clause, 5-47

COMMIT statement, 5-47

comparison

of character values, C-2

of collections, 4-14

of expressions, 2-53

operators, 2-51, 5-6

compilation

using the PL/SQL Wrapper, D-1

component selector, 1-6

composite type, 2-13

compound symbol, 2-5

concatenation operator, 2-53

treatment of nulls, 2-59

concurrency, 5-45

conditional control, 3-2

constant

declaring, 2-34

constraint

where not allowed, 2-27, 7-4

constructor

collection, 4-10

control structure, 3-2

conditional, 3-2

sequential, 3-15

conventions

conversion function

when needed, 2-31

conversion, datatype, 2-29

correlated subquery, 5-14

COUNT collection method, 4-24

COUNT group function, 5-3

CREATE LIBRARY statement, 10-3

CURRENT OF clause, 5-55

current row, 1-5

CURRVAL pseudocolumn, 5-4

cursor, 1-5, 5-9

declaring, 5-10

fetching from, 5-12

parameterized, 5-11

RETURN clause, 5-15

scope rules, 5-10

cursor attribute

%FOUND, 5-38, 5-43

%ISOPEN, 5-38, 5-43

%NOTFOUND, 5-39

%ROWCOUNT, 5-39, 5-44

cursor FOR loop, 5-16

passing parameters to, 5-18

cursor variable, 5-18

assignment, 5-34

declaring, 5-20

fetching from, 5-26

restrictions, 5-37

using to reduce network traffic, 5-33

CURSOR_ALREADY_OPEN exception, 6-5

D

dangling ref, 9-38

data abstraction, 9-2

data encapsulation, 1-16

data integrity, 5-45

data lock, 5-46

database changes

making permanent, 5-47

database character set, 2-22

date

converting, 2-32

TO_CHAR default format, 2-32

DATE datatype, 2-25

DBMS_ALERT package, 8-18

DBMS_OUTPUT package, 8-16

DBMS_PIPE package, 8-17

DBMS_SQL package, 5-8, 8-18

array interface, 5-61

DBMS_STANDARD package, 8-16

DDL support, 5-7

effect on transactions, 5-49

how broken, 5-49

DEBUG_EXTPROC package, 10-25

DEC subtype, 2-17

DECIMAL subtype, 2-17

declaration

collection, 4-8

cursor variable, 5-20

subprogram, 7-8

declarative part

PL/SQL block, 1-3

DECODE function

treatment of nulls, 2-59

DEFAULT keyword, 2-34

default parameter value, 7-15

default pragma, 9-13

DELETE collection method, 4-28

DELETE statement

RETURNING clause, 5-59

demo, external procedure, 10-25

dense collection, 4-3

DEPT table, xxii

DEREF operator, 9-38

dereference, 9-38

digits of precision, 2-15

DISTINCT option, 5-3

DISTINCT row operator, 5-7

distributed transaction, 5-46

DLL (dynamic link library), 10-3

dot notation, 1-6, 1-7

for collection methods, 4-23

for global variables, 3-13

for object attributes, 9-26

for object methods, 9-28

for package contents, 8-6

for record fields, 2-37

DOUBLE PRECISION subtype, 2-17

DUP_VAL_ON_INDEX exception, 6-5

dynamic FOR-loop range, 3-12

dynamic link library (DLL), 10-3

dynamic SQL support, 5-7

E

elaboration, 2-34

element type

collection, 4-5

ELSE clause, 3-3

ELSIF clause, 3-4

EMP table, xxii

encapsulation, data, 1-16

END IF reserved words, 3-3

END LOOP reserved words, 3-9

Entry SQL support, 5-7

error message

maximum length, 6-19

evaluation, 2-47

short-circuit, 2-51

EXAMPBLD script, B-3

EXAMPLOD script, B-7

predefined, 6-4

propagation, 6-13

raised in declaration, 6-17

raised in handler, 6-18

raising with RAISE statement, 6-12

reraising, 6-15

scope rules, 6-7

user-defined, 6-6

WHEN clause, 6-16

exception handler, 6-16

branching from, 6-18

OTHERS handler, 6-2

using RAISE statement in, 6-16

using SQLCODE function in, 6-19

using SQLERRM function in, 6-19

EXCEPTION_INIT pragma, 6-9

using with raise_application_error, 6-11

exception-handling part

PL/SQL block, 1-3

executable part

PL/SQL block, 1-3

execution environment, 1-18

EXISTS collection method, 4-24

EXISTS comparison operator, 5-6

EXIT statement, 3-6, 3-14

WHEN clause, 3-7

where allowed, 3-6

explicit cursor, 5-9

expression

how evaluated, 2-47

parentheses in, 2-48

EXTEND collection method, 4-26

extensibility, 7-3

EXTERNAL clause, 10-3

components, 10-4

external procedure, 10-2

DEBUG_EXTPROC package, 10-25

debugging, 10-25

demo program, 10-25

environment variables, 10-8

guidelines, 10-26

how PL/SQL calls, 10-7

maximum number of parameters, 10-27

passing parameters to, 10-9

registering, 10-3

restrictions, 10-26

specifying datatypes, 10-9

specifying properties, 10-12

extproc process, 10-7

F

FALSE value, 2-11

features, new, A-1

FETCH statement, 5-12, 5-26

fetching across commits, 5-56

Fibonacci sequence, 7-23

field type, 4-31

FIRST collection method, 4-25

flag, PLSQL_V2_COMPATIBILITY, 5-66

FLOAT subtype, 2-17

dynamic range, 3-12

iteration scheme, 3-10

loop counter, 3-10

FOR loop, cursor, 5-16

FOR UPDATE clause, 5-11

restriction on, 5-22

when to use, 5-53

formal parameter, 5-11

format

packaged procedure, 7-9

format mask

when needed, 2-32

forward declaration, 7-8

when needed, 7-8, 7-26

forward reference, 2-39

forward type definition, 9-33

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

function, 7-1, 7-5

RETURN clause, 7-5

specification, 7-5

G

GLB group function, 5-3

GOTO statement, 3-15

restriction, 6-18

GROUP BY clause, 5-3

group function

treatment of nulls, 5-3

H

handler, exception, 6-2

handling exceptions, 6-1

raised in declaration, 6-17

raised in handler, 6-18

using OTHERS handler, 6-16

handling of nulls, 2-57

hidden declaration, 8-2

hiding, information, 1-16

host variable, 5-19

hypertext markup language (HTML), 8-17

hypertext transfer protocol (HTTP), 8-17

I

identifier

maximum length, 2-7

scope rules, 2-43

IF statement, 3-2

ELSE clause, 3-3

ELSIF clause, 3-4

THEN clause, 3-3

implicit cursor, 5-14

attribute, 5-43

implicit datatype conversion, 2-31

effect on performance, 5-63

implicit declaration

cursor FOR loop record, 5-16

FOR loop counter, 3-13

IN comparison operator, 2-53, 5-6

IN OUT parameter mode, 7-14

IN parameter mode, 7-13

incomplete object type, 9-33

index, cursor FOR loop, 5-16

index-by table, 4-3

indicator, 10-14

INDICATOR property, 10-14

infinite loop, 3-6

information hiding, 1-16, 8-4

initialization

collection, 4-10

using DEFAULT, 2-34

when required, 2-35

INSERT statement

RETURNING clause, 5-59

INT subtype, 2-17

INTEGER subtype, 2-17

interoperability, cursor, 5-19

INTERSECT set operator, 5-6

INTO clause, 5-26

INTO list, 5-12

INVALID_CURSOR exception, 6-5

INVALID_NUMBER exception, 6-5

IS DANGLING predicate, 9-38

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

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

iteration

versus recursion, 7-27

iterative control, 3-6

J

L

label

GOTO statement, 3-15

LANGUAGE clause, 10-4

large object (LOB) datatypes, 2-23

LAST collection method, 4-25

LENGTH property, 10-14

LEVEL pseudocolumn, 5-5

lexical unit, 2-2

LIBRARY clause, 10-4

library, alias, 10-3

LIKE comparison operator, 2-52, 5-6

LIMIT collection method, 4-24

character, 2-10

LOB (large object) datatypes, 2-23

local subprogram, 1-19

overriding, 5-53

using FOR UPDATE clause, 5-53

LOCK TABLE statement, 5-55

LOGIN_DENIED exception, 6-5

LONG datatype, 2-19

maximum length, 2-19

restrictions, 2-19

LONG RAW datatype, 2-19

converting, 2-33

maximum length, 2-19

loop

LOOP statement, 3-6

LUB group function, 5-3

M

maintainability, 7-3

map method, 9-10

MAX group function, 5-3

maximum length

CHAR value, 2-18

identifier, 2-7

LONG RAW value, 2-19

LONG value, 2-19

NCHAR value, 2-22

NVARCHAR2 value, 2-23

Oracle error message, 6-19

RAW value, 2-19

VARCHAR2 value, 2-20

maximum precision, 2-15

maximum size

MAXLEN property, 10-14

membership test, 2-53

method

object, 9-3, 9-8

method calls

method, collection, 4-23

MIN group function, 5-3

MINUS set operator, 5-6

mixed notation, 7-12

MLSLABEL datatype, 2-26

mode, parameter

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

multi-line comment, 2-12

mutual recursion, 7-26

N

name

qualified, 2-40

savepoint, 5-50

NAME clause, 10-4

name resolution, 2-41, E-1

named notation, 7-12

naming conventions, 2-40

national character set, 2-22

National Language Support (NLS), 2-21

NATURAL subtype, 2-15

NATURALN subtype, 2-15

NCHAR datatype, 2-22

NCHAR_CS value, 2-33

NCLOB datatype, 2-25

nested table, 4-2

manipulating, 4-15

versus index-by table, 4-3

nesting

network traffic

new features, A-1

NEXT collection method, 4-25

NEXTVAL pseudocolumn, 5-4

NLS (National Language Support), 2-21

NLS datatype, 2-21

NLS_CHARSET_ID function, 2-33

NLS_CHARSET_NAME function, 2-33

NO_DATA_FOUND exception, 6-5

non-blank-padding semantics, C-3

NOT logical operator

treatment of nulls, 2-58

NOT NULL constraint

effect on %TYPE declaration, 2-36

effect on performance, 5-62

restriction, 5-10, 7-3

using in collection declaration, 4-8

using in field declaration, 4-33

using in variable declaration, 2-35

NOT_LOGGED_ON exception, 6-6

notation

positional versus named, 7-12

%NOTFOUND cursor attribute, 5-39

NOWAIT parameter, 5-53

NVARCHAR2 datatype, 2-23

NVL function

treatment of nulls, 2-59

null handling, 2-57

NULL statement, 3-19

using in a procedure, 7-4

NUMBER datatype, 2-15

numeric literal, 2-9

NUMERIC subtype, 2-17

O

declaring, 9-24

initializing, 9-25

manipulating, 9-34

object attribute, 9-3, 9-7

accessing, 9-26

allowed datatypes, 9-7

maximum number, 9-7

object constructor

passing parameters to, 9-28

object method, 9-3, 9-8

object table, 9-34

object type, 9-1, 9-3

advantages, 9-5

object-oriented programming, 9-1

OPEN statement, 5-11

OPEN-FOR statement, 5-22

operator

comparison, 2-51

concatenation, 2-53

precedence, 2-48

relational, 2-52

OR keyword, 6-17

Oracle, Trusted, 2-13

order method, 9-10

order of evaluation, 2-48, 2-49

OTHERS exception handler, 6-2, 6-16

OUT parameter mode, 7-13

overloading, 7-18

object method, 9-10

packaged subprogram, 8-14

restrictions, 7-19

using subtypes, 7-20

P

package, 8-1, 8-2

advantages, 8-4

DEBUG_EXTPROC, 10-25

initializing, 8-8

private versus public objects, 8-14

referencing, 8-6

serially reusable, 5-59

specification, 8-2

package, product-specific, 8-16

packaged cursor, 5-15

packaged subprogram, 1-19, 7-9

overloading, 8-14

parameter

actual versus formal, 7-11

default values, 7-15

parameter passing

by reference, 7-17

PARAMETERS clause, 10-5, 10-12

parentheses, 2-48

Pascal Calling Standard, 10-4

pattern matching, 2-52

performance, 1-22

improving, 5-57

PL/SQL

advantages, 1-21

architecture, 1-18

block structure, 1-2

execution environments, 1-18

new features, A-1

performance, 1-22

portability, 1-23

procedural aspects, 1-2

reserved words, F-1

sample programs, B-1

support for SQL, 1-21

PL/SQL block

anonymous, 1-2, 7-2

maximum size, 5-64

PL/SQL compiler

how calls are resolved, 7-21

how it works, 5-8

how references are resolved, 5-7

PL/SQL engine, 1-18

in Oracle Server, 1-19

in Oracle tools, 1-20

PL/SQL syntax, 11-1

PL/SQL Wrapper, D-1

input and output files, D-3

PLS_INTEGER datatype, 2-17

PLSQL_V2_COMPATIBILITY flag, 5-66

portability, 1-23

positional notation, 7-12

POSITIVE subtype, 2-15

POSITIVEN subtype, 2-15

EXCEPTION_INIT, 6-9

RESTRICT_REFERENCES, 7-6, 9-12

SERIALLY_REUSABLE, 5-59

precedence, operator, 2-48

precision of digits

specifying, 2-15

predefined exception

raising explicitly, 6-12

redeclaring, 6-11

PRIOR collection method, 4-25

PRIOR row operator, 5-5, 5-7

private object, 8-14

procedural abstraction, 9-2

procedure, 7-1, 7-3

specification, 7-4

productivity, 1-23

program unit, 1-11

PROGRAM_ERROR exception, 6-6

propagation, exception, 6-13

property

CHARSETFORM, 10-15

CHARSETID, 10-15

INDICATOR, 10-14

pseudocolumn, 5-4

pseudoinstruction, 6-9

public object, 8-14

purity level, 9-12

Q

qualifier

using subprogram name as, 2-43

when needed, 2-40, 2-45

query work area, 5-19

quoted identifier, 2-8

R

RAISE statement, 6-12

using in exception handler, 6-16

raise_application_error procedure, 6-10

raising an exception, 6-12

range operator, 3-10

RAW datatype, 2-19

converting, 2-33

maximum length, 2-19

read consistency, 5-46

READ ONLY parameter, 5-52

readability, 2-2, 3-19

read-only transaction, 5-52

REAL subtype, 2-17

assigning, 4-36

comparing, 4-38

declaring, 4-32

implicit declaration, 5-16

initializing, 4-33

manipulating, 4-38

referencing, 4-34

RECORD datatype, 4-31

recursion, 7-23

terminating condition, 7-24

versus iteration, 7-27

declaring, 9-31

dereferencing, 9-38

REF CURSOR datatype, 5-19

REF operator, 9-36

REF type modifier, 9-31

reference type, 2-13

relational operator, 2-52

remote access indicator, 2-40

REPEAT UNTIL structure

mimicking, 3-10

REPLACE function

treatment of nulls, 2-60

reraising an exception, 6-15

reserved words, F-1

using as quoted identifier, 2-8

resolution, name, 2-41, E-1

RESTRICT_REFERENCES pragma, 9-12

result set, 1-5, 5-11

result value, function, 7-5

RETURN clause

RETURN statement, 7-7

return type, 5-20, 7-21

RETURNING clause, 5-59, 9-42

reusability, 7-3

reusable packages, 5-59

REVERSE reserved word, 3-11

rollback

statement-level, 5-49

rollback segment, 5-46

ROLLBACK statement, 5-49

effect on savepoints, 5-50

routine

row operator, 5-7

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

ROWID datatype, 2-20

ROWID pseudocolumn, 5-5

ROWIDTOCHAR function, 5-5

ROWNUM pseudocolumn, 5-5

%ROWTYPE attribute, 2-36

ROWTYPE_MISMATCH exception, 6-6

RPC (remote procedure call), 6-13

RTRIM function

using to insert data, C-4

runtime error, 6-1

S

sample database table

DEPT table, xxii

EMP table, xxii

sample programs, B-1

savepoint name

SAVEPOINT statement, 5-50

scalar type, 2-13

scale

specifying, 2-17

scheme, iteration, 3-10

scientific notation, 2-9

collection, 4-10

cursor parameter, 5-10

definition, 2-43

identifier, 2-43

loop counter, 3-13

SELECT INTO statement

SELF parameter, 9-8

semantics

assignment, C-2

blank-padding, C-3

CHAR versus VARCHAR2, C-1

non-blank-padding, C-3

string comparison, C-2

sequential control, 3-15

serially reusable package, 5-59

SERIALLY_REUSABLE pragma, 5-59

server

integration with PL/SQL, 1-23

service routine, 10-17

examples, 10-17

session-specific variables, 8-11

set operator, 5-6

SET TRANSACTION statement, 5-52

short-circuit evaluation, 2-51

side effects, 7-13, 9-12

significant characters, 2-7

SIGNTYPE subtype, 2-15

simple symbol, 2-3

single-line comment, 2-12

size constraint, subtype, 2-27

size limit, varray, 4-5

SMALLINT subtype, 2-17

SOME comparison operator, 5-6

spaces

where allowed, 2-2

spaghetti code, 3-15

sparse collection, 4-3

specification

SQL

comparison operators, 5-6

data manipulation statements, 5-2

pseudocolumn, 5-4

row operators, 5-7

set operators, 5-6

support in PL/SQL, 1-21

SQL cursor

SQL standards conformance, 5-7

SQL92 conformance, 5-7

SQLCODE function, 6-19

SQLERRM function, 6-19

standalone subprogram, 1-19

START WITH clause, 5-5

statement

statement terminator, 11-12

statement-level rollback, 5-49

STDDEV group function, 5-3

STEP clause

mimicking, 3-12

stepwise refinement, 1-2

STORAGE_ERROR exception, 6-6

when raised, 7-24

store table, 4-5

stored subprogram, 1-19, 7-10

string comparison semantics, C-2

string literal, 2-11

STRING subtype, 2-21

structure theorem, 3-2

stub, 3-19, 7-3

subprogram, 7-2

advantages, 7-3

how calls are resolved, 7-21

overloading, 7-18

packaged, 1-19, 7-9

procedure versus function, 7-5

recursive, 7-24

standalone, 1-19

stored, 1-19, 7-10

SUBSCRIPT_BEYOND_COUNT exception, 6-6

SUBSCRIPT_OUTSIDE_LIMIT exception, 6-6

SUBSTR function, 6-21

subtype, 2-15, 2-26

SUM group function, 5-3

support for SQL, 5-2

symbol

syntax definition, 11-1

syntax diagram, reading, 11-2

T

U

unary operator, 2-47

underscore, 2-6

unhandled exception, 6-13, 6-21

uninitialized object

how treated, 9-26

UNION ALL set operator, 5-6

UNION set operator, 5-6

UPDATE statement

RETURNING clause, 5-59

URL (universal resource locator), 8-17

user session, 5-45

user-defined exception, 6-6

user-defined record, 4-31

declaring, 4-32

referencing, 4-34

user-defined subtype, 2-26

UTL_FILE package, 8-17

UTL_HTTP package, 8-17

V

VALUE operator, 9-35

VALUE_ERROR exception, 6-6

VARCHAR subtype, 2-21

VARCHAR2 datatype, 2-20

VARCHAR2 semantics, C-1

variable

assigning values, 2-46

declaring, 2-33

initializing, 2-46

session-specific, 8-11

VARIANCE group function, 5-3

varray

size limit, 4-5

VARRAY datatype, 4-4

visibility

of package contents, 8-2

versus scope, 2-43

W

WHEN clause, 3-7, 6-16

WHILE loop, 3-9

WITH CONTEXT clause, 10-5, 10-16

words, reserved, F-1

work area, query, 5-19

Z

ZERO_DIVIDE exception, 6-6

Copyright © 1997 Oracle Corporation.
All Rights Reserved.