Skip Headers

PL/SQL User's Guide and Reference
Release 2 (9.2)

Part Number A96624-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents

Master Index

Feedback

Go to previous page

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

%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

A

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

B

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

C

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

D

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

E

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

F

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

G

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

H

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

I

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

J

joins, 8-62

L

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

M

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

N

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

O

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

P

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

Q

qualifiers
using subprogram names as, 2-19
when needed, 2-17, 2-21
query work areas, 6-16
quoted identifiers, 2-6

R

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

S

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

T

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

U

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

V

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

W

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

Z

ZERO_DIVIDE exception, 7-6

Go to previous page
Oracle
Copyright © 1996, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents

Master Index

Feedback