Go to main content
74/74
Index
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
Y
Z
Symbols
%BULK_EXCEPTIONS. See BULK_EXCEPTIONS cursor attribute
%BULK_ROWCOUNT. See BULK_ROWCOUNT cursor attribute
%FOUND. See FOUND cursor attribute
%ISOPEN. See ISOPEN cursor attribute
%NOTFOUND. See NOTFOUND cursor attribute
%ROWCOUNT. See ROWCOUNT cursor attribute
%ROWTYPE. See ROWTYPE attribute
%TYPE. See TYPE attribute
:= assignment operator,
1.2.2.2
|| concatenation operator,
2.6.1.9
. item separator,
2.1.1
<< label delimiter,
2.1.1
.. range operator,
2.1.1
,
4.3.6
=, !=, <>, and ~= relational operators,
2.6.1.4
<, >, <=, and >= relational operators,
2.6.1.4
@ remote access indicator,
2.1.1
,
2.3
-- single-line comment delimiter,
2.1.1
; statement terminator,
2.1.1
,
13
- subtraction/negation operator,
2.1.1
A
ACCESS_INTO_NULL exception,
10.3
actual parameters,
6.4.2
additional information
Oracle Technology Network,
Preface
,
1
address
REF CURSOR,
6.5.1
advantages
PL/SQL,
1.1
aggregate assignment,
2.2.5.1
aggregate functions
and PL/SQL,
6.1.3
AVG,
2.9
COUNT,
2.9
AL16UTF16 character encoding,
3.1.3.1
aliases
using with a select list,
2.2.5.2
aliasing
for expression values in a cursor FOR loop,
6.3.7
parameters,
8.13
ALL row operator,
6.1.3
,
6.1.5
ALL_PLSQL_OBJECT_SETTINGS view
PL/SQL compiler parameter settings,
11.1
,
11.2
,
11.9.7
ALTER .. COMPILE statement,
10.9.2
ALTER PROCEDURE
using to recompile a procedure,
10.9.3
analytic functions
CORR,
2.9
LAG,
2.9
anonymous blocks,
1.2.1
apostrophes,
2.1.3.3
architecture
PL/SQL,
1.3
ARRAY
VARYING,
5.3
arrays
associative,
5.1.1
index-by-tables,
5.1.1
variable-size,
5.1.1
articles
technical on OTN,
Preface
AS
defining a procedure,
8.3
defining a procedure body,
8.3
assignment operator,
1.2.2.2
assignment statement
links to examples,
13
syntax,
13
assignments
aggregate,
2.2.5.1
character string,
3.4.1
collection,
5.5
field,
5.11
IN OUT parameters,
1.2.2.2
records,
5.11
semantics,
3.4.1
variables,
1.2.2.2
associative arrays
sets of key-value pairs,
5.1.1.3
syntax,
13
understanding,
5.1.1
VARCHAR2 keys and globalization settings,
5.1.1.4
versus nested tables,
5.2.1
asynchronous operations,
9.7.1
atomically null
object types in PL/SQL,
12.1.2
attributes
%ROWTYPE,
1.2.5.2
,
2.2.5
%TYPE,
1.2.5.1
,
2.2.4
explicit cursors,
6.2.2.6
,
6.2.2.6
AUTHID clause
specifying privileges for a subprogram,
8.9.2
use with functions,
8.4
using to specify privileges of invoker,
8.9
autonomous functions
calling from SQL,
6.8.5
RESTRICT_REFERENCES pragma,
6.8.5
autonomous transactions
advantages,
6.8.1
avoiding errors,
6.8.3
comparison with nested transactions,
6.8.2.1
controlling,
6.8.3
in PL/SQL,
6.8
SQL%ROWCOUNT attribute,
6.2.1.1
autonomous triggers
using,
6.8.4
AUTONOMOUS_TRANSACTION pragma
defining,
6.8.2
links to examples,
13
syntax,
13
AVG
SQL aggregate function,
2.9
B
base types
PL/SQL number types,
3.1.1.1.1
PL/SQL types,
3.2
basic loops,
4.3.1
BEGIN
block structure,
1.2.1
start of executable part,
8.3
start of executable PL/SQL block,
13
syntax,
13
best practices
locating information on OTN,
Preface
Oracle Technology Network,
1
,
11
BETWEEN clause
FORALL,
13
BETWEEN comparison operator,
2.6.1.7
expressions,
13
BFILE datatype,
3.1.4.1
binary operators,
2.6
BINARY_DOUBLE datatype,
3.1.1.2
BINARY_FLOAT and BINARY_DOUBLE datatypes
for computation-intensive programs,
11.6
BINARY_FLOAT datatype,
3.1.1.2
BINARY_FLOAT_INFINITY
constant,
3.1.1.2
BINARY_FLOAT_MAX_NORMAL
constant,
3.1.1.2
BINARY_FLOAT_MAX_SUBNORMAL
constant,
3.1.1.2
BINARY_FLOAT_MIN_NORMAL
constant,
3.1.1.2
BINARY_FLOAT_MIN_SUBNORMAL
constant,
3.1.1.2
BINARY_FLOAT_NAN
constant,
3.1.1.2
BINARY_INTEGER datatype,
3.1.1.1
compared to PLS_INTEGER,
Preface
new features,
Preface
BINARY_INTEGER. See also PLS_INTEGER
bind variables,
1.2.2.3
binding,
11.5
blank-padding semantics,
3.4.2
BLOB datatype,
3.1.4.2
block declaration
syntax,
13
blocks
BEGIN,
1.2.1
DECLARE,
1.2.1
END,
1.2.1
EXCEPTION,
1.2.1
label,
2.4
links to examples,
13
nesting,
1.2.1
,
1.2.1
PL/SQL,
13
structure,
1.2.1
,
1.2.1
syntax,
13
BODY
CREATE PACKAGE SQL statement,
1.2.8.2
,
9.1
,
13
CREATE TYPE SQL statement,
13
with SQL CREATE PACKAGE statement,
1.2.8.2
,
9.1
body
cursor,
9.9
function,
13
functions,
8.4
package,
9.4
packages,
13
procedure,
8.3
,
13
Boolean
assigning values,
2.5.1
expressions,
2.6.2
literals,
2.1.3.4
BOOLEAN datatype,
3.1.5.1
built-in functions,
2.9
bulk
fetches,
11.5.2.1
returns,
11.5.2.3
BULK clause
with COLLECT,
11.5.2
BULK COLLECT clause,
11.5.2
checking whether no results are returned,
11.5.2
DELETE statement,
13
EXECUTE IMMEDIATE,
13
FETCH,
13
retrieving DML results,
11.5.2.3
retrieving query results with,
11.5.2
RETURNING clause,
13
returning multiple rows,
6.3.2
SELECT INTO,
13
using LIMIT clause,
11.5.2
,
11.5.2.2
using ROWNUM pseudocolumn,
11.5.2
using SAMPLE clause,
11.5.2
using with FORALL statement,
11.5.2.4
bulk SQL
in dynamic SQL,
7.3
using to reduce loop overhead,
11.5
BULK_EXCEPTIONS cursor attribute
ERROR_CODE field,
11.5.1.3
ERROR_INDEX field,
11.5.1.3
example,
11.5.1.3
handling FORALL exceptions,
11.5.1.3
using ERROR_CODE field with SQLERRM,
11.5.1.3
BULK_ROWCOUNT cursor attribute
affected by FORALL,
11.5.1.2
by-reference parameter passing,
8.13
by-value parameter passing,
8.13
C
CALL
SQL statement,
1.2.8.2
call specification,
9.1
calling
Java stored procedures,
8.11
procedures,
1.2.8.2
stored subprograms,
1.3.1.2
calls
inter-language,
8.11
resolving subprogram,
8.8
subprograms,
8.6.2
CARDINALITY operator
for nested tables,
5.6
carriage returns,
2.1
CASE expressions,
2.6.3
,
2.6.3
overview,
1.2.6.1
case sensitivity
identifier,
2.1.2
string literal,
2.1.3.3
CASE statement
links to examples,
13
searched,
4.2.4.1
syntax,
13
using,
4.2.4
CASE_NOT_FOUND exception,
10.3
CHAR datatype,
3.1.2.1
differences with VARCHAR2,
3.4
character literals,
2.1.3.2
character sets
PL/SQL,
2.1
CHARACTER subtype,
3.1.2.1.1
character values
assigning,
3.4.1
comparing,
3.4.2
inserting,
3.4.3
selecting,
3.4.4
clauses
AUTHID,
8.4
,
8.9
,
8.9.2
BULK COLLECT,
11.5.2
LIMIT,
11.5.2.2
CLOB datatype,
3.1.4.3
CLOSE statement
disables cursor,
6.2.2.5
disabling cursor variable
closing,
6.5.4.4
links to examples,
13
syntax,
13
code samples
Oracle Technology Network,
Preface
,
1
collating sequence,
2.6.2.2
COLLECT clause
with BULK,
11.5.2
collection exceptions
when raised,
5.9
collection methods
syntax,
13
usage,
5.8
COLLECTION_IS_NULL exception,
10.3
collections
allowed subscript ranges,
5.4.1
applying methods to parameters,
5.8.9
assigning,
5.5
associative arrays versus nested tables,
5.2.1
avoiding exceptions,
5.9
bulk binding,
5.11.5
,
11.5
choosing the type to use,
5.2
comparing,
5.6
constructors,
5.4
COUNT method,
5.8.2
declaring variables,
5.3
,
5.3.1
defining types,
5.3
DELETE method,
5.8.8
element types,
5.3
EXISTS method,
5.8.1
EXTEND method,
5.8.6
FIRST method,
5.8.4
initializing,
5.4
LAST method,
5.8.4
LIMIT method,
5.8.3
links to examples,
13
,
13
methods,
5.8
multilevel,
5.7
NEXT method,
5.8.5
operators to transform nested tables,
5.5
ordered group of elements,
5.1
overview,
1.2.10.2
PRIOR method,
5.8.5
referencing,
5.4
referencing elements,
5.4.1
scope,
5.3
syntax,
13
testing for null,
5.6
TRIM method,
5.8.7
types in PL/SQL,
5
understanding,
5.1.1
using object types with,
12.4
varrays versus nested tables,
5.2.2
column aliases
expression values in a cursor loop,
6.3.7
when needed,
2.2.5.2
COMMENT clause
using with transactions,
6.7.1
comments
in PL/SQL,
2.1.4
links to examples,
13
restrictions,
2.1.4.3
syntax,
13
COMMIT statement,
6.7.1
,
13
links to examples,
13
comparison operators,
6.1.5
comparisons
of character values,
3.4.2
of expressions,
2.6.2
of null collections,
5.6
operators,
2.6.1.3
PL/SQL,
2.6
with NULLs,
2.6.4
compiler parameter settings
ALL_PLSQL_OBJECT_SETTINGS view,
11.1
,
11.2
,
11.9.7
compiler parameters
and REUSE SETTINGS clause,
11.1
PL/SQL,
11.1
compiling
conditional,
1.2.7
,
2.7
PL/SQL procedures for native execution,
11.9
composite types
collection and records,
5.1
overview,
3.1
concatenation operator,
2.6.1.9
treatment of nulls,
2.6.4.1
conditional compilation,
1.2.7
,
2.7
availability for previous Oracle database releases,
2.7.1
control tokens,
2.7.1.1
examples,
2.7.2.1
inquiry directives,
2.7.1.4
limitations,
2.7.3
new features,
Preface
NLS_LENGTH_SEMANTICS initialization parameter,
2.7.1.5
PLSQL_CCFLAGS initialization parameter,
2.7.1.5
PLSQL_CODE_TYPE initialization parameter,
2.7.1.5
PLSQL_DEBUG initialization parameter,
2.7.1.5
PLSQL_LINE flag,
2.7.1.5
PLSQL_OPTIMIZE_LEVEL initialization parameter,
2.7.1.5
PLSQL_UNIT flag,
2.7.1.5
PLSQL_WARNINGS initialization parameter,
2.7.1.5
restrictions,
2.7.3
static constants,
2.7.1.6.4
using PLSQL_CCFLAGS initialization parameter,
2.7.1.7
,
2.7.1.7
using static expressions with,
2.7.1.6
using with DBMS_DB_VERSION,
2.7.1.8
using with DBMS_PREPROCESSOR,
2.7.2.2
conditional control,
4.2
conditional statement
guidelines,
4.2.5
CONSTANT
declaration,
13
for declaring constants,
1.2.2.4
,
2.2.1
constants
declaring,
1.2.2.4
,
2.2
,
2.2.1
links to examples,
13
static,
2.7.1.6.4
syntax,
13
understanding PL/SQL,
1.2.2
constraints
NOT NULL,
2.2.3
constructors
collection,
5.4
context
transactions,
6.8.2.2
control structures
conditional,
4.2
iterative,
4.3
overview of PL/SQL,
4.1
sequential,
4.4
understanding,
1.2.6
conventions
PL/SQL naming,
2.3
conversions
datatype,
3.3
functions,
3.3.2
CORR
SQL analytic function,
2.9
correlated subqueries,
6.4.1
COUNT
SQL aggregate function,
2.9
COUNT method
collections,
5.8.2
,
13
CREATE
with PROCEDURE statement,
1.2.8.1
,
1.3.1.2
,
8.1
CREATE FUNCTION statement,
1.2.8.1
,
1.3.1.2
,
8.1
CREATE PROCEDURE statement,
1.2.8.1
,
1.3.1.2
,
8.1
CREATE statement
packages,
1.2.8.2
,
9.1
with FUNCTION,
1.2.8.1
,
1.3.1.2
,
8.1
CREATE_WRAPPED function
obfuscation,
A.3
using,
A.3.1
creating
functions,
1.2.8.1
,
1.3.1.2
,
8.1
packages,
1.2.8.2
,
9.1
procedures,
1.2.8.1
,
1.3.1.2
,
8.1
CURRENT OF clause
with UPDATE,
6.7.7
CURRVAL
pseudocolumn,
6.1.4
cursor attributes
%BULK_EXCEPTIONS,
11.5.1.3
%BULK_ROWCOUNT,
11.5.1.2
%FOUND,
6.2.1.1
,
6.2.2.6
%ISOPEN,
6.2.1.1
,
6.2.2.6
%NOTFOUND,
6.2.1.1
,
6.2.2.6
%ROWCOUNT,
6.2.1.1
,
6.2.2.6
explicit,
6.2.2.6
implicit,
6.2.1.1
links to examples,
13
syntax,
13
values after OPEN, FETCH, and CLOSE,
6.2.2.6
cursor declarations
links to examples,
13
syntax,
13
cursor expressions
examples,
6.6.2
REF CURSORs,
6.6.3
restrictions,
6.6.1
using,
6.6
cursor FOR loops
passing parameters to,
6.4.2
cursor subqueries
using,
6.6
cursor variables,
6.5
advantages of,
6.5.2
as parameters to table functions,
11.10.8
avoiding errors with,
6.5.6
closing,
6.5.4.4
declaring,
6.5.3
defining,
6.5.3
fetching from,
6.5.4.3
links to examples,
13
opening,
6.5.4.1
passing as parameters,
6.5.3.1
reducing network traffic,
6.5.5
restrictions,
6.5.7
syntax,
13
using as a host variable,
6.5.4.2
CURSOR_ALREADY_OPEN exception,
10.3
cursors
advantages of using cursor variables,
6.5.2
attributes of explicit,
6.2.2.6
attributes of implicit,
6.2.1.1
closing explicit,
6.2.2.5
declaring explicit,
6.2.2.1
definition,
1.2.10.1
explicit,
1.2.10.1
,
6.2.2
explicit FOR loops,
6.3.6
expressions,
6.6
fetching from,
6.2.2.3
guidelines for implicit,
6.2.1.2
implicit,
1.2.10.1
opening explicit,
6.2.2.2
packaged,
9.9
parameterized,
6.4.2
REF CURSOR variables,
6.5
RETURN clause,
9.9
scope rules for explicit,
6.2.2.1
SYS_REFCURSOR type,
11.10.8
variables,
6.5
D
data abstraction
understanding PL/SQL,
1.2.10
database character set,
3.1.3
database triggers,
1.3.1.3
autonomous,
6.8.4
datatypes
BFILE,
3.1.4.1
BINARY_INTEGER,
3.1.1.1
BLOB,
3.1.4.2
BOOLEAN,
3.1.5.1
CHAR,
3.1.2.1
CLOB,
3.1.4.3
DATE,
3.1.6.1
explicit conversion,
3.3.1
families,
3.1
implicit conversion,
3.3.2
INTERVAL DAY TO SECOND,
3.1.6.6
INTERVAL YEAR TO MONTH,
3.1.6.5
LONG,
3.1.2.2
LONG RAW,
3.1.2.2
national character,
3.1.3
NCHAR,
3.1.3.2
NCLOB,
3.1.4.4
NUMBER,
3.1.1.3
NVARCHAR2,
3.1.3.3
PLS_INTEGER,
3.1.1.4
PL/SQL,
3
RAW,
3.1.2.3
RECORD,
5.1
REF CURSOR,
6.5.1
ROWID,
3.1.2.4
scalar versus composite,
3.1
TABLE,
5.3
TIMESTAMP,
3.1.6.2
TIMESTAMP WITH LOCAL TIME ZONE,
3.1.6.4
TIMESTAMP WITH TIME ZONE,
3.1.6.3
UROWID,
3.1.2.4
VARCHAR2,
3.1.2.5
VARRAY,
5.1.1.2
,
5.3
DATE datatype,
3.1.6.1
dates
converting,
3.3.4
TO_CHAR default format,
3.3.4
datetime
arithmetic,
3.1.7
datatypes,
3.1.6
literals,
2.1.3.5
DAY
datatype field,
3.1.6
DBMS_ALERT package,
9.7.1
DBMS_DB_VERSION package
using with conditional compilation,
2.7.1.8
DBMS_DDL package
functions for hiding PL/SQL source code,
A
limitations,
A.1.2
obfuscating PL/SQL code,
A
using,
A.3.1
wrap functions,
A.3
wrapping PL/SQL code,
A
DBMS_OUTPUT package
displaying output,
1.2.9
displaying output from PL/SQL,
9.7.2
using PUT_LINE to display output,
1.2.2.2
DBMS_PIPE package,
9.7.3
DBMS_PREPROCESSOR package
using with conditional compilation,
2.7.2.2
DBMS_PROFILE package
gathering statistics for tuning,
11.4.1
DBMS_SQL package,
7.1
PARSE procedure,
8.9.4
replace with dynamic SQL,
7.1
upgrade to dynamic SQL,
11.7
using for a query,
7.1
DBMS_TRACE package
tracing code for tuning,
11.4.2
DBMS_WARNING package
controlling warning messages in PL/SQL,
10.9.3
dbmsupbin.sql script
interpreted compilation,
11.9.9
dbmsupgnv.sql script
for PL/SQL native compilation,
11.9.9
deadlocks
how handled by PL/SQL,
6.7.4
DEC
NUMBER subtype,
3.1.1.3.1
DECIMAL
NUMBER subtype,
3.1.1.3.1
declarations
collection,
5.3.1
constants,
1.2.2.4
,
2.2.1
cursor variables,
6.5.3
exceptions in PL/SQL,
10.4.1
explicit cursor,
6.2.2.1
object in a PL/SQL block,
12.1.1
PL/SQL functions,
1.2.4
PL/SQL procedures,
1.2.4
PL/SQL subprograms,
1.2.4
PL/SQL variables,
1.2.5
restrictions,
2.2.6
subprograms,
8.5
using %ROWTYPE,
2.2.5
using %TYPE attribute,
2.2.4
using DEFAULT,
2.2.2
using NOT NULL constraint,
2.2.3
variables,
1.2.2.1
,
2.2
declarative part
of function,
8.4
of PL/SQL block,
1.2.1
of procedure,
8.3
DECLARE
block structure,
1.2.1
for local declarations,
8.3
start of declarative part of a PL/SQL block,
13
syntax,
13
DECODE function
treatment of nulls,
2.6.4.1
DEFAULT keyword
for assignments,
2.2.2
DEFAULT option
FUNCTION,
13
RESTRICT_REFERENCES,
13
default parameter values,
8.6.4
default value
effect on %ROWTYPE declaration,
2.2.5
effect on %TYPE declaration,
2.2.4
DEFINE
limitations of use with wrap utility,
A.1.2.1
definer's rights
privileges on subprograms,
8.9
DELETE method
collections,
5.8.8
,
13
DELETE statement
links to examples,
13
syntax,
13
delimiters,
2.1.1
demos
PL/SQL,
Preface
dense collections
arrays versus nested tables,
5.1.1.1
DETERMINISTIC hint
function syntax,
13
use with functions,
8.4
digits of precision,
3.1.1.3
displaying output
from PL/SQL,
1.2.2.2
setting SERVEROUTPUT,
1.2.9
,
9.7.2
using DBMS_OUTPUT.PUT_LINE,
1.2.2.2
with DBMS_OUTPUT,
1.2.9
DISTINCT row operator,
6.1.3
,
6.1.5
dot notation,
1.2.5.1
for collection methods,
5.8
for global variables,
4.3.6.3
for object attributes in PL/SQL,
12.2.1
for object methods in PL/SQL,
12.2.2
for package contents,
9.3.1
DOUBLE PRECISION
NUMBER subtype,
3.1.1.3.1
DUP_VAL_ON_INDEX exception,
10.3
dynamic dispatch,
8.8.1
dynamic SQL,
1.1.1
,
7.1
building a dynamic multi-row query,
7.4.1
guidelines,
7.4
improving performance,
7.4.3
passing nulls,
7.4.7
passing object names as parameters,
7.4.4
RETURNING clause,
7.2
specifying parameter modes,
7.2.1
tips and traps,
7.4
tuning,
11.7
using bulk SQL,
7.3
USING clause,
7.2
using cursor attributes,
7.4.6
using database links,
7.4.8
using duplicate placeholders,
7.4.5
using EXECUTE IMMEDIATE statement,
7.2
using invoker rights,
7.4.9
using RESTRICT_REFERENCES,
7.4.10
using the semicolon,
7.4.2
dynamic string length
with OPEN FOR statement,
13
dynamic wrapping
DBMS_DDL package,
A
new features,
Preface
E
element types
collection,
5.3
ELSE clause
using,
4.2.2
ELSIF clause
using,
4.2.3
END
block structure,
1.2.1
end of a PL/SQL block,
13
end of executable part,
8.3
syntax,
13
END IF
end of IF statement,
4.2.1
END LOOP
end of LOOP statement,
4.3.5
error handling
in PL/SQL,
10
overview,
1.2.11
error messages
maximum length,
10.8.4
ERROR_CODE
BULK_EXCEPTIONS cursor attribute field,
11.5.1.3
using with SQLERRM,
11.5.1.3
ERROR_INDEX
BULK_EXCEPTIONS cursor attribute field,
11.5.1.3
evaluation
short-circuit,
2.6.1.2
EXCEPTION
block structure,
1.2.1
exception handling part,
8.3
exception-handling part of a block,
13
syntax in PL/SQL block,
13
exception definition
syntax,
13
exception handlers
OTHERS handler,
10.1
overview,
1.2.11
using RAISE statement in,
10.7
,
10.8
using SQLCODE function in,
10.8.4
using SQLERRM function in,
10.8.4
WHEN clause,
10.8
EXCEPTION_INIT pragma
links to examples,
13
syntax,
13
using with RAISE_APPLICATION_ERROR,
10.4.4
with exceptions,
10.4.3
exception-handling part
of function,
8.4
of PL/SQL block,
1.2.1
of procedure,
8.3
exceptions
advantages of PL/SQL,
10.2
branching with GOTO,
10.8.3
catching unhandled in PL/SQL,
10.8.5
continuing after an exception is raised,
10.8.6.1
controlling warning messages,
10.9.2
declaring in PL/SQL,
10.4.1
definition,
13
handling in PL/SQL,
10
links to examples,
13
list of predefined in PL/SQL,
10.3
locator variables to identify exception locations,
10.8.6.3
OTHERS handler in PL/SQL,
10.8
PL/SQL compile-time warnings,
10.9
PL/SQL error condition,
10.1
PL/SQL warning messages,
10.9.1
predefined in PL/SQL,
10.3
propagation in PL/SQL,
10.6
raise_application_error procedure,
10.4.4
raised in a PL/SQL declaration,
10.8.1
raised in handlers,
10.8.2
raising in PL/SQL,
10.5
raising predefined explicitly,
10.5.1
raising with RAISE statement,
10.5.1
redeclaring predefined in PL/SQL,
10.4.5
reraising in PL/SQL,
10.7
retrying a transaction after,
10.8.6.2
scope rules in PL/SQL,
10.4.2
tips for handling PL/SQL errors,
10.8.6
user-defined in PL/SQL,
10.4
using EXCEPTION_INIT pragma,
10.4.3
using SQLCODE,
10.8.4
using SQLERRM,
10.8.4
using the DBMS_WARNING package,
10.9.3
using WHEN and OR,
10.8
WHEN clause,
10.8
executable part
of function,
8.4
of PL/SQL block,
1.2.1
of procedure,
8.3
EXECUTE IMMEDIATE statement,
7.2
links to examples,
13
syntax,
13
EXECUTE privilege
subprograms,
8.9.6
EXISTS method
collections,
5.8.1
,
13
EXIT statement
early exit of LOOP,
4.3.6.4
links to examples,
13
stopping a loop,
4.3.1
syntax,
13
using,
4.3.2
where allowed,
4.3.1
EXIT-WHEN statement
overview,
1.2.6.2
using,
4.3.3
explicit cursors,
6.2.2
explicit datatype conversion,
3.3.1
explicit declarations
cursor FOR loop record,
6.3.6
expressions
Boolean,
2.6.2
CASE,
2.6.3
,
2.6.3
examples,
13
PL/SQL,
2.6
static,
2.7.1.6
syntax,
13
EXTEND method
collections,
5.8.6
,
13
extended rowids,
3.1.2.4.1
external
references,
8.9.2
routines,
8.11
subprograms,
8.11
F
FALSE value,
2.1.3.4
features, new,
Preface
FETCH statement
links to examples,
13
syntax,
13
using explicit cursors,
6.2.2.3
with cursor variable,
6.5.4.3
fetching
across commits,
6.7.7
bulk,
11.5.2.1
,
11.5.2.1
fields
of records,
5.1
file I/O,
9.7.5
FIRST method
collections,
5.8.4
,
13
FLOAT
NUMBER subtype,
3.1.1.3.1
FOR loops
explicit cursors,
6.3.6
nested,
4.3.6.3
FOR UPDATE clause,
6.2.2.2
when to use,
6.7.7
FORALL statement
links to examples,
13
syntax,
13
using,
11.5.1
using to improve performance,
11.5.1
using with BULK COLLECT clause,
11.5.2.4
with rollbacks,
11.5.1.1
FOR-LOOP statement
syntax,
13
using,
4.3.6
formal parameters,
6.4.2
format
masks,
3.3.4
forward
declarations of subprograms,
8.5
references,
2.2.6
FOUND cursor attribute
explicit,
6.2.2.6
implicit,
6.2.1.1
FUNCTION
with CREATE statement,
1.2.8.1
,
1.3.1.2
,
8.1
function declaration
syntax,
13
functions
body,
8.4
,
13
built-in,
2.9
calling,
8.4
creating,
1.2.8.1
,
1.3.1.2
,
8.1
declaration,
13
DETERMINISTIC hint,
8.4
in PL/SQL,
8
links to examples,
13
PARALLEL_ENABLE option,
8.4
parameters,
8.4
parts,
8.4
pipelined,
11.10
RETURN clause,
8.4
RETURN statement,
8.4.1
specification,
8.4
table,
11.10.1
understanding,
8.4
G
GOTO statement
branching into or out of exception handler,
10.8.3
label,
4.4.1
links to examples,
13
overview,
1.2.6.3
restrictions,
4.4.1.1
syntax,
13
using,
4.4.1
GROUP BY clause,
6.1.3
H
handlers
exception in PL/SQL,
10.1
handling errors
PL/SQL,
10
handling exceptions
PL/SQL,
10
raised in as PL/SQL declaration,
10.8.1
raised in handler,
10.8.2
using OTHERS handler,
10.8
handling of nulls,
2.6.4
hash tables
simulating with associative arrays,
5.2
hiding PL/SQL code,
A
host arrays
bulk binds,
11.5.2.5
HOUR
datatype field,
3.1.6
HTF package,
9.7.4
HTP package,
9.7.4
hypertext markup language (HTML),
9.7.6
hypertext transfer protocol (HTTP),
1.1.6
UTL_HTTP package,
9.7.6
I
identifiers
forming,
2.1.2
maximum length,
2.1.2
quoted,
2.1.2.3
scope rules,
2.4
IF statement,
4.2
ELSE clause,
4.2.2
links to examples,
13
syntax,
13
using,
4.2.1
IF-THEN statement
using,
4.2.1
IF-THEN-ELSE statement
overview,
1.2.6.1
using,
4.2.2
IF-THEN-ELSEIF statement
using,
4.2.3
IMMEDIATE
with EXECUTE statement,
7.2
implicit cursors
attributes,
6.2.1.1
guidelines,
6.2.1.2
implicit datatype conversion,
3.3.2
implicit datatype conversions
performance,
11.3.1.6
implicit declarations
FOR loop counter,
4.3.6.3
IN comparison operator,
2.6.1.8
IN OUT parameter mode
subprograms,
8.6.3.3
IN parameter mode
subprograms,
8.6.3.1
INDEX BY
collection definition,
13
index-by tables
See
associative arrays
INDICES OF clause
FORALL,
13
with FORALL,
11.5.1
infinite loops,
4.3.1
inheritance
and overloading,
8.8.1
initialization
collections,
5.4
objects in PL/SQL,
12.1.2
package,
9.4
using DEFAULT,
2.2.2
variable,
2.5
with NOT NULL constraint,
2.2.3
initialization parameters
PL/SQL compilation,
11.1
INSERT statement
links to examples,
13
syntax,
13
with a record variable,
5.11.2
INT
NUMBER subtype,
3.1.1.3.1
INTEGER
NUMBER subtype,
3.1.1.3.1
integer
BINARY,
3.1.1.1
PLS datatype,
3.1.1.4
inter-language calls,
8.11
interpreted compilation
dbmsupbin.sql script,
11.9.9
recompiling all PL/SQL modules,
11.9.9
INTERSECT set operator,
6.1.5
interval
arithmetic,
3.1.7
INTERVAL DAY TO SECOND datatype,
3.1.6.6
INTERVAL YEAR TO MONTH datatype,
3.1.6.5
intervals
datatypes,
3.1.6
INTO
SELECT INTO statement,
13
INTO clause
with FETCH statement,
6.5.4.3
INTO list
using with explicit cursors,
6.2.2.3
INVALID_CURSOR exception,
10.3
INVALID_NUMBER exception,
10.3
invoker's rights
advantages,
8.9.1
privileges on subprograms,
8.9
IS
defining a procedure,
8.3
defining a procedure body,
8.3
IS A SET operator,
5.6
IS EMPTY operator,
5.6
IS NULL comparison operator,
2.6.1.5
expressions,
13
ISOLATION LEVEL parameter
READ COMMITTED,
13
SERIALIZABLE,
13
setting transactions,
13
ISOPEN cursor attribute
explicit,
6.2.2.6
implicit,
6.2.1.1
J
JAVA
use for calling external subprograms,
8.11
Java
call specs,
8.11
Java stored procedures
calling from PL/SQL,
8.11
JDeveloper with PL/SQL
Oracle By Example tutorial,
Preface
K
keywords
in PL/SQL,
D
list of PL/SQL,
D
use in PL/SQL,
2.1.2.1
L
labels
block,
2.4
block structure,
13
exiting loops,
4.3.4
GOTO statement,
4.4.1
loops,
4.3.4
syntax,
13
LAG
SQL analytic function,
2.9
LANGUAGE
use for calling external subprograms,
8.11
language elements
of PL/SQL,
13
large object (LOB) datatypes,
3.1.4
LAST method
collections,
5.8.4
,
13
LEVEL
pseudocolumn,
6.1.4
LEVEL parameter
with ISOLATION to set transactions,
13
lexical units
PL/SQL,
2.1
LIKE comparison operator,
2.6.1.6
expressions,
13
LIMIT clause
FETCH,
13
using to limit rows for a Bulk FETCH operation,
11.5.2.2
LIMIT method
collections,
5.8.3
,
13
limitations
DBMS_DDL package,
A.1.2
native compilation,
11.9.3.3
of PL/SQL programs,
C
PL/SQL compiler,
C
wrap utility,
A.1.2
limits
on PL/SQL programs,
C
literals
Boolean,
2.1.3.4
character,
2.1.3.2
datetime,
2.1.3.5
examples,
13
NCHAR string,
2.1.3.3
NUMBER datatype,
2.1.3.1
numeric,
2.1.3.1
numeric datatypes,
2.1.3.1
string,
2.1.3.3
syntax,
13
types of PL/SQL,
2.1.3
LOB (large object) datatypes,
3.1.4
lob locators,
3.1.4
local subprograms,
1.3.1.2
locator variables
used with exceptions,
10.8.6.3
LOCK TABLE statement
examples,
13
locking a table,
6.7.7
syntax,
13
locks
modes,
6.7
overriding,
6.7.7
transaction processing,
6.7
using FOR UPDATE clause,
6.7.7
logical operators,
2.6.1
logical rowids,
3.1.2.4
LOGIN_DENIED exception,
10.3
LONG datatype,
3.1.2.2
maximum length,
3.1.2.2
restrictions,
3.1.2.2
supported only for backward compatibility,
3.1.2.2
LONG RAW datatype,
3.1.2.2
converting,
3.3.5
maximum length,
3.1.2.2
supported only for backward compatibility,
3.1.2.2
LOOP statement
links to examples,
13
overview,
1.2.6.2
syntax,
13
using,
4.3.1
loops
counters,
4.3.6
dynamic ranges,
4.3.6.2
exiting using labels,
4.3.4
implicit declaration of counter,
4.3.6.3
iteration,
4.3.6.1
labels,
4.3.4
reversing the counter,
4.3.6
scope of counter,
4.3.6.3
M
maximum precision,
3.1.1.3
maximum size
CHAR value,
3.1.2.1
identifier,
2.1.2
LOB,
3.1.4
LONG RAW value,
3.1.2.2
LONG value,
3.1.2.2
NCHAR value,
3.1.3.2
NVARCHAR2 value,
3.1.3.3
Oracle error message,
10.8.4
RAW value,
3.1.2.3
VARCHAR2 value,
3.1.2.5
MEMBER OF operator,
5.6
membership test,
2.6.1.8
memory
avoid excessive overhead,
11.3.2
MERGE statement
syntax,
13
methods
collection,
5.8
MINUS set operator,
6.1.5
MINUTE
datatype field,
3.1.6
modularity,
1.2.8
packages,
9.2
MONTH
datatype field,
3.1.6
multilevel collections
using,
5.7
multi-line comments,
2.1.4.2
MULTISET EXCEPT operator,
5.5
MULTISET INTERSECT operator,
5.5
MULTISET UNION operator,
5.5
N
NAME
for calling external subprograms,
8.11
NAME parameter
setting transactions,
13
transactions,
6.7.6
name resolution,
2.3
differences between PL/SQL and SQL,
B.3
global and local variables,
B.1
inner capture in DML statements,
B.5
overriding in subprograms,
8.9.5
qualified names and dot notation,
B.2
qualifying references to attributes and methods,
B.6
understanding,
B.1
understanding capture,
B.4
with synonyms,
8.9.5
names
explicit cursor,
6.2.2.1
qualified,
2.3
savepoint,
6.7.3
variable,
2.3
naming conventions
PL/SQL,
2.3
national character datatypes,
3.1.3
national character set,
3.1.3
National Language Support (NLS),
3.1.3
native compilation
and Real Application Clusters,
11.9.3.2
certified compilers in installation guides,
11.9.1
creating databases for,
11.9.8
dbmsupgnv.sql script,
11.9.9
dependencies,
11.9.3.1
enhancements,
Preface
how it works,
11.9.3
initialization parameters,
11.9.5
invalidation,
11.9.3.1
limitations,
11.9.3.3
modifying databases for,
11.9.9
new and updated components,
Preface
Oracle Metalink,
11.9
Oracle Technology Network,
11.9
performance gains,
11.9.2
PL/SQL code,
11.9
plsql_code_type column,
11.9.7
prerequirements,
11.9.1
revalidation,
11.9.3.1
setting up,
11.9.7
setting up databases,
11.9.8
setting up library subdirectories,
11.9.6
shared libraries,
11.9.3
spnc_commands file,
11.9.4
testing,
11.9.1
understanding,
11.9.1
using,
11.9.7
utlirp.sql script,
11.9.7
utlrp.sql script,
11.9.9
native dynamic SQL. See dynamic SQL
native execution
compiling PL/SQL procedures for,
11.9
NATURAL
BINARY_INTEGER subtype,
3.1.1.1.1
NATURAL and NATURALN subtypes,
3.1.1.1.1
NATURALN
BINARY_INTEGER subtype,
3.1.1.1.1
NCHAR datatype,
3.1.3.2
NCLOB datatype,
3.1.4.4
nested collections,
5.7
nested cursors
using,
6.6
nested tables
manipulating in PL/SQL,
12.4
sets of values,
5.1.1.1
syntax,
13
transforming with operators,
5.5
understanding,
5.1.1
versus associative arrays,
5.2.1
versus varrays,
5.2.2
nesting
block,
1.2.1
,
1.2.1
FOR loops,
4.3.6.3
record,
5.10
new features,
Preface
PL/SQL information on Oracle Technology Network,
Preface
NEXT method
collections,
5.8.5
,
13
NEXTVAL
pseudocolumn,
6.1.4
nibble
half a byte,
3.3.5
NLS (National Language Support),
3.1.3
NLS_LENGTH_SEMANTICS initialization parameter
use with conditional compilation,
2.7.1.5
NO COPY hint
FUNCTION,
13
NO_DATA_FOUND exception,
10.3
NOCOPY compiler hint
for tuning,
11.8
restrictions on,
11.8.1
non-blank-padding semantics,
3.4.2
NOT logical operator
treatment of nulls,
2.6.4.1
NOT NULL
declaration,
13
NOT NULL constraint
effect on %ROWTYPE declaration,
2.2.5
effect on %TYPE declaration,
2.2.4
restriction on explicit cursors,
6.2.2.1
using in collection declaration,
5.3.1
using in variable declaration,
2.2.3
NOT NULL option
record definition,
13
NOT_LOGGED_ON exception,
10.3
notation
positional versus named,
8.6.2
NOTFOUND cursor attribute
explicit,
6.2.2.6
implicit,
6.2.1.1
NOWAIT option
LOCK TABLE,
13
NOWAIT parameter
using with FOR UPDATE,
6.7.7
NVARCHAR2 datatype,
3.1.3.3
null handling,
2.6.4
in dynamic SQL,
7.4.7
NULL statement
links to examples,
13
syntax,
13
using,
4.4.2
using in a procedure,
8.3
NUMBER datatype,
3.1.1.3
range of literals,
2.1.3.1
range of values,
3.1.1.3
NUMBER subtypes,
3.1.1.3.1
NUMERIC
NUMBER subtype,
3.1.1.3.1
numeric literals,
2.1.3.1
PL/SQL datatypes,
2.1.3.1
NVL function
treatment of nulls,
2.6.4.1
O
obfuscating
PL/SQL code,
A
understanding,
A.1
obfuscation
CREATE_WRAPPED function,
A.3
DBMS_DDL package,
A.3
hiding PL/SQL code,
A
limitations,
A.1.2
recommendations,
A.1.1
tips,
A.1.1
understanding,
A.1
using DBMS_DDL CREATE_WRAPPED function,
A.3.1
viewing source,
A.1
,
A.3.1
WRAP function,
A.3
wrap utility,
A.2
object constructors
calling in PL/SQL,
12.2.2
passing parameters to in PL/SQL,
12.2.2
object methods
calling in PL/SQL,
12.2.2
object type declaration
syntax,
13
object types
declaring in a PL/SQL block,
12.1.1
defining in PL/SQL,
12.1
initializing in PL/SQL,
12.1
,
12.1.2
overview,
1.2.10.4
using with invoker's rights subprograms,
8.9.10
using with PL/SQL,
12
using with PL/SQL collections,
12.4
object-oriented programming
with PL/SQL,
12
ONLY parameter
with READ to set transactions,
13
on-the-fly wrapping
DBMS_DDL package,
A
new features,
Preface
OPEN FOR statement
dynamic string length,
13
OPEN statement
explicit cursors,
6.2.2.2
links to examples,
13
syntax,
13
OPEN-FOR statement,
6.5.4.1
links to examples,
13
syntax,
13
OPEN-FOR-USING statement
syntax,
13
operators
comparison,
2.6.1.3
logical,
2.6.1
precedence,
2.6
relational,
2.6.1.4
optimizing
PL/SQL programs,
11.2
OR keyword
using with EXCEPTION,
10.8
Oracle By Example
PL/SQL new features,
Preface
Oracle Metalink
native compilation,
11.9
spnc_commands,
11.9.4
Oracle Technology Network
best practices,
1
,
11
code samples,
Preface
,
1
information on PL/SQL new features,
Preface
native compilation,
11.9
SQL injection,
7.4
,
13
table function examples,
11.10
table functions,
11.10
tuning information,
11
order of evaluation,
2.6
,
2.6.1.1
OTHERS clause
exception handling,
13
OTHERS exception handler,
10.1
,
10.8
OTN
technical articles,
Preface
OUT parameter mode
subprograms,
8.6.3.2
overloading
and inheritance,
8.8.1
guidelines,
8.7.1
packaged subprograms,
9.5
restrictions,
8.7.2
subprogram names,
8.7
P
PACKAGE
with SQL CREATE statement,
1.2.8.2
,
9.1
PACKAGE BODY
with SQL CREATE statement,
1.2.8.2
,
9.1
package declaration
syntax,
13
packaged cursors,
9.9
packaged subprograms,
1.3.1.2
packages
advantages,
9.2
bodiless,
9.3
body,
9.1
,
9.4
,
13
call specification,
9.1
calling subprograms,
9.3.1
contents of,
9.1.1
creating,
1.2.8.2
,
9.1
cursor specifications,
9.9
cursors,
9.9
declaration,
13
dot notation,
9.3.1
examples of features,
9.5
global variables,
9.5
guidelines for writing,
9.8
hidden declarations,
9.1
initializing,
9.4
links to examples,
13
modularity,
9.2
overloading subprograms,
9.5
overview,
1.2.8.2
overview of Oracle supplied,
9.7
private versus public objects,
9.5.1
product-specific,
9.7
product-specific for use with PL/SQL,
1.1.6
referencing,
9.3.1
restrictions on referencing,
9.3.1.1
scope,
9.3
serially reusable,
13
specification,
9.1
,
13
specifications,
9.3
STANDARD package,
9.6
understanding,
9.1
visibility of contents,
9.1
PARALLE_ENABLE option
FUNCTION,
13
PARALLEL_ENABLE option
use with functions,
8.4
parameter passing
by reference,
8.13
by value,
8.13
in dynamic SQL,
7.2.1
parameters
actual,
6.4.2
actual versus formal,
8.6.1
aliasing,
8.13
cursor,
6.4.2
default values,
8.6.4
formal,
6.4.2
IN mode,
8.6.3.1
IN OUT mode,
8.6.3.3
in PL/SQL subprograms,
8.3
modes,
8.6.3
OUT mode,
8.6.3.2
summary of modes,
8.6.3.4
parentheses,
2.6
pattern matching,
2.6.1.6
performance
avoid memory overhead,
11.3.2
avoiding problems,
11.3
improving with PL/SQL,
1.1.2
performance. See also tuning
physical rowids,
3.1.2.4
pipe,
9.7.3
PIPE ROW statement
for returning rows incrementally,
11.10.4
PIPELINED
function option,
11.10.2
,
13
pipelined functions
exception handling,
11.10.11
fetching from results of,
11.10.7
for querying a table,
11.10.1
overview,
11.10.1
passing data with cursor variables,
11.10.8
performing DML operations inside,
11.10.9
performing DML operations on,
11.10.10
returning results from,
11.10.4
transformation of data,
11.10
transformations,
11.10.3
writing,
11.10.2
pipelines
between table functions,
11.10.5
returning results from table functions,
11.10.4
support collection types,
11.10.2
using table functions,
11.10.3
writing table functions,
11.10.2
pipelining
definition,
11.10.1
placeholders
duplicate,
7.4.5
PLS_INTEGER datatype,
3.1.1.4
compared to BINARY_INTEGER,
Preface
new features,
Preface
overflow condition,
3.1.1.4
range update,
Preface
PL/SQL
advantages,
1.1
anonymous blocks,
1.2.1
architecture,
1.3
assigning Boolean values,
2.5.1
assigning query result to variable,
2.5.2
assigning values to variables,
2.5
best practices information on OTN,
Preface
blocks,
13
structure,
1.2.1
CASE expressions,
2.6.3
character sets,
2.1
collection types,
5
collections
overview,
1.2.10.2
comments,
2.1.4
comparisons,
2.6
compiler limitations,
C
compiler parameter settings,
11.1
,
11.2
,
11.9.7
compiler parameters,
11.1
compile-time warnings,
10.9
compiling PL/SQL code for native execution,
11.9
conditional compilation,
1.2.7
,
2.7
constants,
1.2.2
control structures,
1.2.6
,
4.1
creating Web applications and pages,
2.8
data abstraction,
1.2.10
datatypes,
3
debugging with JDeveloper tutorial on Oracle by Example,
Preface
declarations
constants,
2.2
demos,
Preface
displaying output,
1.2.2.2
,
9.7.2
engine,
1.3
environment,
9.6
error handling
overview,
1.2.11
errors,
10
exceptions,
10
expressions,
2.6
functions,
2.9
,
8
hiding or obfuscating source code,
A
input data,
1.2.9
keywords,
D
lexical units,
2.1
limitations of programs,
C
limits on programs,
C
literals,
2.1.3
logical operators,
2.6.1
name resolution,
B.1
naming conventions,
2.3
new features,
Preface
Oracle By Example tutorial,
Preface
output data,
1.2.9
performance
advantage,
1.1.2
performance problems,
11.3
portability,
1.1.4
procedural aspects,
1.2
procedures,
8
profiling and tracing programs,
11.4
querying data,
6.3
recompiling,
10.9.2
records
overview,
1.2.10.3
reserved words,
D
sample programs,
Preface
scope of identifiers,
2.4
Server Pages (PSPs),
2.8.2
SQL support,
6.1
statements,
13
subprograms,
8
calling,
1.3.1.2
syntax of language elements,
13
transaction processing,
6.7
tuning code,
11.2.1
tuning computation-intensive programs,
11.6
tuning dynamic SQL programs,
11.7
using dynamic SQL,
7.1
using NOCOPY for tuning,
11.8
using transformation pipelines,
11.10
variables,
1.2.2
warning messages,
10.9.1
Web applications,
2.8.1
writing reusable code,
1.2.8
PLSQL datatypes
numeric literals,
2.1.3.1
PLSQL_CCFLAGS initialization parameter
conditional compilation,
2.7.1.7
,
2.7.1.7
use with conditional compilation,
2.7.1.5
plsql_code_type column
native compilation,
11.9.7
PLSQL_CODE_TYPE initialization parameter
setting PL/SQL native compilation,
11.9.5.3
use with conditional compilation,
2.7.1.5
PLSQL_DEBUG initialization parameter
use with conditional compilation,
2.7.1.5
PLSQL_LINE flag
use with conditional compilation,
2.7.1.5
PLSQL_NATIVE_LIBRARY_DIR initialization parameter
for native compilation,
11.9.5.1
Real Application Clusters with native compilation,
11.9.3.2
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT initialization parameter
for native compilation,
11.9.5.2
PLSQL_OPTIMIZE_LEVEL initialization parameter
optimizing PL/SQL programs,
11.2
use with conditional compilation,
2.7.1.5
PLSQL_UNIT flag
use with conditional compilation,
2.7.1.5
PLSQL_WARNINGS initialization parameter,
10.9
use with conditional compilation,
2.7.1.5
pointers
REF CIRSOR,
6.5.1
portability,
1.1.4
POSITIVE
BINARY_INTEGER subtype,
3.1.1.1.1
POSITIVE and POSITIVEN subtypes,
3.1.1.1.1
POSITIVEN
BINARY_INTEGER subtype,
3.1.1.1.1
PRAGMA
compiler directive with AUTONOMOUS_TRANSACTION,
13
,
13
compiler directive with AUTONOMOUS_TRANSACTION for procedures,
13
compiler directive with EXCEPTION_INIT,
13
compiler directive with RESTRICT_REFERENCES,
13
compiler directive with SERIALLY_REUSABLE,
13
,
13
pragmas
AUTONOMOUS_TRANSACTION,
6.8.2
compiler directives,
10.4.3
EXCEPTION_INIT,
10.4.3
RESTRICT_REFERENCES,
6.8.5
,
7.4.10
,
8.12
precedence, operator,
2.6
precision of digits
specifying,
3.1.1.3
predefined exceptions
raising explicitly,
10.5.1
redeclaring,
10.4.5
predicates,
6.1.5
PRIOR method
collections,
5.8.5
,
13
PRIOR row operator,
6.1.4
private objects
packages,
9.5.1
PROCEDURE
with CREATE statement,
1.2.8.1
,
1.3.1.2
,
8.1
procedure declaration
syntax,
13
procedures
body,
8.3
,
13
calling,
1.2.8.2
,
8.3
creating,
1.2.8.1
,
1.3.1.2
,
8.1
declaration,
13
in PL/SQL,
8
links to examples,
13
parts,
8.3
recompiling with ALTER PROCEDURE,
10.9.3
specification,
8.3
understanding PL/SQL,
8.3
productivity,
1.1.3
program units,
1.2.8
PROGRAM_ERROR exception,
10.3
propagation
exceptions in PL/SQL,
10.6
pseudocolumns
CURRVAL,
6.1.4
LEVEL,
6.1.4
NEXTVAL,
6.1.4
ROWID,
6.1.4
ROWNUM,
6.1.4
SQL,
6.1.4
UROWID,
6.1.4
use in PL/SQL,
6.1.4
public objects
packages,
9.5.1
purity rules,
8.12
PUT_LINE
displaying output with,
1.2.2.2
Q
qualifiers
using subprogram names as,
2.3
when needed,
2.3
,
2.4
query work areas,
6.5.1
querying data
BULK COLLECT clause,
6.3.2
cursor FOR loop,
6.3.3
implicit cursor FOR loop,
6.3.5
looping through multiple rows,
6.3.3
maintaining,
6.4.2
performing complicated processing,
6.3.4
SELECT INTO,
6.3.1
using explicit cursors,
6.3.4
using implicit cursors,
6.3.5
with PL/SQL,
6.3
work areas,
6.5.1
quoted identifiers,
2.1.2.3
R
RAISE statement
exceptions in PL/SQL,
10.5.1
links to examples,
13
syntax,
13
using in exception handler,
10.7
,
10.8
raise_application_error procedure
for raising PL/SQL exceptions,
10.4.4
raising an exception
in PL/SQL,
10.5
range operator,
4.3.6
RAW datatype,
3.1.2.3
converting,
3.3.5
maximum length,
3.1.2.3
READ ONLY parameter
setting transactions,
13
transactions,
6.7.6
READ WRITE parameter
setting transactions,
13
readability,
2.1
with NULL statement,
4.4.2
read-only transaction,
6.7.6
REAL
NUMBER subtype,
3.1.1.3.1
Real Application Clusters
and PL/SQL native compilation,
11.9.3.2
with native compilation,
11.9.3.2
recompiling
functions, packages, and procedures,
10.9.2
RECORD datatype,
5.1
record definition
syntax,
13
records
%ROWTYPE,
6.3.6
assigning values,
5.11
bulk-binding collections of,
5.11.5
comparing,
5.11.1
declaring,
5.10
defining,
5.10
definition,
1.2.5.2
,
13
group of fields,
5.1.2
group of related data items,
5.1
implicit declaration,
6.3.6
inserting,
5.11.2
links to examples,
13
manipulating,
5.10.1
nesting,
5.10
overview,
1.2.10.3
passing as parameters,
5.10.1
restriction on assignments,
5.11
restrictions on inserts and updates of,
5.11.4
returning into,
5.11.3
ROWTYPE attribute,
5.1.2
updating,
5.11.3
using as function return values,
5.10.1
recursion
using with PL/SQL subprograms,
8.10
REF CURSOR datatype,
6.5.1
cursor variables,
6.5
defining,
6.5.3
using with cursor subqueries,
6.6.3
REF CURSOR variables
as parameters to table functions,
11.10.8
predefined SYS_REFCURSOR type,
11.10.8
REF function
manipulating objects in PL/SQL,
12.2.4
reference datatypes,
3.1
references
external,
8.9.2
resolving external,
8.9.4
referencing
collections,
5.4
referencing elements
allowed subscript ranges,
5.4.1
regular expression functions
REGEXP_LIKE,
6.2.2.3
relational operators,
2.6.1.4
remote access indicator,
2.3
REPEAT UNTIL structure
PL/SQL equivalent,
4.3.5
REPLACE function
treatment of nulls,
2.6.4.1
reraising an exception,
10.7
reserved words
list of PL/SQL,
D
PL/SQL,
D
syntactic meaning in PL/SQL,
2.1.2.1
resolution
name,
2.3
references to names,
B.1
RESTRICT_REFERENCES pragma,
8.12
links to examples,
13
syntax,
13
using with autonomous functions,
6.8.5
using with dynamic SQL,
7.4.10
restricted rowids,
3.1.2.4.1
restrictions
cursor expressions,
6.6.1
cursor variables,
6.5.7
overloading subprograms,
8.7.2
result sets,
6.2.2.2
RETURN clause
cursor,
9.9
cursor declaration,
13
FUNCTION,
13
functions,
8.4
RETURN statement
functions,
8.4.1
links to examples,
13
syntax,
13
return types
overloading,
8.7.2
REF CURSOR,
6.5.3
return values
functions,
8.4
RETURNING clause
links to examples,
13
syntax,
13
with a record variable,
5.11.3
with dynamic SQL,
7.2
returns
bulk,
11.5.2.3
REUSE SETTINGS clause
with compiler parameters,
11.1
REVERSE
with LOOP counter,
4.3.6
REVERSE option
LOOP,
13
RNDS option
RESTRICT_REFERENCES,
13
RNPS option
RESTRICT_REFERENCES,
13
ROLLBACK statement,
6.7.2
effect on savepoints,
6.7.3
links to examples,
13
syntax,
13
rollbacks
implicit,
6.7.4
of FORALL statement,
11.5.1.1
routines
external,
8.11
row locks
with FOR UPDATE,
6.7.7
row operators,
6.1.5
ROWCOUNT cursor attribute
explicit,
6.2.2.6
implicit,
6.2.1.1
ROWID
pseudocolumn,
6.1.4
ROWID datatype,
3.1.2.4
rowids,
3.1.2.4
ROWIDTOCHAR function,
6.1.4
ROWNUM
pseudocolumn,
6.1.4
ROWTYPE attribute
declaring,
1.2.5.2
effect of default value,
2.2.5
effect of NOT NULL constraint,
2.2.5
inherited properties from columns,
13
links to examples,
13
records,
5.10
syntax,
13
using,
2.2.5
with SUBTYPE,
3.2.1
ROWTYPE_MISMATCH exception,
10.3
RPC (remote procedure call)
and exceptions,
10.6
rules
purity,
8.12
run-time errors
PL/SQL,
10
S
samples
PL/SQL,
Preface
SAVE EXCEPTIONS clause
FORALL,
13
SAVEPOINT statement,
6.7.3
links to examples,
13
syntax,
13
savepoints
reusing names,
6.7.3
scalar datatypes,
3.1
scale
specifying,
3.1.1.3
scientific notation,
2.1.3.1
scope,
2.4
collection,
5.3
definition,
2.4
exceptions in PL/SQL,
10.4.2
explicit cursor,
6.2.2.1
explicit cursor parameter,
6.2.2.1
identifier,
2.4
loop counter,
4.3.6.3
package,
9.3
searched CASE expression,
2.6.3.2
searched CASE statement,
4.2.4.1
SECOND
datatype field,
3.1.6
security
SQL injection,
7.4
,
13
SELECT INTO statement
links to examples,
13
returning one row,
6.3.1
syntax,
13
selector,
2.6.3.1
SELF_IS_NULL exception,
10.3
semantics
assignment,
3.4.1
blank-padding,
3.4.2
non-blank-padding,
3.4.2
string comparison,
3.4.2
separators,
2.1.1
sequences
CURRVAL and NEXTVAL,
6.1.4
SERIALLY_REUSABLE pragma
examples,
13
syntax,
13
use with packages,
13
Server Pages (PSPs)
PL/SQL,
2.8.2
SERVEROUTPUT
displaying output from PL/SQL,
1.2.9
setting ON to display output,
9.7.2
SET clause
UPDATE,
13
set operators,
6.1.5
SET TRANSACTION statement,
6.7.6
links to examples,
13
syntax,
13
short-circuit evaluation,
2.6.1.2
side effects,
8.6.3
controlling,
8.12
SIGNTYPE
BINARY_INTEGER subtype,
3.1.1.1.1
SIGNTYPE subtype,
3.1.1.1.1
simple CASE expression,
2.6.3.1
single-line comments,
2.1.4.1
size limit
varrays,
5.3
SMALLINT
NUMBER subtype,
3.1.1.3.1
spaces
where allowed,
2.1
sparse collections
nested tables versus arrays,
5.1.1.1
specification
call,
9.1
cursor,
9.9
functions,
8.4
package,
9.3
packages,
13
procedure,
8.3
spnc_commands
Oracle Metalink,
11.9.4
SQL
comparisons operators,
6.1.5
data manipulation operations,
6.1.1
define variables and data manipulation statements,
6.1.1
DML operations,
6.1.1
dynamic,
1.1.1
,
7.1
exceptions raised by data manipulation statements,
6.1.1
injection,
7.4
,
13
issuing from PL/SQL,
6.1
no rows returned with data manipulation statements,
6.1.1
pseudocolumns,
6.1.4
static,
1.1.1
SQL cursor
links to examples,
13
syntax,
13
SQL injection,
7.4
,
13
Oracle Technology Network,
7.4
,
13
SQLCODE function
links to examples,
13
syntax,
13
using with exception handlers,
10.8.4
SQLERRM function
links to examples,
13
syntax,
13
using with BULK_EXCEPTIONS ERROR_CODE field,
11.5.1.3
using with exception handlers,
10.8.4
standalone subprograms,
1.3.1.2
STANDARD package
defining PL/SQL environment,
9.6
START WITH clause,
6.1.4
statement terminator,
13
statements
assignment,
13
CASE,
13
CLOSE,
6.2.2.5
,
6.5.4.4
,
13
COMMIT,
13
DELETE,
13
EXECUTE IMMEDIATE,
7.2
,
13
EXIT,
13
FETCH,
6.2.2.3
,
6.5.4.3
,
13
FORALL,
11.5.1
,
13
FOR-LOOP,
13
GOTO,
13
IF,
13
INSERT,
13
LOCK TABLE,
13
LOOP,
13
MERGE,
13
NULL,
13
OPEN,
6.2.2.2
,
13
OPEN-FOR,
6.5.4.1
,
13
OPEN-FOR-USING,
13
PL/SQL,
13
RAISE,
13
RETURN,
13
ROLLBACK,
13
SAVEPOINT,
13
SELECT INTO,
13
SET TRANSACTION,
13
UPDATE,
13
WHILE-LOOP,
13
static constants
conditional compilation,
2.7.1.6.4
static expressions
boolean,
2.7.1.6
PLS_INTEGER,
2.7.1.6
use with conditional compilation,
2.7.1.6
VARCHAR2,
2.7.1.6
static SQL,
1.1.1
STEP clause
equivalent in PL/SQL,
4.3.6.1
STORAGE_ERROR exception,
10.3
raised with recursion,
8.10.1
store tables,
5.2.2
stored subprograms
in Oracle database,
1.3.1.2
string comparison semantics,
3.4.2
string literals,
2.1.3.3
NCHAR,
2.1.3.3
STRING subtype,
3.1.2.5.1
subprograms
actual versus formal parameters,
8.6.1
advantages in PL/SQL,
8.2
AUTHID clause,
8.9
,
8.9.2
calling external,
8.11
calling from SQL*Plus,
1.3.1.2
,
1.3.1.2
calling with parameters,
8.6.2
controlling side effects,
8.12
current user during execution,
8.9.3
declaring nested,
8.5
declaring PL/SQL,
1.2.4
default parameter modes,
8.6.4
definer's rights,
8.9
EXECUTE privilege,
8.9.6
external references,
8.9.2
granting privileges on invoker's rights,
8.9.6
guidelines for overloading,
8.7.1
how calls are resolved,
8.8
IN OUT parameter mode,
8.6.3.3
IN parameter mode,
8.6.3.1
in PL/SQL,
8
invoker's rights,
8.9
local,
1.3.1.2
mixed notation parameters,
8.6.2
named parameters,
8.6.2
OUT parameter mode,
8.6.3.2
overloading and inheritance,
8.8.1
overloading names,
8.7
overriding name resolution,
8.9.5
packaged,
1.3.1.2
parameter aliasing,
8.13
parameter modes,
8.6.3
,
8.6.3.4
passing parameter by value,
8.13
passing parameters,
8.6
passing parameters by reference,
8.13
positional parameters,
8.6.2
procedure versus function,
8.4
recursive,
8.10.1
resolving external references,
8.9.4
restrictions on overloading,
8.7.2
roles with invoker's rights,
8.9.7
standalone,
1.3.1.2
stored,
1.3.1.2
understanding PL/SQL,
8.1
using database links with invoker's rights,
8.9.9
using recursion,
8.10
using triggers with invoker's rights,
8.9.8
using views with invoker's rights,
8.9.8
subqueries
correlated,
6.4.1
using in PL/SQL,
6.4
SUBSCRIPT_BEYOND_COUNT exception,
10.3
SUBSCRIPT_OUTSIDE_LIMIT exception,
10.3
substitutability of object types
with overloading,
8.8.1
SUBSTR function
using with SQLERRM,
10.8.4
subtypes
BINARY_INTEGER,
3.1.1.1.1
CHARACTER,
3.1.2.1.1
compatibility,
3.2.2.1
constrained versus unconstrained,
3.2
defining,
3.2.1
NATURAL and NATUARALN,
3.1.1.1.1
NUMBER,
3.1.1.3.1
PL/SQL,
3.2
POSITIVE and POSITIVEN,
3.1.1.1.1
SIGNTYPE,
3.1.1.1.1
STRING,
3.1.2.5.1
using,
3.2.2
VARCHAR,
3.1.2.5.1
synonyms
name resolution,
8.9.5
syntax
assignment statement,
13
AUTONOMOUS_TRANSACTION pragma,
13
BEGIN,
13
block declaration,
13
CASE statement,
13
CLOSE statement,
13
collection definition,
13
collection method,
13
comment,
13
constant,
13
cursor attributes,
13
cursor declaration,
13
cursor variables,
13
DECLARE,
13
DELETE statement,
13
diagram reading,
13
END,
13
EXCEPTION,
13
exception definition,
13
EXCEPTION_INIT pragma,
13
EXECUTE IMMEDIATE statement,
13
EXIT statement,
13
expression,
13
FETCH statement,
13
FORALL statement,
13
FOR-LOOP statement,
13
function declaration,
13
GOTO statement,
13
IF statement,
13
INSERT statement,
13
label,
13
literal declaration,
13
LOCK TABLE statement,
13
LOOP statement,
13
NULL statement,
13
object type declaration,
13
of PL/SQL language elements,
13
OPEN statement,
13
OPEN-FOR statement,
13
OPEN-FOR-USING statement,
13
package declaration,
13
procedure declaration,
13
RAISE statement,
13
record definition,
13
RESTRICT_REFERENCES pragma,
13
RETURN statement,
13
RETURNING clause,
13
ROLLBACK statement,
13
ROWTYPE attribute,
13
SAVEPOINT statement,
13
SELECT INTO statement,
13
SERIALLY_REUSABLE pragma,
13
SET TRANSACTION statement,
13
SQL cursor,
13
SQLCODE function,
13
SQLERRM function,
13
TYPE attribute,
13
UPDATE statement,
13
variable,
13
WHILE-LOOP statement,
13
SYS_INVALID_ROWID exception,
10.3
SYS_REFCURSOR type,
11.10.8
T
TABLE datatype,
5.3
table functions
examples on Oracle Technology Network,
11.10
exception handling,
11.10.11
fetching from results of,
11.10.7
for querying,
11.10.1
information on Oracle Technology Network,
11.10
organizing multiple calls to,
11.10.6
passing data with cursor variables,
11.10.8
performing DML operations inside,
11.10.9
performing DML operations on,
11.10.10
pipelining data between,
11.10.5
returning results from,
11.10.4
setting up transformation pipelines,
11.10
using transformation pipelines,
11.10.3
writing transformation pipelines,
11.10.2
TABLE operator
manipulating objects in PL/SQL,
12.3.1
tabs,
2.1
terminator, statement,
2.1.1
ternary operators,
2.6
THEN clause
using,
4.2.1
with IF statement,
4.2.1
TIMEOUT_ON_RESOURCE exception,
10.3
TIMESTAMP datatype,
3.1.6.2
TIMESTAMP WITH LOCAL TIME ZONE datatype,
3.1.6.4
TIMESTAMP WITH TIME ZONE datatype,
3.1.6.3
TIMEZONE_ABBR
datatype field,
3.1.6
TIMEZONE_HOUR
datatype field,
3.1.6
TIMEZONE_MINUTES
datatype field,
3.1.6
TIMEZONE_REGION
datatype field,
3.1.6
TOO_MANY_ROWS exception,
10.3
trailing blanks,
3.4.3
transactions,
6.1.2
autonomous in PL/SQL,
6.8
committing,
6.7.1
context,
6.8.2.2
ending properly,
6.7.5
processing in PL/SQL,
6.1.2
,
6.7
properties,
6.7.6
read-only,
6.7.6
restrictions,
6.7.6.1
rolling back,
6.7.2
savepoints,
6.7.3
visibility,
6.8.2.3
triggers
as a stored PL/SQL subprogram,
1.3.1.3
autonomous,
6.8.4
TRIM method
collections,
5.8.7
,
13
TRUE value,
2.1.3.4
TRUST option
RESTRICT_REFERENCES,
13
tuning
allocate large VARCHAR2 variables,
11.3.2.1
avoid memory overhead,
11.3.2
compiling PL/SQL code for native execution,
11.9
computation-intensive programs,
11.6
do not duplicate built-in functions,
11.3.1.4
dynamic SQL programs,
11.7
group related subprograms into a package,
11.3.2.2
guidelines for avoiding PL/SQL performance problems,
11.3
improve code to avoid compiler warnings,
11.3.2.4
information on Oracle Technology Network,
11
make function calls efficient,
11.3.1.2
make loops efficient,
11.3.1.3
make SQL statements efficient,
11.3.1.1
optimizing PL/SQL programs,
11.2
pin packages in the shared memory pool,
11.3.2.3
PL/SQL code,
11.2.1
profiling and tracing,
11.4
reducing loop overhead,
11.5
reorder conditional tests to put least expensive first,
11.3.1.5
use BINARY_FLOAT or BINARY_DOUBLE for floating-point arithmetic,
11.3.1.8
use PLS_INTEGER for integer arithmetic,
11.3.1.7
using DBMS_PROFILE and DBMS_TRACE,
11.4
using FORALL,
11.5.1
using NOCOPY,
11.8
using transformation pipelines,
11.10
TYPE attribute
declaring,
1.2.5.1
effect of default value,
2.2.4
effect of NOT NULL constraint,
2.2.4
inherited properties from column,
13
links to examples,
13
syntax,
13
using,
2.2.4
with SUBTYPE,
3.2.1
TYPE definition
associative arrays,
5.3
collection,
5.3
collection types,
5.3
nested tables,
5.3
RECORD,
5.10
REF CURSOR,
6.5.3
VARRAY,
5.3
U
unary operators,
2.6
underscores,
2.1.2
unhandled exceptions
catching,
10.8.5
propagating,
10.6
uninitialized object
how treated in PL/SQL,
12.1.2
UNION ALL set operator,
6.1.5
UNION set operator,
6.1.5
universal rowids,
3.1.2.4
UPDATE statement
links to examples,
13
syntax,
13
with a record variable,
5.11.3
URL (uniform resource locator),
9.7.6
UROWID
pseudocolumn,
6.1.4
UROWID datatype,
3.1.2.4
USE ROLLBACK SEGMENT parameter
setting transactions,
13
user-defined
exceptions in PL/SQL,
10.4
records,
5.1
subtypes,
3.2
USING clause
EXECUTE IMMEDIATE,
13
with dynamic SQL,
7.2
with EXECUTE IMMEDIATE,
13
with OPEN FOR statement,
13
UTF8 character encoding,
3.1.3.1
UTL_FILE package,
9.7.5
UTL_HTTP package,
9.7.6
UTL_SMTP package,
9.7.7
utlirp.sql script
for PL/SQL native compilation,
11.9.7
utlrp.sql script
for PL/SQL native compilation,
11.9.9
V
V$RESERVED_WORDS view
listing of reserved words and keywords,
D
VALUE function,
12.2.2
VALUE_ERROR exception,
10.3
VALUES clause
INSERT,
13
VALUES OF clause,
11.5.1
FORALL,
13
VARCHAR subtype,
3.1.2.5.1
VARCHAR2 datatype,
3.1.2.5
differences with CHAR,
3.4
variables
assigning query result to,
2.5.2
assigning values,
1.2.2.2
,
2.5
bind,
1.2.2.3
,
13
declaring,
1.2.2.1
,
2.2
declaring PL/SQL,
1.2.5
global,
9.5
host,
13
initializing,
2.5
links to examples,
13
passing as IN OUT parameter,
1.2.2.2
REF CURSOR datatype,
6.5
syntax,
13
understanding PL/SQL,
1.2.2
variable-size arrays (varrays)
understanding,
5.1.1
VARRAY datatype,
5.1.1.2
,
5.3
varrays
size limit,
5.3
syntax,
13
TYPE definition,
5.3
understanding,
5.1.1
versus nested tables,
5.2.2
visibility
of package contents,
9.1
transaction,
6.8.2.3
versus scope,
2.4
W
warning messages
controlling PL/SQL,
10.9.2
Web applications
creating with PL/SQL,
2.8
Web server pages
creating with PL/SQL,
2.8
WHEN clause
exception handling,
13
exceptions,
10.8
using,
4.3.3
WHERE CURRENT OF clause
DELETE statement,
13
UPDATE,
13
WHILE-LOOP statement
overview,
1.2.6.2
syntax,
13
using,
4.3.5
wildcards,
2.6.1.6
WNDS option
RESTRICT_REFERENCES,
13
WNPS option
RESTRICT_REFERENCES,
13
words
reserved in PL/SQL,
D
work areas
queries,
6.5.1
WRAP function
obfuscation,
A.3
wrap utility
limitations,
A.1.2
obfuscating PL/SQL code,
A
obfuscation of PL/SQL code,
A.2
running,
A.2.2
wrapped
PL/SQL source code,
A
wrapping
DBMS_DDL package,
A.3
dynamically,
Preface
,
A
limitations,
A.1.2
obfuscating code,
A
on-the-fly,
Preface
,
A
recommendations,
A.1.1
tips,
A.1.1
understanding,
A.1
viewing source,
A.1
,
A.3.1
with CREATE_WRAPPED,
A.3.1
WRITE parameter
with READ to set transactions,
13
Y
YEAR
datatype field,
3.1.6
Z
ZERO_DIVIDE exception,
10.3
ZONE
part of TIMESTAMP datatype,
3.1.6.3
Scripting on this page enhances content navigation, but does not change the content in any way.