Skip Headers

Oracle® Database Application Developer's Guide - Fundamentals
10g Release 1 (10.1)

Part Number B10795-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
View PDF

Index

A  B  C  D  E  F  G  H  I  J  K  L  M  N  O  P  Q  R  S  T  U  V  W  X  Y  Z 


Symbols

%ROWTYPE attribute, 7-7
used in stored functions, 7-8
%TYPE attribute, 7-7

Numerics

3GL, definition, 1-4
4GL, definition, 1-3

A

Active Data Object, translating to PSP, 13-20
Active Server Pages, translating to PSP, 13-20
AFTER SUSPEND event, handling suspended storage allocation, 5-39
AFTER triggers
auditing and, 9-33, 9-35
correlation names and, 9-17
specifying, 9-6
agent, definition, 11-4
ALL_ERRORS view, debugging stored procedures, 7-35
ALL_SOURCE view, 7-35
ALTER SESSION statement, SERIALIZABLE clause, 5-23
ALTER TABLE statement
defining integrity constraints, 3-18
DISABLE ALL TRIGGERS clause, 9-29
DISABLE CONSTRAINT clause, 3-22
DROP CONSTRAINT clause, 3-25
ENABLE ALL TRIGGERS clause, 9-29
ENABLE CONSTRAINT clause, 3-21
ALTER TABLE statement (continued)
INITRANS parameter, 5-23
RETENTION option, for flashback, 15-5
ALTER TRIGGER statement
DISABLE clause, 9-29
ENABLE clause, 9-29
anonymous PL/SQL blocks
about, 7-2
compared to triggers, 7-20
ANSI SQL92
FIPS flagger, 5-2
AnyData datatype, 2-44
AnyDataSet datatype, 2-44
AnyType datatype, 2-44
applications
calling stored procedures and packages, 7-45
unhandled exceptions in, 7-38
attributes
%rowtype, PL/SQL, 1-5
%type, PL/SQL, 1-5
auditing, triggers and, 9-32
autonomous routine, 5-29
autonomous scope, definition, 5-29
autonomous transactions, 5-28 to 5-36
AUTONOMOUS_TRANSACTION pragma, 5-29

B

BEFORE triggers
complex security authorizations, 9-45
correlation names and, 9-17
derived column values, 9-46
specifying, 9-6
BFILE datatype, 2-7, 2-34
binary data, RAW and LONG RAW, 2-35
Binary Large Object, 2-34
BINARY_DOUBLE datatype, 2-5, 2-11
BINARY_FLOAT datatype, 2-5, 2-11
binding, bulk, definition, 7-17
blank-padded comparison, 2-10
BLOB datatype, 2-7, 2-34
body of package, definition, 7-13
Boolean expressions, 2-43
bulk binding, definition, 7-17
bulk binds, 7-17
DML statements, 7-18
FOR loops, 7-19
SELECT statements, 7-19
usage, 7-18
BY REF phrase, 8-32
BYTE qualifier for column lengths, 2-9

C

call specifications, 8-4 to 8-53
callbacks, 8-47 to 8-49
canceling a cursor, 5-10
CATPROC.SQL script, 10-2
CC datetime format element, 2-27
century, 2-25
date format masks, 2-21
CGI variables, 13-19
CHAR datatype, 2-4
CHAR qualifier for column lengths, 2-9
character data, 2-8
Character Large Object, 2-34
CHARSETFORM property, 8-28
CHARSETID property, 8-28
CHARTOROWID function, 2-41
CHECK constraint, triggers and, 9-38, 9-43
check constraints, how to use, 3-15
client, definition, 11-4
client events, 10-8
CLOB datatype, 2-5, 2-34
column type attribute, PL/SQL, 1-5
columns
accessing in triggers, 9-17
default values, 3-5, 7-51
generating derived values with triggers, 9-46
columns (continued)
listing in an UPDATE trigger, 9-6, 9-20
multiple foreign key constraints, 3-11
number of CHECK constraints limit, 3-17
specifying length in bytes or characters, 2-9
COMMIT statement, 5-5
comparison operators
blank-padded and non-padded data, 2-10
dates, 2-21
compile-time errors, 7-33
compiling PL/SQL procedures to native code, 7-21
composite keys
foreign, 3-8
restricting nulls in, 3-17
concurrency, 5-19
conditional expressions, in WHERE clause, 2-32
conditional predicates, trigger bodies, 9-15, 9-19
connection pooling, 1-23
consistency, read-only transactions, 5-7
constraining tables, 9-22
constraints, , S
context switches, reducing with bulk binds, 7-17
converting data, 2-40
ANSI datatypes, 2-39
assignments, 2-41
expression evaluation, 2-43
SQL/DS and DB2 datatypes, 2-40
year and century considerations, 2-26
cookies, 13-19
correlation names, 9-14 to 9-19
NEW, 9-17
OLD, 9-17
REFERENCING option and, 9-19
when preceded by a colon, 9-17
CREATE INDEX statement, 4-7
CREATE PACKAGE BODY statement, 7-15
CREATE PACKAGE statement, 7-15
CREATE TABLE statement
defining integrity constraints, 3-18
INITRANS parameter in, 5-23
CREATE TRIGGER statement, 9-2
REFERENCING option, 9-19
cursor and cursor variable, definitions, 5-8
cursors, 5-8
canceling, 5-10
cursors, 5-8 (continued)
closing, 5-10
declaring and opening cursor variables, 7-31
maximum number of, 5-9
pointers to, 7-30
private SQL areas and, 5-8
custom OWA, definition, 13-6

D

DAD, definition, 13-4
data blocks, shown in ROWIDs, 2-37
data conversion, , S
data dictionary
compile-time errors, 7-35
integrity constraints in, 3-28
procedure source code, 7-35
data object number, extended ROWID, 2-36, 2-37
data recovery using flashback features, 15-3
Database Access Descriptor, 13-4
datafiles, shown in ROWIDs, 2-37
datatypes, 2-2
ANSI/ISO, 2-39
BFILE, 2-7
BINARY_DOUBLE, 2-5, 2-11
BINARY_FLOAT, 2-5, 2-11
BLOB, 2-7
CHAR, 2-4, 2-8
column length, 2-9
character, 2-8
CLOB, 2-5
column lengths for character types, 2-9
conversion, 2-40
DATE, 2-6, 2-25
DB2, 2-39
INTERVAL DAY TO SECOND, 2-6
INTERVAL YEAR TO MONTH, 2-6
LONG, 2-5, 2-8
LONG RAW, 2-8
MDSYS.SDO_GEOMETRY, 2-33
native floating point, 2-11
native floating-point, IEEE 754 exceptions not raised, 2-15
NCHAR, 2-4, 2-8
NCLOB, 2-5
datatypes, 2-2 (continued)
NUMBER, 2-5, 2-11
numeric, 2-11
NVARCHAR2, 2-4, 2-8
RAW, 2-7
ROWID, 2-8, 2-36
SQL/DS, 2-39
TIMESTAMP, 2-6
TIMESTAMP WITH LOCAL TIME ZONE, 2-7
TIMESTAMP WITH TIME ZONE, 2-7
UROWID, 2-8
VARCHAR, , S
VARCHAR2, 2-4, 2-8
column length, 2-9
date and time data, representing, 2-20
date arithmetic, 2-43
functions for, 2-22
DATE datatype, 2-6, 2-20
centuries, 2-25
data conversion, 2-41
DB2 datatypes, 2-39
DBA_ERRORS view, debugging stored procedures, 7-35
DBA_SOURCE view, 7-35
DBMS_FLASHBACK package, 15-7
DBMS_LOCK package, 5-17
DBMS_RESUMABLE package, handling suspended storage allocation, 5-39
DBMS_SQL package
advantages of, 6-16
client-side programs, 6-16
DESCRIBE, 6-16
differences with native dynamic SQL, 6-11
See also dynamic SQL
DBMS_STATS package and Flashback Query, 15-15
DBMS_TYPES package, 2-44
DBMS_XMLGEN package, 2-47
DBMS_XMLQUERY package, 2-47
DBMS_XMLSAVE package, 2-47
DDL statements, package state and, 7-16
DEBUG_EXTPROC package, 8-51
debugging
stored procedures, 7-40
triggers, 9-28
dedicated external procedure agents, 8-6
default parameters in stored functions, 7-53
definer's-rights procedure, 7-46
DELETE statement
column values and triggers, 9-17
data consistency, 5-10
triggers for referential integrity, 9-40, 9-41
denormal floating-point numbers, 2-13
dependencies
among PL/SQL library objects, 7-21
in stored triggers, 9-27
schema objects, trigger management, 9-22
timestamp model, 7-22
DESC function, 4-9
deterministic function, definition, 7-56
DETERMINISTIC keyword, 7-56
dictionary_obj_owner event attribute, 10-3
dictionary_obj_owner_list event attribute, 10-3
dictionary_obj_type event attribute, 10-3
disabled integrity constraint, definition, 3-19
disabled trigger, definition, 9-28
disabling
integrity constraints, 3-20
triggers, 9-28, 9-29
distributed databases
referential integrity and, 3-15
remote stored procedures, 7-47, 7-48
triggers and, 9-22
distributed queries
flashback features, 15-16
handling errors, 7-38
distributed transaction processing architecture, 16-2
distributed update, definition, 7-49
DML_LOCKS parameter, 5-11
double datatype, native in C and C++, 2-19
DROP INDEX statement, 4-6
DROP TRIGGER statement, 9-28
dropping
indexes, 4-6
integrity constraints, 3-25
packages, 7-11
procedures, 7-11
triggers, 9-28
dynamic SQL
application development languages, 6-20
invoker's rights, 6-7
invoking PL/SQL blocks, 6-6
optimization, 6-5
queries, 6-4
scenario, 6-7
usage, 6-3
See also
DBMS_SQL package
native dynamic SQL
dynamic Web pages, 13-19
dynamically typed data, representing, 2-44

E

e-mail, sending from PL/SQL, 13-15
embedded SQL, 7-2
enabled integrity constraint, definition, 3-19
enabled trigger, definition, 9-28
enabling
integrity constraints, 3-20
triggers, 9-28
errors
application errors raised by Oracle Database packages, 7-36
remote procedures, 7-38
user-defined, 7-35, 7-37
event attribute functions, 10-2
event notification, 10-1, 11-5
event publication, 9-50 to 9-52, 10-1
triggering, 9-50
events
attribute, 10-2
client, 10-8
resource manager, 10-7
system, 10-1
tracking, 9-49, 10-1
exception handlers, in PL/SQL, 7-2
exception to a constraint, 3-20
exceptions
anonymous blocks, 7-3
during trigger execution, 9-20
effects on applications, 7-38
remote procedures, 7-38
exceptions (continued)
unhandled, 7-38
exclusive locks, LOCK TABLE statement, 5-15
explicit locks, 5-10
expression filtering, 2-32
expressions, conditional in WHERE clause, 2-32
extended ROWID format, 2-36
external LOB, definition, 2-34
external procedure, 8-3
DBA tasks required, 8-6
DEBUG_EXTPROC package, 8-51
debugging, 8-50
definition, 8-3
maximum number of parameters, 8-53
specifying datatypes, 8-19
extproc process, 8-6, 8-37

F

features, new, i-xxxvii
FIPS flagger, interactive SQL statements and, 5-2
firing of triggers, 9-1
FIXED_DATE initialization parameter, 2-21
flashback features, 15-2
performance, 15-15
flashback privileges, 15-4
Flashback Query, 15-5
DBMS_STATS package, 15-15
Flashback Transaction Query, 15-12
Flashback Version Query, 15-10
FLASHBACK_TRANSACTION_QUERY view, 15-12
float datatype, native in C and C++, 2-19
floating-point numbers, 2-11
FOR EACH ROW clause, 9-13
FORALL statement, using, 7-17
foreign key constraints
defining, 3-27
enabling, 3-20, 3-26
NOT NULL constraint and, 3-10
one-to-many relationship, 3-10
one-to-n relationships, 3-10
UNIQUE key constraint and, 3-11
format mask, definition, 2-28
format masks, TO_DATE function, 2-21
fourth-generation computing language, definition, 1-3
full-text search, using Oracle9i Text, 2-34
functions, , S

G

geographic coordinate data, representing, 2-33
global entity, definition, 7-15
global OWA, definition, 13-6
grantee event attribute, 10-3

H

HEXTORAW function, 2-41
hiding PL/SQL code, 7-20
hostname, 13-16
HTML
displaying within PSP files, 13-22
retrieving from PL/SQL, 13-16
HTP and HTF packages, 13-19
HTTP URLs, 13-16

I

IBM datatypes, 2-39
IEEE 754 standard for floating-point numbers, 2-12
image maps, 13-19
IN OUT parameter mode, 7-6
IN parameter mode, 7-6
indexes
creating, 4-6
dropping, 4-6
function-based, 4-8
guidelines, 4-3
order of columns, 4-4
privileges, 4-6
SQL*Loader and, 4-3
temporary segments and, 4-3
when to create, 4-2
INDICATOR property, 8-27
-INF and +INF, 2-14
infinity values, 2-14
initialization parameters
DML_LOCKS, 5-11
initialization parameters (continued)
OPEN_CURSORS, 5-9
REMOTE_DEPENDENCIES_MODE, 7-28
INITRANS parameter, 5-23
INSERT statement
column values and triggers, 9-17
read consistency, 5-10
instance_num event attribute, 10-3
INSTEAD OF triggers, 9-8
on nested table view columns, 9-18
integrity constraints
CHECK, 3-15
composite UNIQUE keys, 3-7
defining, 3-17
disabling, 3-20, 3-21, 3-22
dropping, 3-25
enabling, 3-19
examples, 3-2
exceptions to, 3-23
listing definitions of, 3-28
naming, 3-19
NOT NULL, 3-3
performance considerations, 3-3
PRIMARY KEY, 3-5
privileges required for creating, 3-19
renaming, 3-24
triggers vs., 9-2, 9-37
UNIQUE, 3-6
violations, 3-20
when to use, 3-2
interactive block execution, 7-44
INTERVAL DAY TO SECOND datatype, 2-6, 2-20
INTERVAL YEAR TO MONTH datatype, 2-6, 2-20
invoker's-rights procedure, 7-46
is_alter_column event attribute, 10-3
ISOLATION LEVEL
changing, 5-23
SERIALIZABLE, 5-23

J

Java
calling methods through call specifications, 8-4
compared to PL/SQL, 1-41
Java (continued)
generating wrapper classes with JPublisher, 1-16
JDBC, overview, 1-9
loading into the database, 8-5
SQLJ, overview, 1-13
Java Server Pages, translating to PSP, 13-20
Javascript, translating to PSP, 13-20
JDBC, , S
JScript, translating to PSP, 13-20

K

keys
foreign, 3-26
unique, composite, 3-7

L

large data, representing with LOBs, 2-34
Large Objects (LOBs), 2-34
libraries, 1-41
library units, remote dependencies, 7-21
loadjava utility, 1-16
loadpsp command, 13-29
LOB datatypes, 1-38, 2-34
external, definition, 2-34
support in OO4O, 1-37
use in triggers, 9-18
LOCK TABLE statement, 5-11
locks
explicit, 5-10
LOCK TABLE statement, 5-11, 5-12
privileges for manual acquirement, 5-15
user, 5-17
UTLLOCKT.SQL script, 5-19
LONG datatype, 2-5, 2-8
use in triggers, 9-22
LONG RAW datatype, 2-8, 2-35
LOWER function, 4-9

M

mail, sending from PL/SQL, 13-15
main transaction, definition, 5-29
manual locks, 5-10
LOCK TABLE statement, 5-11
mask, format, definition, 2-28
match full rule for NULL values, 3-10
match partial rule for NULL values, 3-10
MDSYS.SDO_GEOMETRY datatype, 2-33
memory, scalability, 7-63
migration, ROWID format, 2-38
mod_plsql, definition, 13-3
modes, parameter, 7-6
mutating table, definition, 9-22
mutating tables, trigger restrictions, 9-22

N

NaN (not a number value), 2-14
National Character Set Large Object, 2-34
native dynamic SQL
advantages of, 6-11
differences with DBMS_SQL package, 6-11
fetching into records, 6-15
performance, 6-14
user-defined types, 6-15
See also dynamic SQL
native execution, of PL/SQL procedures, 7-21
native float and native double datatypes in C and C++, 2-19
native floating-point datatypes, 2-11
arithmetic, rounding behavior, 2-16
IEEE 754 exceptions not raised, 2-15
infinity values, 2-14
NCHAR datatype, 2-4, 2-8
NCLOB datatype, 2-5, 2-34
negative infinity value, 2-14
negative zero value, 2-14
NEW correlation name, 9-17
new features, i-xxxvii
NLS_DATE_FORMAT parameter, 2-21
NLSSORT order, and indexes, 4-9
non-padded comparison, 2-10
normalization of floating-point numbers, 2-13
not a number (NaN) value, 2-14
NOT NULL constraint
CHECK constraint and, 3-17
data integrity, 3-20
NOT NULL constraint (continued)
when to use, 3-3
notification, event, 10-1, 11-5
NOWAIT option, 5-12
NUMBER datatype, 2-5, 2-11, 2-19
numeric datatypes, 2-11
NVARCHAR2 datatype, 2-4, 2-8

O

OAS, 13-19
object columns, indexes on, 4-9
object support in OO4O, 1-37
OCCI, overview, 1-25
OCI, 7-2
applications, 7-4
cancelling cursors, 5-10
closing cursors, 5-10
overview, 1-25
parts of, 1-27
vs precompilers, 1-40
OLD correlation name, 9-17
one-to-many relationship, with foreign keys, 3-10
one-to-one relationship, with foreign keys, 3-11
OO4O, , S
open string for XA, 16-9
OPEN_CURSORS parameter, 5-9
OR REPLACE clause, for creating packages, 7-15
ora_dictionary_obj_owner event attribute, 10-3
ora_dictionary_obj_owner_list event attribute, 10-3
ora_dictionary_obj_type event attribute, 10-3
ora_grantee event attribute, 10-3
ora_instance_num event attribute, 10-3
ora_is_alter_column event attribute, 10-3
ora_is_creating_nested_table event attribute, 10-4
ora_is_drop_column event attribute, 10-4
ora_is_servererror event attribute, 10-4
ora_login_user event attribute, 10-4
ora_privileges event attribute, 10-4
ora_revokee event attribute, 10-4
ORA_ROWSCN pseudocolumn, 15-9
ora_server_error event attribute, 10-4
ora_sysevent event attribute, 10-4
ora_with_grant_option event attribute, 10-7
ORA-21301 error, fixing, 16-12, 16-14
OraAQ object, 1-36
OraAQAgent object, 1-37
OraAQMsg object, 1-37
OraBFILE object, 1-38
OraBLOB object, 1-38
Oracle Application Server (OAS), 13-19
Oracle Call Interface, , S
Oracle Data Control (ODC), 1-39
Oracle Data Provider for .NET, overview, 1-29
Oracle Database 10g, new application development features, i-xxxvii
Oracle Database errors, 7-3
Oracle JDBC
definition, 1-9
example, 1-12
OCI driver, 1-10
Oracle Database extensions, 1-11
server driver, 1-11
stored procedures, 1-19
thin driver, 1-10
Oracle JDeveloper, definition, 1-15
Oracle JPublisher, definition, 1-17
Oracle Objects for OLE
automation server, 1-31
C++ Class Library, 1-39
LOB and object support, 1-37
object model, 1-32
overview, 1-30
Oracle SQLJ
advantages over JDBC, 1-15
definition, 1-13
design, 1-15
in the server, 1-16
stored programs, 1-16
OraCLOB object, 1-38
OraDatabase object, 1-34
OraDynaset object, 1-34
OraField object, 1-35
OraMDAttribute object, 1-35
OraMetaData object, 1-35
OraParamArray object, 1-36
OraParameter object, 1-35
OraServer object, 1-33
OraSession object, 1-33
OraSQLStmt object, 1-36
OUT parameter mode, 7-6
overloading
packaged functions, 7-63
procedures and functions, definition, 7-12
using RESTRICT_REFERENCES, 7-63
OWA function invocation types, 13-6
OWA* packages, 13-19

P

package, definition, 7-12
package body, 7-12
package OWA, definition, 13-6
package specification, 7-12
packages, 1-41
creating, 7-15
DBMS_OUTPUT, example of use, 7-3
DEBUG_EXTPROC, 8-51
dropping, 7-11
in PL/SQL, 7-12
naming of, 7-16
Oracle Database, 7-17
privileges for execution, 7-45
privileges required to create, 7-16
privileges required to create procedures in, 7-10
serially reusable packages, 7-63
session state and, 7-16
synonyms, 7-49
where documented, 7-17
parallel server, distributed locks, 5-10
PARALLEL_ENABLE keyword, 7-56
parameters
default values, 7-9
with stored functions, 7-53
modes, 7-6
parse tree, 9-26
pcode, when generated for triggers, 9-26
performance
index column order, 4-4
native dynamic SQL, 6-14
permanent and temporary LOB instances, 2-34
platform-specific Oracle Database documentation, PL/SQL wrapper, 7-20
PL/SQL, 7-2
anonymous blocks, 7-2
PL/SQL, 7-2 (continued)
calling remote stored procedures, 7-48
compared to Java, 1-41
cursor variables, 7-30
dependencies among library units, 7-21
exception handlers, 7-2
functions
arguments, 7-53
overloading, 7-63
parameter default values, 7-53
purity level, 7-62
RESTRICT_REFERENCES pragma, 7-59
using, 7-50
hiding source code, 7-20
invoking with dynamic SQL, 6-6
objects, 1-6
overview, 1-3
packages, 7-12
program units, 7-2
RAISE statement, 7-36
sample code, 1-4
serially reusable packages, 7-63
server pages, 13-19 to 13-30
tables, 7-8
of records, 7-8
trigger bodies, 9-15, 9-17
user-defined errors, 7-36
Web toolkit, 13-19
wrapper to hide code, 7-20
PL/SQL Gateway, definition, 13-3
PL/SQL Web Toolkit, definition, 13-3
PLSQL_COMPILER_FLAGS initialization parameter, 7-21
positive infinity value, 2-14
positive zero value, 2-14
posting, message, definition, 11-5
pragma, 5-37
RESTRICT_REFERENCES, 7-59
SERIALLY_REUSABLE pragma, 7-63, 7-64
precompilers, 7-45
applications, 7-4
calling stored procedures and packages, 7-45
vs OCI, 1-40
PRIMARY KEY constraints
choosing a primary key, 3-5
PRIMARY KEY constraints (continued)
disabling, 3-21
enabling, 3-20
multiple columns in, 3-6
UNIQUE key constraint vs., 3-6
private SQL areas, cursors and, 5-8
privileges
creating integrity constraints, 3-19
creating triggers, 9-26
dropping triggers, 9-28
flashback, 15-4
index creation, 4-6
manually acquiring locks, 5-15
recompiling triggers, 9-28
stored procedure execution, 7-45
triggers, 9-26
Pro*C/C++, overview of application development, 1-20
Pro*COBOL, overview of application development, 1-23
procedure
external, definition, 8-3
procedures
called by triggers, 9-22
external, 8-3
program units in PL/SQL, 7-2
property
CHARSETFORM, 8-28
CHARSETID, 8-28
INDICATOR, 8-27
pseudocolumns, modifying views, 9-9
PSP, , S
.psp files, 13-21
publish-subscribe, 11-2 to 11-6
purity of stored function, definition, 7-55

Q

queries
dynamic, 6-4
errors in distributed queries, 7-38

R

RAISE statement, 7-36
RAISE_APPLICATION_ERROR procedure, 7-35
remote procedures, 7-38
raising exceptions, triggers, 9-20
RAW datatype, 2-7, 2-35
RAWTOHEX function, 2-41
RAWTONHEX function, 2-41
read-only transactions, 5-7
recovery, data, using flashback features, 15-3
REF column, indexes on, 4-9
REFERENCING option, 9-19
referential integrity
distributed databases and, 3-15
one-to-many relationship, 3-10
one-to-one relationship, 3-11
privileges required to create foreign keys, 3-27
self-referential constraints, 9-41
triggers and, 9-38 to 9-42
remote dependencies, 7-21
signatures, 7-23
specifying timestamps or signatures, 7-28
remote exception handling, 7-38, 9-20
remote queries, flashback features, 15-16
REMOTE_DEPENDENCIES_MODE parameter, 7-28
repeatable reads, 5-7, 5-10
resource manager, 16-2
events, 10-7
RESTRICT_REFERENCES pragma, 7-59
syntax for, 7-60
restrictions, system triggers, 9-25
resumable storage allocation, 5-38
definition, 5-38
examples, 5-39
RETENTION GUARANTEE clause for undo tablespace, 15-4
reusable packages, 7-63
RM (resource manager), 16-2
RNDS argument, 7-60
RNPS argument, 7-60
ROLLBACK statement, 5-5
rolling back transactions, to savepoints, 5-6
rounding modes for native floating-point numbers, 2-15
routine
autonomous scope, definition, 5-29
routine (continued)
external, definition, 8-3
routines
external, 8-3
service, 8-38
row locking, manual, 5-16
row triggers
defining, 9-13
REFERENCING option, 9-19
timing, 9-6
UPDATE statements and, 9-6, 9-20
ROWID, definition, 2-37
ROWID datatype, 2-8, 2-36
extended ROWID format, 2-36
migration, 2-38
ROWIDTOCHAR function, 2-41
ROWIDTONCHAR function, 2-41
rows
shown in ROWIDs, 2-37
violating integrity constraints, 3-20
rowtype attribute, PL/SQL, 1-5
ROWTYPE_MISMATCH exception, 7-33
RR date format, 2-26
RS locks, LOCK TABLE statement, 5-12
run-time error handling, 7-35
RX locks, LOCK TABLE statement, 5-12

S

S locks, LOCK TABLE statement, 5-12
SAVEPOINT statement, 5-6
savepoints
maximum number of, 5-6
rolling back to, 5-6
scalability, serially reusable packages, 7-63
scope
autonomous, definition, 5-29
scripting, 13-19
scrollable cursors, 1-23
search data, representing, 2-34
secure application roles, i-xlv
SELECT statement
AS OF clause, 15-5
FOR UPDATE clause, 5-16
read consistency, 5-10
SELECT statement (continued)
VERSIONS BETWEEN...AND clause, 15-10
SERIALIZABLE option, for ISOLATION LEVEL, 5-23
serializable transactions, 5-19
serially reusable PL/SQL packages, 7-63
SERIALLY_REUSABLE pragma, 7-64
service routine, 8-38
sessions, package state and, 7-16
SET TRANSACTION statement, 5-8
ISOLATION LEVEL clause, 5-23
SERIALIZABLE, 5-23
share locks (S), LOCK TABLE statement, 5-12
share row exclusive locks (SRX), LOCK TABLE statement, 5-14
side effects, subprogram, 7-6, 7-55
signatures
PL/SQL library unit dependencies, 7-21
to manage remote dependencies, 7-23
SORT_AREA_SIZE parameter, index creation and, 4-3
sorting, with function-based indexes, 4-8
specification part of package, definition, 7-13
SQL statements
execution, 5-2
in trigger bodies, 9-17, 9-22
not allowed in triggers, 9-22
SQL*Loader, indexes and, 4-3
SQL*Module, applications, 7-4
SQL*Plus
anonymous blocks, 7-4
compile-time errors, 7-33
invoking stored procedures, 7-43
loading a procedure, 7-10
SET SERVEROUTPUT ON command, 7-3
SHOW ERRORS command, 7-34
SQL/DS datatypes, 2-39
SRX locks, LOCK Table statement, 5-14
standards, IEEE 754, 2-11
state
package, definition, 7-63
session, package objects, 7-16
Web application, definition, 13-14
stateful and stateless user interfaces, definitions, 1-3
statement triggers
conditional code for statements, 9-19
row evaluation order, 9-7
specifying SQL statement, 9-5
timing, 9-6
trigger evaluation order, 9-7
UPDATE statements and, 9-6, 9-20
valid SQL statements, 9-22
storage allocation errors, resuming execution after, 5-38
stored functions, 7-4
creating, 7-9
restrictions, 7-51
stored procedure, definition, 7-5
stored procedures, 7-4
argument values, 7-46
creating, 7-9
distributed query creation, 7-38
exceptions, 7-35, 7-37
invoking, 7-43
names of, 7-5
overloading names of, 7-12
parameter, default values, 7-9
privileges, 7-45
remote, 7-47
remote objects and, 7-48
storing, 7-9
synonyms, 7-49
turning into a Web page, 13-19
subnormal floating-point numbers, 2-13
synonyms, stored procedures and packages, 7-49
SYS_XMLAGG function, 2-47
SYS_XMLGEN function, 2-47
SYSDATE function, 2-21
system events, 10-1
attributes, 10-2
client, 10-8
resource manager, 10-7
tracking, 9-49, 10-1

T

table, mutating, definition, 9-22
tables
constraining, 9-22
tables (continued)
in PL/SQL, 7-8
mutating, 9-22
TCP/IP, 13-16
temporary and permanent LOB instances, 2-34
temporary segments, index creation and, 4-3
text search, using Oracle9i Text, 2-34
third-generation computing language, definition, 1-4
time and date data, representing, 2-20
time zones, functions, 2-23
TIMESTAMP datatype, 2-6, 2-20
TIMESTAMP WITH LOCAL TIME ZONE datatype, 2-7, 2-20
TIMESTAMP WITH TIME ZONE datatype, 2-7, 2-20
timestamps, PL/SQL library unit dependencies, 7-21
TM (transaction manager), 16-2
TO_CHAR function, 2-41
CC date format, 2-27
RR date format, 2-26
TO_CLOB function, 2-41
TO_DATE function, 2-21, 2-41
RR date format, 2-26
TO_NCHAR function, 2-41
TO_NCLOB function, 2-41
TO_NUMBER function, 2-41
tracking system events, 9-49, 10-1
transaction manager, 16-2
transaction set consistency, definition, 5-25
transaction, main, definition, 5-29
transactions
autonomous, 5-28 to 5-36
read-only, 5-8
serializable, 5-19
SET TRANSACTION statement, 5-8
trigger
disabled, definition, 9-28
enabled, definition, 9-28
triggering statement, definition, 9-5
triggers
about, 7-20
accessing column values, 9-17
AFTER, 9-6, 9-17, 9-33, 9-35
triggers (continued)
auditing with, 9-32, 9-33
BEFORE, 9-6, 9-17, 9-45, 9-46
body, 9-15, 9-19, 9-20, 9-22
check constraints, 9-43, 9-45
client events, 10-8
column list in UPDATE, 9-6, 9-20
compiled, 9-26
conditional predicates, 9-15, 9-19
creating, 9-2, 9-21, 9-26
data access restrictions, 9-45
debugging, 9-28
designing, 9-2
disabling, 9-28, 9-29
distributed query creation, 7-38
enabling, 9-28
error conditions and exceptions, 9-20
events, 9-5
examples, 9-31 to 9-47
firing, 9-1
FOR EACH ROW clause, 9-13
generating derived column values, 9-46
illegal SQL statements, 9-22
INSTEAD OF triggers, 9-8
integrity constraints vs., 9-2, 9-37
listing information about, 9-29
modifying, 9-28
multiple same type, 9-7
mutating tables and, 9-22
naming, 9-4
package variables and, 9-7
privileges, 9-26
to drop, 9-28
procedures and, 9-22
recompiling, 9-27
REFERENCING option, 9-19
referential integrity and, 9-38 to 9-42
remote dependencies and, 9-22
remote exceptions, 9-20
resource manager events, 10-7
restrictions, 9-14, 9-21
row, 9-13
row evaluation order, 9-7
scan order, 9-7
stored, 9-26
triggers (continued)
system triggers, 9-4
on DATABASE, 9-4
on SCHEMA, 9-4
trigger evaluation order, 9-7
use of LONG and LONG RAW datatypes, 9-22
username reported in, 9-25
WHEN clause, 9-14
TRUNC function, 2-21
TRUST keyword, 7-61
type attribute, PL/SQL, 1-5

U

undo data, 15-2
UNDO_MANAGEMENT configuration parameter, 15-4
UNDO_RETENTION configuration parameter, 15-4
UNDO_TABLESPACE configuration parameter, 15-4
unhandled exceptions, 7-38
UNIQUE key constraints
combining with NOT NULL constraint, 3-5
composite keys and nulls, 3-7
disabling, 3-21
enabling, 3-20
PRIMARY KEY constraint vs., 3-6
when to use, 3-6
updatable view, definition, 9-8
UPDATE statement
column values and triggers, 9-17
data consistency, 5-10
triggers and, 9-6, 9-20
triggers for referential integrity, 9-40, 9-41
update, distributed, definition, 7-49
UPPER function, 4-9
URLs, 13-16
UROWID datatype, 2-8
USER function, 3-5
user locks, requesting, 5-17
USER_ERRORS view, debugging stored procedures, 7-35
USER_SOURCE view, 7-35
user-defined errors, 7-35, 7-37
usernames, as reported in a trigger, 9-25
UTL_HTTP package, 13-16
UTL_INADDR package, 13-16
UTL_SMTP package, 13-15
UTL_TCP package, 13-16
UTLLOCKT.SQL script, 5-19

V

VARCHAR datatype, , S
VARCHAR2 datatype, 2-4, 2-8
column length, 2-9
VBScript, translating to PSP, 13-20
VERSIONS_ENDSCN pseudocolumn, 15-11
VERSIONS_ENDTIME pseudocolumn, 15-11
VERSIONS_OPERATION pseudocolumn, 15-11
VERSIONS_STARTSCN pseudocolumn, 15-11
VERSIONS_STARTTIME pseudocolumn, 15-11
VERSIONS_XID pseudocolumn, 15-11
views
containing expressions, 9-9
FLASHBACK_TRANSACTION_QUERY, 15-12
inherently modifiable, 9-9
modifiable, 9-9
pseudocolumns, 9-9

W

Web pages, dynamic, 13-19
WHEN clause, 9-14
cannot contain PL/SQL expressions, 9-14
correlation names, 9-17
examples, 9-2, 9-13, 9-38
EXCEPTION examples, 9-20, 9-38, 9-43, 9-45
WITH CONTEXT clause, 8-33
WNDS argument, 7-60
WNPS argument, 7-60
wrapper to hide PL/SQL code, 7-20

X

X locks, LOCK TABLE statement, 5-15
XA library, 16-1 to 16-36
xa_open string, 16-9
XML
as document type for PSP file, 13-22
searching with Oracle9i Text, 2-34
XML data, representing, 2-47
X/Open distributed transaction processing architecture, 16-2

Y

year 2000, 2-25

Z

zero values, 2-14