Index

A B C D E F G H I J K L M N& nbsp; 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, definitio n, 1-4

4GL, definition, 1-3

A

Active Data Object, translating to PSP, 13-20

Acti ve Server Pages, translating to PSP, 13-20

AFTER SUSPEND event, handling sus pended storage allocation, 5-39

AFTER triggers

auditing and, 9-33, 9-35

correlati on names and, 9-17

specifying, 9-6

agent, definition, 11-4

ALL_ERRORS view, debugging stored procedures, 7-35

ALL_SOURCE view, 7-3 5

ALTER SESSION statement, SERIALIZABLE clause, 5-23

ALTER TABLE statement

defining integrity constraints, 3-18< /dd>

DISABLE ALL TRIGGERS clause, 9-29

DISABLE CONSTRAINT c lause, 3-22

DROP CONSTRAINT clause, 3-25< /dd>

ENABLE ALL TRIGGERS clause, 9-29

ENABLE CONSTRAINT cla use, 3-21

ALTER TABLE statement (continued )

INITRANS parameter, 5-23

RETENTION opt ion, 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

aut onomous 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 colu mn 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-pa dded 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

< h2 class="GroupTitlesIX">C

call specificatio ns, 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

charact er 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 trigg ers, 9-17

default values, 3-5, 7-51

generating derived values with triggers, 9-46< /dd>

columns (continued)

listing in an UPDATE trigger, 9-6, 9-20

multiple foreign key c onstraints, 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 predica tes, trigger bodies, 9-15, 9-19

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

< /dl>

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 optio n, 9-19

cursor and cursor variable, definitions, 5-8

cursors, 5-8

cance ling, 5-10

cursors, 5-8 (continued)

closing, 5-10

declaring and opening cursor var iables, 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, < a href="adfns_ty.htm#1007324">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

n ative floating point, 2-11

native floating-point, IEEE 754 exceptions not ra ised, 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 arit hmetic, 2-43

functions for, 2-2 2

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

< dd class="L1IX">DBMS_LOCK package, 5-17

DBMS_RESUMABLE package, handling sus pended 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 consistenc y, 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

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

< dd class="L1IX">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

i ndexes, 4-6

integrity constraints, 3-25

packages, 7-11

procedures, 7-11

triggers, 9-28

dynamic SQL

application development languages, 6-20

invok er's rights, 6-7

invoking PL/SQL blocks, 6-6< /a>

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, representi ng, 2-44

E

e-mail, sending from PL/SQL, 13-15

embedded SQL, 7-2

enabled integrit y 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, 1 0-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< /dd>

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

< dd class="L1IX">explicit locks, 5-10

expression filtering, 2-32

expressions, conditional in WHERE clause, 2-32

< dd class="L1IX">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

definitio n, 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

ge ographic coordinate data, representing, 2-33

global entity, definition, 7-15

global OWA, definition, 13-6

grantee event attribute, 10-3

HEXTORAW function, 2-41

hiding PL/SQL code, 7-20

hostname, 13-16

HTML

display ing 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-1 4

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

< dd class="L2IX">on nested table view columns, 9-18

integrity const raints

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

viola tions, 3-20

when to use, 3-2

interactive block execution, 7-44

INTERVAL DAY TO SECOND datatype, 2-6, 2-20

INTERVAL YEAR TO MON TH datatype, 2-6, 2-20

invoker's-rights p rocedure, 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 (continu ed)

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

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

mail, sending from PL/SQL, 13-15

main transaction, definition, 5-29

manual locks, 5-10

LOCK TABLE s tatement, 5-11

mask, format, definition, 2-28

match full rule for NULL values, 3-10

m atch 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 t able, 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 wit h 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 flo ating-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 na me, 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) va lue, 2-14

NOT NULL constraint

CHECK cons traint 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

application s, 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 relat ionship, 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 attrib ute, 10-4

ora_sysevent event attribute, 10-4< /a>

ora_with_grant_option event attribute, 10-7

ORA-21 301 error, fixing, 16-12, 16-14

OraAQ obj ect, 1-36

OraAQAgent object, 1-37

OraAQMsg object, 1-37

OraBFILE object, 1-38

OraBLOB object, 1-38

Oracle Applicat ion 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 J Developer, 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 serve r, 1-16

stored programs, 1-16

OraCLOB object, 1-38

OraDatabase object, 1-34

OraDynaset object, 1-34

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

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

PARAL LEL_ENABLE keyword, 7-56

parameters

defa ult 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 an d temporary LOB instances, 2-34

platform-specific Oracle Database documentat ion, 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< /a>

exception handlers, 7-2

functions

arguments, 7-53

overloading, 7-63

parameter default values, 7-53

purity lev el, 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, < a href="adfns_we.htm#1016906">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_REFEREN CES, 7-59

SERIALLY_REUSABLE pragma, 7-63, 7-64

precompilers, 7-45

applications, 7-4

calling stored procedures an d 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 procedur e 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 functi on, 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-o ne 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-2 0

remote queries, flashback features, 15-16

REMOTE _DEPENDENCIES_MODE parameter, 7-28

repeatable reads, 5-7, 5-10

resource manager, 16-2< /a>

events, 10-7

RESTRICT_REFE RENCES pragma, 7-59

syntax for, 7-60

restrictions, system triggers, 9-25

resumable storage allocation, 5-38

definition, 5-38

examples, 5-39

RE TENTION 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 transactio ns, 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 tri ggers

defining, 9-13

REFERENCING option, 9-19

timing, 9-6

U PDATE statements and, 9-6, 9-20

ROWID, definition, 2-37

ROWID datatype, 2-8< /a>, 2-36

extended ROWID format, 2-36

migration, 2-38

ROWIDTOCHAR funct ion, 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

< dd class="L1IX">RX locks, LOCK TABLE statement, 5-12

S

S lock s, LOCK TABLE statement, 5-12

SAVEPOINT statement, 5-6

savepoints

maximum number of, 5-6

rolling back to, 5-6

scalability, se rially reusable packages, 7-63

scope

aut onomous, definition, 5-29

scripting, 13-19

scrollable cursors, 1-23

search data, repr esenting, 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

lo ading a procedure, 7-10

SET SERVEROUTPUT ON command, 7-3

SHOW ERRORS command, 7-34

SQ L/DS datatypes, 2-39

SRX locks, LOCK Table statement, 5-14

standards, IEEE 754, 2-11

state

package, definition, 7-63

session, package o bjects, 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 SQ L 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

< dd class="L1IX">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, defa ult 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 an d 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, defini tion, 9-22

tables

constraining, 9-22

tables (continued)

in PL/SQL, 7-8

mutating, 9-22

TCP/IP, 13-16

temporary and permanent LOB ins tances, 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, re presenting, 2-20

time zones, functions, 2-23< /a>

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 f unction, 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

tr igger

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)

a uditing 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< /a>

compiled, 9-26

conditional predicates, 9-15, 9-19

creating, 9-2, 9-21, 9-26

data access restricti ons, 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

ge nerating derived column values, 9-46

illegal SQL statements, 9-22

INSTEAD OF triggers, 9-8

inte grity 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 vari ables and, 9-7

privileges, 9-26

to drop, 9-28

procedures and, 9-22

recompiling, 9-27

REFER ENCING option, 9-19

referential integrity and, 9-38 to 9-42

remote dependencies and, 9-22

remote exceptions, 9-20

resource man ager events, 10-7

restrictions, 9-14, 9-21

row, 9-13

row eva luation order, 9-7

scan order, 9-7

< dd class="L2IX">stored, 9-26

triggers (continue d)

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, < a href="adfns_tr.htm#1007461">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, < a href="adfns_sq.htm#1025038">5-17

USER_ERRORS view, debugging stored procedures, 7-35

USER_SOURCE view, 7-35

user-defined err ors, 7-35, 7-37

usernames, as reported in a trigger, 9-25

UTL_HTTP package, 13-16< /dd>

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 pseud ocolumn, 15-11

VERSIONS_STARTTIME pseudocolumn, 15-11

VERSIONS_XID pseudocolumn, 15-11

views < dl class="L2IX">

containing expressions, 9-9

FLASHBACK_TRAN SACTION_QUERY, 15-12

inherently modifiable, 9 -9

modifiable, 9-9

pseudocolumns, 9-9

W

Web pages, dynamic, 13-19< /dd>

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 CON TEXT 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< /a>

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, < a href="adfns_ty.htm#1015596">2-14