-- Index

PL/SQL User's Guide and Reference
Release 8.0
A54654_01

A

abstraction, 7-3, 9-2

ACCESS_INTO_NULL exception, 6-5

actual parameter, 5-12

addition operator, 2-3

aggregate assignment, 2-32

alias library, 10-2

ALL comparison operator, 5-6

ALL option, 5-3

ALL row operator, 5-7

anonymous PL/SQL block, 7-2

ANY comparison operator, 5-6

apostrophe, 2-8

architecture, 1-16

assignment

aggregate, 2-32

character string, C-2

collection, 4-10

cursor variable, 5-32

assignment operator, 1-4, 2-4

assignment statement

association operator, 2-4, 7-12

asterisk (*) option, 5-3

asynchronous operation, 8-17

atomically null, 9-22

object, 9-3, 9-7

attribute indicator, 2-3

AVG group function, 5-3

B

C

call, subprogram, 7-12

callback, 10-19

restrictions, 10-20

CALLING STANDARD clause, 10-4

carriage return, 2-3

case sensitivity

identifier, 2-5

string literals, 2-8

case, lower, xv

case, upper, xv

CHAR column

maximum width, 2-13

CHAR datatype, 2-13

CHAR semantics, C-1

CHAR_CS value, 2-27

character literal, 2-7

character set, 2-2

character string delimiter, 2-3

CHARACTER subtype, 2-14

character value

CHARSETFORM property, 10-13

CHARSETID property, 10-13

client program, 9-2

CLOB datatype, 2-20

CLOSE statement, 5-13, 5-26

collating sequence, 2-45

collection, 4-2

assigning, 4-10

comparing, 4-11

constructor, 4-7

element type, 4-5

initializing, 4-7

referencing, 4-9

collection exceptions

when raised, 4-24

collection method

applying to parameters, 4-23

collection types, 4-1

COLLECTION_IS_NULL exception, 6-5

column alias, 5-17

when needed, 2-33

column, ROWLABEL, 5-6

restrictions, 2-9

COMMENT clause, 5-44

COMMIT statement, 5-43

comparison

of character values, C-2

of collections, 4-11

of expressions, 2-45

operators, 2-43, 5-6

compilation

using the PL/SQL Wrapper, D-1

component selector, 2-3

composite type, 2-10

concatenation operator, 2-4, 2-44

treatment of nulls, 2-48

concurrency, 5-42

conditional control, 3-2

constant

declaring, 2-28

constraint

where not allowed, 2-23, 7-4

constructor

collection, 4-7

control structure, 3-2

conditional, 3-2

sequential, 3-13

conventions

conversion function

when needed, 2-26

conversion, datatype, 2-25

correlated subquery, 5-14

COUNT collection method, 4-19

COUNT group function, 5-3

CREATE LIBRARY statement, 10-2

CURRENT OF clause, 5-49

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-36, 5-40

%ISOPEN, 5-36, 5-40

%NOTFOUND, 5-37

%ROWCOUNT, 5-37, 5-40

cursor FOR loop, 5-16

passing parameters to, 5-17

cursor variable, 5-18

assignment, 5-32

declaring, 5-20

fetching from, 5-25

restrictions, 5-35

using to reduce network traffic, 5-31

CURSOR_ALREADY_OPEN exception, 6-5

D

dangling ref, 9-31

data abstraction, 9-2

data encapsulation, 1-15

data integrity, 5-42

data lock, 5-42

database changes

making permanent, 5-43

database character set, 2-17

date

converting, 2-26

TO_CHAR default format, 2-26

DATE datatype, 2-21

DBMS_ALERT package, 8-17

DBMS_OUTPUT package, 8-15

DBMS_PIPE package, 8-16

DBMS_SQL package, 5-9, 8-17

DBMS_STANDARD package, 8-15

DDL support, 5-8

effect on transactions, 5-45

how broken, 5-45

DEBUG_EXTPROC package, 10-21

DEC subtype, 2-12

DECIMAL subtype, 2-12

declaration

collection, 4-6

cursor variable, 5-20

subprogram, 7-8

declarative part

PL/SQL block, 1-3

DECODE function

treatment of nulls, 2-48

DEFAULT keyword, 2-29

default parameter value, 7-15

default pragma, 9-13

DELETE collection method, 4-22

DELETE statement

demo, external procedure, 10-21

dense collection, 4-3

DEPT table, xvi

DEREF operator, 9-32

dereference, 9-32

digits of precision, 2-11

DISTINCT option, 5-3

DISTINCT row operator, 5-7

distributed transaction, 5-43

division operator, 2-3

DLL (dynamic link library), 10-2

dot notation, 1-7

for collection methods, 4-18

for global variables, 3-12

for object attributes, 9-23

for object methods, 9-25

for package contents, 8-6

for record fields, 2-31

DOUBLE PRECISION subtype, 2-12

DUP_VAL_ON_INDEX exception, 6-5

dynamic FOR-loop range, 3-11

dynamic link library (DLL), 10-2

dynamic SQL support, 5-8

E

elaboration, 2-28

element type

collection, 4-5

ELSE clause, 3-3

ELSIF clause, 3-4

encapsulation, data, 1-15

END IF reserved words, 3-3

END LOOP reserved words, 3-8

Entry SQL support, 5-7

error message

maximum length, 6-17

evaluation, 2-40

EXAMPBLD script, B-3

EXAMPLOD script, B-6

predefined, 6-4

propagation, 6-11

raised in declaration, 6-16

raised in handler, 6-16

raising with RAISE statement, 6-10

reraising, 6-14

scope rules, 6-7

user-defined, 6-6

WHEN clause, 6-15

exception handler, 6-15

branching from, 6-16

OTHERS handler, 6-2

using RAISE statement in, 6-14

using SQLCODE function in, 6-17

using SQLERRM function in, 6-17

EXCEPTION_INIT pragma, 6-8

using with raise_application_error, 6-9

exception-handling part

PL/SQL block, 1-3

executable part

PL/SQL block, 1-3

execution environment, 1-16

EXISTS collection method, 4-18

EXISTS comparison operator, 5-6

EXIT statement, 3-6, 3-13

WHEN clause, 3-7

where allowed, 3-6

explicit cursor, 5-10

exponentiation operator, 2-4

expression

how evaluated, 2-40

parentheses in, 2-41

expression or list delimiter, 2-3

EXTEND collection method, 4-21

extensibility, 7-3

EXTERNAL clause, 10-3

components, 10-3

external procedure, 10-2

DEBUG_EXTPROC package, 10-21

debugging, 10-21

demo program, 10-21

guidelines, 10-22

how PL/SQL calls, 10-7

maximum number of parameters, 10-23

passing parameters to, 10-8

registering, 10-3

restrictions, 10-23

specifying datatypes, 10-8

specifying properties, 10-11

extproc process, 10-7

F

FALSE value, 2-8

features, new, A-1

FETCH statement, 5-12, 5-25

fetching across commits, 5-50

Fibonacci sequence, 7-23

field type, 4-25

FIRST collection method, 4-19

flag, PLSQL_V2_COMPATIBILITY, 5-53

FLOAT subtype, 2-12

dynamic range, 3-11

iteration scheme, 3-9

loop counter, 3-10

FOR loop, cursor, 5-16

FOR UPDATE clause, 5-11

restriction on, 5-22

when to use, 5-48

formal parameter, 5-12

format

packaged procedure, 7-9

format mask

when needed, 2-26

forward declaration, 7-8

when needed, 7-8, 7-25

forward reference, 2-33

forward type definition, 9-28

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

function, 7-1, 7-5

RETURN clause, 7-5

specification, 7-5

G

GLB group function, 5-3

GOTO statement, 3-14

restriction, 6-16

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

raised in handler, 6-16

using OTHERS handler, 6-15

handling of nulls, 2-47

hidden declaration, 8-2

hiding, information, 1-15

host variable indicator, 2-3

hypertext markup language (HTML), 8-17

hypertext transfer protocol (HTTP), 8-17

I

identifier

maximum length, 2-5

scope rules, 2-36

IF statement, 3-2

ELSE clause, 3-3

ELSIF clause, 3-4

THEN clause, 3-3

implicit cursor, 5-14

attribute, 5-40

implicit datatype conversion, 2-25

implicit declaration

cursor FOR loop record, 5-16

FOR loop counter, 3-12

IN comparison operator, 2-44, 5-6

IN OUT parameter mode, 7-14

IN parameter mode, 7-13

incomplete object type, 9-28

INDEX BY clause, 4-5

index, cursor FOR loop, 5-16

index-by table, 4-3

indicator, 10-12

INDICATOR property, 10-12

infinite loop, 3-6

information hiding, 1-15, 8-4

inheritance, 9-4

initialization

collection, 4-7

using DEFAULT, 2-29

when required, 2-29

INSERT statement

INT subtype, 2-12

INTEGER subtype, 2-12

interoperability, cursor, 5-19

INTERSECT set operator, 5-7

INTO clause, 5-25

INTO list, 5-12

INVALID_CURSOR exception, 6-5

INVALID_NUMBER exception, 6-5

IS DANGLING predicate, 9-31

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

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

item separator, 2-3

iteration

versus recursion, 7-26

iteration scheme, 3-9

iterative control, 3-6

J

L

label

GOTO statement, 3-14

label delimiter, 2-4

LANGUAGE clause, 10-4

large object (LOB) datatypes, 2-19

LAST collection method, 4-19

LENGTH property, 10-13

LEVEL pseudocolumn, 5-5

lexical unit, 2-2

LIBRARY clause, 10-3

library, alias, 10-2

LIKE comparison operator, 2-43, 5-6

LIMIT collection method, 4-19

LOB (large object) datatypes, 2-19

local subprogram, 1-18

overriding, 5-48

using FOR UPDATE clause, 5-48

LOCK TABLE statement, 5-49

LOGIN_DENIED exception, 6-5

LONG datatype, 2-14

maximum length, 2-14

restrictions, 2-14

LONG RAW datatype, 2-15

converting, 2-27

maximum length, 2-15

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

identifier, 2-5

LONG RAW value, 2-15

LONG value, 2-14

NCHAR value, 2-17

NVARCHAR2 value, 2-18

Oracle error message, 6-17

RAW value, 2-14

VARCHAR2 value, 2-16

maximum precision, 2-11

maximum size

MAXLEN property, 10-13

membership test, 2-44

method

object, 9-3, 9-8

method calls

method, collection, 4-18

MIN group function, 5-3

MINUS set operator, 5-7

mixed notation, 7-12

MLSLABEL datatype, 2-21

mode, parameter

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

multi-line comment, 2-9

multi-line comment delimiter, 2-4

multiplication operator, 2-3

mutual recursion, 7-25

N

name

qualified, 2-34

savepoint, 5-46

NAME clause, 10-4

name resolution, 2-35, E-1

named notation, 7-12

naming conventions, 2-34

national character set, 2-17

National Language Support (NLS), 2-17

NATURAL subtype, 2-11

NATURALN subtype, 2-11

NCHAR datatype, 2-17

NCHAR_CS value, 2-27

NCLOB datatype, 2-20

nested table, 4-2

manipulating, 4-12

versus index-by table, 4-3

nesting

network traffic

new features, A-1

NEXT collection method, 4-20

NEXTVAL pseudocolumn, 5-4

NLS (National Language Support), 2-17

NLS datatype, 2-17

NLS_CHARSET_ID function, 2-27

NLS_CHARSET_NAME function, 2-27

NO_DATA_FOUND exception, 6-5

non-blank-padding semantics, C-3

NOT logical operator

treatment of nulls, 2-47

NOT NULL constraint

effect on %TYPE declaration, 2-30

restriction, 5-10, 7-3

using in collection declaration, 4-6

using in field declaration, 4-27

using in variable declaration, 2-29

NOT_LOGGED_ON exception, 6-5

notation

positional versus named, 7-12

%NOTFOUND cursor attribute, 5-37

NOWAIT parameter, 5-49

NVARCHAR2 datatype, 2-18

NVL function

treatment of nulls, 2-48

null handling, 2-47

NULL statement, 3-17

using in a procedure, 7-4

NUMBER datatype, 2-11

numeric literal, 2-7

NUMERIC subtype, 2-12

O

declaring, 9-21

initializing, 9-22

manipulating, 9-29

object attribute, 9-3, 9-7

accessing, 9-23

allowed datatypes, 9-7

maximum number, 9-7

object constructor

passing parameters to, 9-25

object method, 9-3, 9-8

object table, 9-29

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

concatenation, 2-44

precedence, 2-40

relational, 2-43

OR keyword, 6-15

Oracle, Trusted, 2-10

order method, 9-10

order of evaluation, 2-40, 2-42

OTHERS exception handler, 6-2, 6-15

OUT parameter mode, 7-13

overloading, 7-18

object method, 9-9

packaged subprogram, 8-13

restrictions, 7-19

using subtypes, 7-20

P

package, 8-1-8-2

advantages, 8-4

DEBUG_EXTPROC, 10-21

initializing, 8-7

private versus public objects, 8-12

referencing, 8-6

serially reusable, 8-14

specification, 8-2

package, product-specific, 8-15

packaged cursor, 5-15

packaged subprogram, 1-18, 7-9

overloading, 8-13

parameter

actual versus formal, 7-11

default values, 7-15

parameter passing

by reference, 7-17

PARAMETERS clause, 10-4, 10-10

parentheses, 2-41

Pascal Calling Standard, 10-4

pattern matching, 2-44

performance, 1-20

PL/SQL

advantages, 1-19

architecture, 1-16

block structure, 1-2

execution environments, 1-16

new features, A-1

performance, 1-20

portability, 1-21

procedural aspects, 1-2

reserved words, F-1

sample programs, B-1

support for SQL, 1-19

PL/SQL block

anonymous, 1-2, 7-2

maximum size, 5-51

PL/SQL compiler

how calls are resolved, 7-20

how it works, 5-8

how references are resolved, 5-8

PL/SQL engine, 1-16

in Oracle Server, 1-17

in Oracle tools, 1-19

PL/SQL syntax, 11-1

PL/SQL Wrapper, D-1

input and output files, D-3

PLS_INTEGER datatype, 2-13

PLSQL_V2_COMPATIBILITY flag, 5-53

portability, 1-21

positional notation, 7-12

POSITIVE subtype, 2-11

POSITIVEN subtype, 2-11

EXCEPTION_INIT, 6-8

RESTRICT_REFERENCES, 7-6, 9-12

SERIALLY_REUSABLE, 8-14

precedence, operator, 2-40

precision of digits

specifying, 2-11

predefined exception

raising explicitly, 6-11

redeclaring, 6-10

PRIOR collection method, 4-20

PRIOR row operator, 5-5, 5-7

private object, 8-12

procedural abstraction, 9-2

procedure, 7-1, 7-3

specification, 7-4

productivity, 1-21

program unit, 1-11

PROGRAM_ERROR exception, 6-5

propagation, exception, 6-11

property

CHARSETFORM, 10-13

CHARSETID, 10-13

INDICATOR, 10-12

pseudocolumn, 5-4

pseudoinstruction, 6-8

public object, 8-12

purity level, 9-12

Q

qualifier

using subprogram name as, 2-36

when needed, 2-34, 2-38

query work area, 5-19

quoted identifier, 2-6

quoted identifier delimiter, 2-3

R

RAISE statement, 6-10

using in exception handler, 6-14

raise_application_error procedure, 6-8

raising an exception, 6-10

range operator, 2-4

RAW datatype, 2-14

converting, 2-27

maximum length, 2-14

read consistency, 5-42

READ ONLY parameter, 5-48

readability, 2-2, 3-17

read-only transaction, 5-47

REAL subtype, 2-12

assigning, 4-29

comparing, 4-30

declaring, 4-26

implicit declaration, 5-16

initializing, 4-27

manipulating, 4-31

referencing, 4-27

RECORD datatype, 4-25

recursion, 7-23

terminating condition, 7-23

recursion versus iteration, 7-26

declaring, 9-27

dereferencing, 9-32

REF CURSOR datatype, 5-19

REF operator, 9-31

REF type modifier, 9-27

reference type, 2-10

relational operator, 2-3-2-4, 2-43

remote access indicator, 2-3

REPEAT UNTIL structure

REPLACE function

treatment of nulls, 2-48

reraising an exception, 6-14

reserved words, F-1

using as quoted identifier, 2-6

resolution, name, 2-35, 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-20

RETURNING clause, 9-34

reusability, 7-3

reusable packages, 8-14

REVERSE reserved word, 3-10

rollback

statement-level, 5-45

rollback segment, 5-42

ROLLBACK statement, 5-44

effect on savepoints, 5-45

routine

row operator, 5-7

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

ROWID datatype, 2-15

ROWID pseudocolumn, 5-5

ROWIDTOCHAR function, 5-5

ROWLABEL column, 5-6

ROWNUM pseudocolumn, 5-5

%ROWTYPE attribute, 2-31

ROWTYPE_MISMATCH exception, 6-6

RPC (remote procedure call), 6-11

RTRIM function

using to insert data, C-4

runtime error, 6-1

S

sample database table

DEPT table, xvi

sample programs, B-1

savepoint name

SAVEPOINT statement, 5-45

scalar type, 2-10

scale

specifying, 2-12

scheme, iteration, 3-9

scientific notation, 2-7

collection, 4-7

cursor parameter, 5-11

definition, 2-36

identifier, 2-36

loop counter, 3-12

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

serially reusable package, 8-14

SERIALLY_REUSABLE pragma, 8-14

server

integration with PL/SQL, 1-22

service routine, 10-14

examples, 10-15

session-specific variables, 8-10

set operator, 5-7

SET TRANSACTION statement, 5-47

side effects, 7-13, 9-12

significant characters, 2-5

SIGNTYPE subtype, 2-11

single-line comment, 2-9

single-line comment indicator, 2-4

size constraint, subtype, 2-23

size limit, varray, 4-5

SMALLINT subtype, 2-12

SOME comparison operator, 5-6

spaces

where allowed, 2-2

spaghetti code, 3-13

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

support in PL/SQL, 1-19

SQL cursor

SQL standards conformance, 5-7

SQL92 conformance, 5-7

SQLCODE function, 6-17

SQLERRM function, 6-17

standalone subprogram, 1-18

START WITH clause, 5-5

statement

statement terminator, 2-3

statement-level rollback, 5-45

STDDEV group function, 5-3

STEP clause

mimicking, 3-11

stepwise refinement, 1-2

STORAGE_ERROR exception, 6-6

when raised, 7-23

store table, 4-4

stored subprogram, 1-18, 7-10

string comparison semantics, C-2

string literal, 2-8

STRING subtype, 2-16

structure theorem, 3-2

stub, 3-18, 7-3

subprogram, 7-2

advantages, 7-3

how calls are resolved, 7-20

overloading, 7-18

packaged, 1-18, 7-9

procedure versus function, 7-5

recursive, 7-23

standalone, 1-18

stored, 1-18, 7-10

SUBSCRIPT_BEYOND_COUNT exception, 6-6

SUBSCRIPT_OUTSIDE_LIMIT exception, 6-6

SUBSTR function, 6-18

subtraction operator, 2-3

subtraction/negation operator, 2-3

subtype, 2-11, 2-22

SUM group function, 5-3

support for SQL, 5-2

syntax definition, 11-1

T

U

unary operator, 2-40

underscore, 2-4

unhandled exception, 6-11, 6-18

uninitialized object

how treated, 9-23

UNION ALL set operator, 5-7

UNION set operator, 5-7

UPDATE statement

URL (universal resource locator), 8-17

user session, 5-42

user-defined exception, 6-6

user-defined record, 4-25

declaring, 4-26

referencing, 4-27

user-defined subtype, 2-22

UTL_FILE package, 8-16

UTL_HTTP package, 8-17

V

VALUE operator, 9-30

VALUE_ERROR exception, 6-6

VARCHAR subtype, 2-16

VARCHAR2 datatype, 2-16

VARCHAR2 semantics, C-1

variable

assigning values, 2-38

declaring, 2-28

initializing, 2-38

session-specific, 8-10

VARIANCE group function, 5-3

varray

size limit, 4-5

VARRAY datatype, 4-4

visibility

of package contents, 8-2

versus scope, 2-36

W

WHEN clause, 3-7, 6-15

WHILE loop, 3-8

WITH CONTEXT clause, 10-4, 10-14

words, reserved, F-1

work area, query, 5-19

Z

ZERO_DIVIDE exception, 6-6

Copyright © 1997 Oracle Corporation.
All Rights Reserved.