PL/SQL User's Guide and Reference
Library
Product
Contents
Index
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
@
!= relational operator
" quoted identifier delimiter
% attribute indicator
%FOUND cursor attribute
using with explicit cursor
using with implicit cursor
%ISOPEN cursor attribute
using with explicit cursor
using with implicit cursor
%NOTFOUND cursor attribute
using with explicit cursor
using with implicit cursor
%ROWCOUNT cursor attribute
using with explicit cursor
using with implicit cursor
%ROWTYPE attribute
syntax
using in record declaration
%TYPE attribute
syntax
using in field declaration
using in variable declaration
' character string delimiter
( expression or list delimiter
) expression or list delimiter
* multiplication operator
** exponentiation operator
*/ multi-line comment delimiter
+ addition operator
, item separator
- subtraction/negation operator
-- single-line comment indicator
[
2
]
. component selector
.. range operator
/ division operator
/* multi-line comment delimiter
: host variable indicator
:= assignment operator
[
2
]
; statement terminator
<< relational operator
<<<< label delimiter
<<= relational operator
<<> relational operator
= relational operator
=> association operator
> relational operator
>= relational operator
>> label delimiter
@ remote access indicator
[
\
]
^
^= relational operator
abstraction
actual parameter
address
aggregate assignment
aliasing
aliasing, parameter
ALL comparison operator
ALL option
[
2
]
ALL row operator
AND logical operator
anonymous PL/SQL block
ANY comparison operator
apostrophe
architecture
arithmetic operators
ARRAYLEN statement
assignment statement, syntax
assignment
aggregate
character string
cursor variable
field
PL/SQL table
record
semantics
association operator
asterisk (*) option
asynchronous operation
attribute
%ROWTYPE
[
2
]
%TYPE
[
2
]
COUNT
cursor
[
2
]
DELETE
EXISTS
FIRST
LAST
NEXT
PL/SQL table
[
2
] [
3
]
PRIOR
AVG group function
Bachus-Naur Form (BNF)
base type
[
2
]
basic loop
BETWEEN comparison operator
binary operator
BINARY_INTEGER datatype
bind variable
binding
blank-padding semantics
block
anonymous
label
maximum size
PL/SQL
structure
body
cursor
function
package
procedure
BOOLEAN datatype
Boolean
expression
literal
value
built-in function
call, subprogram
carriage return
case sensitivity
identifier
string literal
case, upper and lower
CHAR column, maximum width
CHAR datatype
character literal
character set
CHARACTER subtype
character value
assigning
comparing
inserting
selecting
CLOSE statement
[
2
]
syntax
collating sequence
column alias
when needed
column, ROWLABEL
COMMENT clause
comment
multi-line
restrictions
single-line
syntax
using to disable code
commit
COMMIT statement
syntax
comparison
of character values
of expressions
operators
[
2
]
compilation, using the PL/SQL Wrapper
compiler
component
PL/SQL table
record
composite type
concatenation operator
treatment of nulls
concurrency
conditional control
constant
declaring
syntax
constraint
datatype
NOT NULL
where not allowed
[
2
]
control structure
[
2
]
conditional
iterative
sequential
conventions
naming
notational
conversion function, when needed
conversion, datatype
correlated subquery
COUNT attribute
COUNT group function
CURRENT OF clause
current row
CURRVAL pseudocolumn
cursor
[
2
]
cursor attribute, %FOUND
[
2
]
%ISOPEN
[
2
]
%NOTFOUND
[
2
]
%ROWCOUNT
[
2
]
implicit
syntax
values
cursor FOR loop
passing parameters to
cursor variable
[
2
]
assignment
closing
declaring
fetching from
opening
restrictions
syntax
using to reduce network traffic
CURSOR_ALREADY_OPEN exception
cursor
analogy
attribute
closing
declaring
explicit
fetching from
implicit
opening
packaged
parameterized
RETURN clause
scope rules
syntax
data encapsulation
data integrity
data lock
database changes
making permanent
undoing
datatype
BINARY_INTEGER
BOOLEAN
CHAR
constraint
DATE
families
implicit conversion
LONG
LONG RAW
MLSLABEL
NUMBER
PLS_INTEGER
RAW
RECORD
REF CURSOR
ROWID
scalar versus composite
TABLE
VARCHAR2
DATE datatype
date
converting
default value
TO_CHAR default format
DBMS_ALERT package
DBMS_OUTPUT package
DBMS_PIPE package
DBMS_SQL package
[
2
]
DBMS_STANDARD package
DDL, support for
deadlock
effect on transactions
how broken
DEC subtype
DECIMAL subtype
declaration
constant
cursor
cursor variable
exception
forward
PL/SQL table
subprogram
user-defined record
variable
declarative part
function
PL/SQL block
procedure
Declare Section
DECLARE TABLE statement
DECODE function
treatment of nulls
using to mimic dynamic SQL
default parameter value
DEFAULT reserved word
DELETE attribute
DELETE statement, syntax
delimiter, list
dependency, remote
DEPT table
digits of precision
DISTINCT row operator
distributed transaction
division by zero
division operator
dot notation
[
2
]
referencing global variables
referencing package contents
referencing record fields
DOUBLE PRECISION subtype
DUP_VAL_ON_INDEX exception
dynamic FOR-loop range
dynamic SQL
EXECUTE statement
mimicking
PREPARE statement
support for
USING clause
elaboration
ellipsis
ELSE clause
ELSIF clause
embedded PL/SQL
calling stored subprograms
handling nulls
languages supported
using ARRAYLEN statement
EMP table
encapsulation, data
END IF reserved words
END LOOP reserved words
Entry SQL, support for
environment
OCI
Oracle Precompilers
SQL*Plus
error
error message, maximum length
evaluation
EXAMPBLD script
EXAMPLOD script
exception
exception handler
branching from
OTHERS handler
using RAISE statement in
using SQLCODE function in
using SQLERRM function in
exception-handling part
function
PL/SQL block
procedure
EXCEPTION_INIT pragma
syntax
using with raise_application_error
exception
declaring
EXCEPTION_INIT pragma
predefined
propagation
raised in declaration
raised in handler
raising with RAISE statement
reraising
ROWTYPE_MISMATCH
scope rules
syntax
user-defined
WHEN clause
executable part
function
PL/SQL block
procedure
execution environment
[
2
]
EXISTS attribute
EXISTS comparison operator
EXIT statement
[
2
]
syntax
WHEN clause
where allowed
explicit cursor
exponentiation operator
expression
Boolean
how evaluated
parentheses in
syntax
extensibility
FALSE value
features, new
FETCH statement
[
2
]
syntax
fetching across commits
Fibonacci sequence
[
2
]
field
assigning values
file I/O
FIRST attribute
FLOAT subtype
FOR loop
FOR loop, cursor
FOR loop
dynamic range
iteration scheme
loop counter
nested
FOR UPDATE clause
[
2
]
when to use
formal parameter
format mask, when needed
format
function
package
packaged procedure
procedure
forward declaration
when needed
[
2
]
forward reference
function
[
2
]
body
call
kinds
parameter
parts
result value
RETURN clause
[
2
]
specification
syntax
GLB group function
GOTO statement
label
misuse
restriction
syntax
GROUP BY clause
group function
AVG
COUNT
GLB
LUB
MAX
MIN
STDDEV
SUM
treatment of nulls
VARIANCE
handler
handling exceptions
raised in declaration
raised in handler
using OTHERS handler
hexadecimal number
hidden declaration
hiding, information
host array, using with PL/SQL table
host language
host program
host variable
converting datatype
declaring
referencing
scope rules
identifier
forming
maximum length
quoted
scope rules
IF statement
ELSE clause
ELSIF clause
syntax
THEN clause
implicit cursor
attribute
implicit datatype conversion
implicit declaration
cursor FOR loop record
FOR loop counter
IN comparison operator
[
2
]
IN OUT parameter mode
IN parameter mode
INDEX BY clause
index
cursor FOR loop
PL/SQL table
indicator variable
infinite loop
information hiding
[
2
]
initialization
package
record
using DEFAULT
variable
when required
INSERT statement, syntax
instantiation
INT subtype
INTEGER subtype
interoperability
INTERSECT set operator
INTO clause
INTO list
[
2
]
INVALID_CURSOR exception
INVALID_NUMBER exception
IS NULL comparison operator
[
2
]
iteration
scheme
versus recursion
iterative control
join
label
block
GOTO statement
loop
LAST attribute
LEVEL pseudocolumn
lexical unit
library
LIKE comparison operator
[
2
]
literal
Boolean
character
definition
numeric
string
syntax
local subprogram
lock
LOCK TABLE statement
syntax
lock
modes
overriding
using FOR UPDATE clause
logical operator
LOGIN_DENIED exception
LONG datatype
maximum length
restrictions
LONG RAW datatype
converting
maximum length
LOOP statement
forms
syntax
loop
counter
kinds
label
LUB group function
maintainability
MAX group function
maximum length
CHAR value
identifier
LONG RAW value
LONG value
Oracle error message
RAW value
[
2
]
VARCHAR2 value
maximum precision
membership test
MIN group function
MINUS set operator
mixed notation
MLSLABEL datatype
mode, parameter
IN
IN OUT
OUT
modularity
[
2
] [
3
]
multi-line comment
multiplication operator
mutual recursion
name resolution
name, cursor
named notation
name
qualified
savepoint
variable
naming conventions
NATURAL subtype
NATURALN subtype
nesting
block
FOR loop
record
network traffic, reducing
new features
NEXT attribute
NEXTVAL pseudocolumn
nibble
NO_DATA_FOUND exception
non-blank-padding semantics
NOT logical operator
treatment of nulls
NOT NULL constraint
effect on %TYPE declaration
restriction
[
2
]
using in field declaration
using in TABLE type definition
using in variable declaration
NOT_LOGGED_ON exception
notational conventions
notation
mixed
positional versus named
NOWAIT parameter
NULL statement
syntax
using in a procedure
null
detecting with indicator variable
handling
nullity
NUMBER datatype
numeric literal
NUMERIC subtype
NVL function, treatment of nulls
OCI (Oracle Call Interface)
calling a stored subprogram
environment
OPEN statement
syntax
OPEN-FOR statement
syntax
operator
comparison
concatenation
logical
precedence
relational
OR logical operator
OR reserved word
Oracle Call Interface (OCI)
Oracle Precompilers
ARRAYLEN statement
DECLARE TABLE statement
environment
SQLCHECK option
VARCHAR pseudotype
Oracle, Trusted
order of evaluation
[
2
]
OTHERS exception handler
[
2
]
OUT parameter mode
overloading
packaged subprogram
restrictions
using subtypes
p-code
package
package, product-specific
package
advantages
bodiless
body
creating
packaged cursor
packaged subprogram
[
2
]
calling
overloading
package
initializing
private versus public objects
product-specific
referencing
scope
specification
syntax
parameter
actual versus formal
aliasing
cursor
default values
modes
parentheses
pattern matching
performance
pipe
PL/SQL block, anonymous
[
2
]
maximum size
syntax
PL/SQL compiler
how aliasing occurs
how calls are resolved
how it works
how references are resolved
PL/SQL engine
in Oracle Server
in Oracle tools
PL/SQL syntax
PL/SQL table
PL/SQL table attribute
COUNT
DELETE
EXISTS
FIRST
LAST
NEXT
PRIOR
syntax
PL/SQL table
assigning values to elements
attributes
declaring
inserting Oracle data
primary key
referencing
restriction
[
2
]
retrieving Oracle data
scope
syntax
using host array
PL/SQL Wrapper
input and output files
running
PL/SQL
advantages
architecture
block structure
execution environments
new features
performance
portability
procedural aspects
reserved words
sample programs
support for SQL
PLS_INTEGER datatype
pointer
portability
positional notation
POSITIVE subtype
POSITIVEN subtype
pragma
EXCEPTION_INIT
RESTRICT_REFERENCES
precedence, operator
precision of digits, specifying
predefined exception
list of
raising explicitly
redeclaring
predicate
primary key, PL/SQL table
PRIOR attribute
PRIOR row operator
[
2
]
private object
procedure
body
calling
packaged
parameter
parts
specification
syntax
productivity
program unit
PROGRAM_ERROR exception
propagation, exception
pseudocolumn
[
2
]
CURRVAL
LEVEL
NEXTVAL
ROWID
ROWNUM
pseudoinstruction
pseudotype
public object
qualifier
using subprogram name as
when needed
[
2
]
query work area
quoted identifier
RAISE statement
syntax
using in exception handler
raise_application_error procedure
raising an exception
RAW datatype
converting
maximum length
[
2
]
read consistency
[
2
]
READ ONLY parameter
read-only transaction
readability
REAL subtype
record
RECORD datatype
defining
record
%ROWTYPE
assignment
declaring
implicit declaration
initializing
nesting
referencing
restriction
scope
syntax
recursion
infinite
mutual
terminating condition
versus iteration
REF CURSOR datatype
defining
reference type
relational operator
remote dependency
REPEAT UNTIL structure, mimicking
REPLACE function, treatment of nulls
reraising an exception
reserved words
misuse of
using as quoted identifier
RESTRICT_REFERENCES pragma
result set
[
2
]
result value, function
RETURN clause
cursor
function
RETURN statement
syntax
return type
[
2
]
reusability
REVERSE reserved word
rollback segment
ROLLBACK statement
effect on savepoints
implicit
syntax
rollback
purpose
statement-level
row lock
row operator
rowid
ROWID datatype
ROWID pseudocolumn
using to mimic CURRENT OF clause
ROWIDTOCHAR function
ROWLABEL column
ROWNUM pseudocolumn
ROWTYPE_MISMATCH exception
RPC (remote procedure call)
RTRIM function, using to insert data
runtime error
sample database table
DEPT table
EMP table
sample programs
savepoint name, reusing
SAVEPOINT statement
syntax
scalar type
scale, specifying
scheme, iteration
scientific notation
scope
cursor
cursor parameter
definition
exception
host variable
identifier
loop counter
package
PL/SQL table
record
script, SQL*Plus
SELECT INTO statement, syntax
selector
[
2
]
semantics
assignment
blank-padding
non-blank-padding
string comparison
separator
sequence
sequential control
server, integration with PL/SQL
session
session-specific variables
set operator
SET TRANSACTION statement
syntax
side effects
signature
significant characters
single-line comment
size constraint, subtype
SMALLINT subtype
snapshot
SOME comparison operator
spaces, where allowed
spaghetti code
sparsity
specification
cursor
function
package
procedure
SQL cursor, syntax
SQL standards conformance
SQL*Plus
calling a stored subprogram
environment
executing a PL/SQL block
SET COMPATIBILITY command
substitution variable
using a script
SQL92 conformance
SQLCHECK option
SQLCODE function
syntax
SQL
comparison operators
data manipulation statements
SQLERRM function
syntax
SQL
pseudocolumn
row operators
set operators
support in PL/SQL
standalone subprogram
START WITH clause
statement-level rollback
statement
assignment
CLOSE
[
2
] [
3
]
COMMIT
DELETE
EXIT
FETCH
[
2
] [
3
]
GOTO
IF
INSERT
LOCK TABLE
LOOP
NULL
OPEN
[
2
]
OPEN-FOR
[
2
]
RAISE
RETURN
ROLLBACK
SAVEPOINT
SELECT INTO
SET TRANSACTION
terminator
UPDATE
STDDEV group function
STEP clause, mimicking
stepwise refinement
STORAGE_ERROR exception
when raised
stored subprogram
[
2
]
string comparison semantics
string literal
STRING subtype
structure theorem
stub
[
2
]
subprogram
[
2
]
advantages
declaring
how calls are resolved
[
2
]
local
overloading
packaged
[
2
]
parts
procedure versus function
recursive
standalone
stored
[
2
]
subquery
substitution variable
SUBSTR function
subtraction operator
subtype
[
2
]
CHARACTER
compatibility
DEC
DECIMAL
defining
DOUBLE PRECISION
FLOAT
INT
INTEGER
NATURAL
NATURALN
NUMERIC
overloading
POSITIVE
POSITIVEN
REAL
SMALLINT
STRING
VARCHAR
SUM group function
support for SQL
syntax definition
tab
TABLE datatype
defining
table, PL/SQL
terminating condition
terminator, statement
ternary operator
THEN clause
TIMEOUT_ON_RESOURCE exception
timestamp
TOO_MANY_ROWS exception
top-down design
trailing blanks, how handled
transaction
[
2
]
transaction processing
[
2
]
transaction
committing
distributed
ending properly
read-only
rolling back
TRUE value
truncation
detecting with indicator variable
when performed
Trusted Oracle
truth tables
type definition
RECORD
REF CURSOR
TABLE
unary operator
underscore
unhandled exception
[
2
]
UNION ALL set operator
UNION set operator
UPDATE statement, syntax
user session
user-defined record
declaring
referencing
restriction
user-defined
exception
record
subtype
UTL_FILE package
VALUE_ERROR exception
VARCHAR pseudotype
VARCHAR subtype
VARCHAR2 datatype
variable
assigning values
declaring
initializing
session-specific
syntax
VARIANCE group function
visibility
of package contents
versus scope
WHEN clause
[
2
]
WHILE loop
wildcard
words, reserved
work area, query
ZERO_DIVIDE exception
[
\
]
^
_
`
{
|
|| concatenation operator
[
2
]
}
~
~= relational operator