Oracle9i Application Developer's Guide

%ROWTYPE attribute, 9-7

used in stored functions, 9-9

%TYPE attribute, 9-7

Active Data Object

translating to PSP, 18-15

Active Server Pages

translating to PSP, 18-15

ADD_CONTEXT procedure, 12-50

ADD_GROUPED_POLICY procedure, 12-50

ADD_POLICY procedure, 12-50

AFTER SUSPEND event

handling suspended storage allocation, 7-40

AFTER triggers

auditing and, 15-34, 15-37

correlation names and, 15-16

specifying, 15-6

ALL_ERRORS view

debugging stored procedures, 9-36

ALL_SOURCE view, 9-36

ALTER privilege, 11-24

ALTER SEQUENCE statement, 2-24

ALTER SESSION SET SCHEMA statement, 12-6

ALTER SESSION statement

SERIALIZABLE clause, 7-24

SET SCHEMA, 11-20

ALTER TABLE statement

defining integrity constraints, 4-19

DISABLE ALL TRIGGERS clause, 15-30

DISABLE CONSTRAINT clause, 4-23

DROP CONSTRAINT clause, 4-26

ENABLE ALL TRIGGERS clause, 15-30

ENABLE CONSTRAINT clause, 4-22

INITRANS parameter, 7-24

ALTER TRIGGER statement

DISABLE clause, 15-30

ENABLE clause, 15-29

American National Standards Institute (ANSI)

ANSI-compatible locking, 7-16

anonymous PL/SQL blocks, 11-18

about, 9-2

compared to triggers, 9-21

ANSI SQL92

FIPS flagger, 7-2

AnyData datatype, 3-41

AnyDataSet datatype, 3-41

AnyType datatype, 3-41

application context

as secure data cache, 12-8

bind variables, 12-9

creating, 12-16

examples, 12-17

fine-grained access control, 11-9, 12-8

how to use, 12-13

introduction, 11-4, 12-2

parallel query, 12-15

performance, 12-20

returning predicate, 12-9

security features, 12-2

setting, 12-16

USERENV namespace, 12-3

using in policy, 12-17

versioning, 12-16

application roles, 11-14

application security

considerations for use, 11-11

introduction, 11-4, 11-10

limitations, 12-60

specifying attributes, 12-3

through validation, 12-3

applications

administrator, 11-13

calling stored procedures and packages, 9-45

database users, 11-11

One Big Application User model, 11-11, 11-12

roles, 11-10, 11-17

security, 11-12, 12-58

unhandled exceptions in, 9-39

AS OF clause of SELECT statement, 7-45

attributes, USERENV, 12-5

auditing

compromised by One Big Application User, 11-11

n-tier systems, 13-10

triggers and, 15-33

authentication

compromised by One Big Application User, 11-11

n-tier systems, 13-6

AUTHENTICATION_DATA attribute, 12-6

AUTHENTICATION_TYPE attribute, 12-6

automatic segment-space management, 3-30

autonomous routine, 7-31

autonomous scope

versus autonomous transaction, 7-31

autonomous transactions, 7-31 to 7-39

AUTONOMOUS_TRANSACTION pragma, 7-31

BEFORE triggers

complex security authorizations, 15-46

correlation names and, 15-16

derived column values, 15-47

specifying, 15-6

BFILEs, 3-5, 3-8

binary data

RAW and LONG RAW, 3-32

bind variables, 12-9

blank padding data

performance considerations, 3-13

BLOB (binary large object) datatype, 3-5, 3-8

Boolean expressions, 3-41

bulk binds, 9-18

DML statements, 9-19

FOR loops, 9-20

SELECT statements, 9-20

usage, 9-19

BY REF phrase, 10-30

BYTE qualifier for column lengths, 3-11

CACHE option

CREATE SEQUENCE statement, 2-27

caches

sequence numbers, 2-23, 2-27

call specifications, 10-4 to 10-52

callbacks, 10-47 to 10-48

canceling a cursor, 7-10

CASCADE CONSTRAINTS option, 11-35

CATPROC.SQL script, 16-2

CC datetime format element, 3-21

century, 3-20

date format masks, 3-16

CGI variables, 18-13

CHAR datatype, 3-10

column length, 3-11

CHAR qualifier for column lengths, 3-11

character data

representing, 3-10

CHARSETFORM property, 10-26

CHARSETID property, 10-26

CHARTOROWID function, 3-38

CHECK constraint

triggers and, 15-39, 15-45

check constraints

how to use, 4-16 to 4-22

client events, 16-9

CLIENT_INFO attribute, USERENV, 12-5

CLOB datatype, 3-10

columns

accessing in triggers, 15-16

default values, 4-5

generating derived values with triggers, 15-47

listing in an UPDATE trigger, 15-6, 15-19

multiple foreign key constraints, 4-12

number of CHECK constraints limit, 4-17

specifying length in bytes or characters, 3-11

columns, privileges, 11-32, 11-34

COMMIT statement, 7-5

comparison operators

blank padding data, 3-13

comparing dates, 3-16

compile-time errors, 9-34

compiling PL/SQL procedures to native code, 9-22

composite keys

restricting nulls in, 4-18

concurrency, 7-20

conditional predicates

trigger bodies, 15-13, 15-18

connection pooling, 1-24

consistency

read-only transactions, 7-8

constraining tables, 15-23

constraints

restriction on stored functions, 9-51

See integrity constraints, 4-1

context switches

reducing with bulk binds, 9-18

conversion functions, 3-38

TO_CHAR function

year and century considerations, 3-20

TO_DATE function, 3-20

converting data, 3-38

ANSI datatypes, 3-37

assignments, 3-38

expression evaluation, 3-40

SQL/DS and DB2 datatypes, 3-37

cookies, 18-13

CORBA, 1-20

correlation names, 15-13 to 15-18

NEW, 15-16

OLD, 15-16

REFERENCING option and, 15-18

when preceded by a colon, 15-16

CREATE CONTEXT statement, 12-16

CREATE INDEX statement, 5-7

CREATE PACKAGE BODY statement, 9-15

CREATE PACKAGE statement, 9-15

CREATE ROLE statement, 11-25

CREATE SCHEMA statement, 2-30, 11-20

privileges required, 2-31

CREATE SEQUENCE statement

CACHE option, 2-23, 2-27

examples, 2-27

NOCACHE option, 2-28

CREATE SESSION statement, 11-20

CREATE TABLE statement, 2-3

defining integrity constraints, 4-18

INITRANS parameter in, 7-24

CREATE TRIGGER statement, 15-2

REFERENCING option, 15-18

CREATE VIEW statement, 2-9

OR REPLACE option, 2-12

WITH CHECK OPTION, 2-9, 2-13

CREATE_POLICY_GROUP procedure, 12-50

creating

indexes, 5-7

integrity constraints, 4-2

multiple objects, 2-30

packages, 9-15

sequences, 2-27, 2-28

synonyms, 2-29

tables, 2-3

triggers, 15-2, 15-21

views, 2-9

CURRENT_SCHEMA attribute, USERENV, 12-6

CURRENT_USER attribute, USERENV, 12-5

CURRVAL pseudo-column, 2-25

restrictions, 2-26

cursors, 7-9

canceling, 7-10

closing, 7-10

declaring and opening cursor variables, 9-32

maximum number of, 7-9

pointers to, 9-31

private SQL areas and, 7-9

shared, 12-9

data blocks

shown in ROWIDs, 3-34

data control in OO4O, 1-39

data conversion, 3-38

ANSI datatypes, 3-37

assignments, 3-38

expression evaluation, 3-40

SQL/DS and DB2 datatypes, 3-37

data dictionary

compile-time errors, 9-36

integrity constraints in, 4-29

procedure source code, 9-36

schema object views, 2-34

data encryption, 11-5

data object number

extended ROWID, 3-33, 3-34

database

application administrator, 11-13

applications and security, 11-10

event notification, 16-1, 17-5

global name in a distributed system, 2-31

security and schemas, 11-20

user and application user, 11-11

datafiles

shown in ROWIDs, 3-34

datatypes, 3-2

ANSI/ISO, 3-37

BFILE, 3-3

BLOB, 3-3

CHAR, 3-2, 3-10

choosing a character datatype, 3-11

CLOB, 3-2

column lengths for character types, 3-11

data conversion, 3-38

DATE, 3-2, 3-20

DB2, 3-37

INTERVAL DAY TO SECOND, 3-2

INTERVAL YEAR TO MONTH, 3-2

LONG, 3-2

LONG RAW, 3-3

MDSYS.SDO_GEOMETRY, 3-27

NCHAR, 3-2, 3-10

NCLOB, 3-2

NUMBER, 3-2

NVARCHAR2, 3-2, 3-10

RAW, 3-3

ROWID, 3-3, 3-33

SQL/DS, 3-37

summary of datatypes, 3-3

TIMESTAMP, 3-2

TIMESTAMP WITH LOCAL TIME ZONE, 3-3

TIMESTAMP WITH TIME ZONE, 3-3

VARCHAR, 3-10

VARCHAR2, 3-2, 3-10

date and time data

representing, 3-14

date arithmetic, 3-40

functions for, 3-17

DATE datatype, 3-14

centuries, 3-20

data conversion, 3-38

DB_DOMAIN attribute, USERENV, 12-6

DB2 datatypes, 3-37

DBA_ERRORS view

debugging stored procedures, 9-36

DBA_ROLE_PRIVS view, 11-14

DBA_SOURCE view, 9-36

DBMS_FLASHBACK package

using in applications, 7-45

DBMS_LOCK package, 7-18

DBMS_OBFUSCATION_TOOLKIT, 14-1, 14-6

DBMS_RESUMABLE package

handling suspended storage allocation, 7-40

DBMS_RLS package, 12-49

DBMS_SESSION package

SET_CONTEXT procedure, 12-16

SET_ROLE procedure, 11-18

DBMS_SQL package

advantages of, 8-16

client-side programs, 8-16

DESCRIBE, 8-16

differences with native dynamic SQL, 8-11

multiple row updates and deletes, 8-16

RETURNING clause, 8-16

See Also dynamic SQL

SET_ROLE procedure, 11-19

DBMS_TYPES package, 3-41

DBMS_XMLGEN package, 3-44

DBMS_XMLQUERY package, 3-44

DBMS_XMLSAVE package, 3-44

DDL statements

package state and, 9-17

DEBUG_EXTPROC package, 10-50

debugging

stored procedures, 9-41

triggers, 15-29

dedicated external procedure agents, 10-6

default

column values, 4-5, 9-51

maximum savepoints, 7-6

parameters in stored functions, 9-53

role, 11-26

DELETE privilege, 11-24

DELETE statement

column values and triggers, 15-16

data consistency, 7-11

triggers for referential integrity, 15-41, 15-42

dependencies

among PL/SQL library objects, 9-22

in stored triggers, 15-27

schema objects

trigger management, 15-21

the timestamp model, 9-23

DESC function, 5-9

DETERMINISTIC keyword, 9-57

dictionary_obj_owner event attribute, 16-3

dictionary_obj_owner_list event attribute, 16-3

dictionary_obj_type event attribute, 16-3

disabling

integrity constraints, 4-21

roles, 11-6

triggers, 15-29, 15-30

Distinguished Name, 11-21

distributed databases

referential integrity and, 4-15

remote stored procedures, 9-47, 9-48

triggers and, 15-21

distributed queries

handling errors, 9-40

distributed transaction processing architecture, 20-2

DML_LOCKS parameter, 7-11

DROP INDEX statement, 5-6

DROP ROLE statement, 11-29

DROP TRIGGER statement, 15-29

DROP_CONTEXT procedure, 12-50

DROP_GROUPED_POLICY procedure, 12-50

DROP_POLICY procedure, 12-50

dropping

indexes, 5-6

integrity constraints, 4-26

packages, 9-12

procedures, 9-11

roles, 11-29

sequences, 2-28

synonyms, 2-30

triggers, 15-29

views, 2-14

dynamic SQL, 12-43, 12-59

application development languages, 8-21

invoker-rights, 8-7

invoking PL/SQL blocks, 8-6

optimization, 8-5

queries, 8-4

scenario, 8-8

See Also DBMS_SQL package

See Also native dynamic SQL

usage, 8-3

dynamic web pages, 18-13

dynamically typed data

representing, 3-41

EJB, 1-20

e-mail

sending from PL/SQL, 18-9

embedded SQL, 9-2

ENABLE_GROUPED_POLICY procedure, 12-50

ENABLE_POLICY procedure, 12-50

enabling

integrity constraints, 4-21

roles, 11-6

triggers, 15-29

encryption, 11-5

Enterprise JavaBeans, 1-20

enterprise user management, 11-12

enterprise users, 11-21

errors

application errors raised by Oracle packages, 9-37

creating views with errors, 2-11

remote procedures, 9-40

user-defined, 9-36, 9-38

event attribute functions, 16-2

event publication, ?? to ?? to 15-53, 16-1

triggering, 15-52

event triggers, 12-23

exception handlers

in PL/SQL, 9-2

exceptions

anonymous blocks, 9-3

during trigger execution, 15-19

effects on applications, 9-39

remote procedures, 9-40

unhandled, 9-39

exclusive locks

LOCK TABLE statement, 7-15

EXECUTE privilege, 11-24

explicit locking

manual locking, 7-11

extended ROWID format, 3-33

external procedure, 10-3

DEBUG_EXTPROC package, 10-50

debugging, 10-50

maximum number of parameters, 10-52

restrictions, 10-52

specifying datatypes, 10-17

EXTERNAL_NAME attribute, USERENV, 12-6

EXTPROC process

running on different machines, 10-6

extproc process, 10-36

features, new, xxix

fine grained access control, 11-4

fine-grained access control

application context, 11-9, 12-8

features, 12-40

introduction, 12-40

performance, 12-43

fine-grained auditing

introduction, 11-4

FIPS flagger

interactive SQL statements and, 7-2

FIXED_DATE initialization parameter, 3-16

flashback query

and AS OF clause of SELECT statement, 7-45

and DBMS_STATS package, 7-47

examples, 7-48

using, 7-43

FOR EACH ROW clause, 15-11

FORALL statement

using, 9-18

foreign key constraints

defining, 4-27, 4-28

enabling, 4-21, 4-29

NOT NULL constraint and, 4-11

one-to-many relationship, 4-11

one-to-n relationships, 4-11

UNIQUE key constraint and, 4-11

format masks

TO_DATE function, 3-15

full-text search

using Oracle9i Text, 3-27

functions

See Also PL/SQL

geographic coordinate data

representing, 3-27

GRANT ANY OBJECT PRIVILEGE, 11-33

GRANT OPTION, 11-33

GRANT statement

ADMIN OPTION, 11-30

effective time, 11-38

last DDL time, 11-36

object privileges, 11-22, 11-32

system privileges, 11-30

grantee event attribute, 16-3

granting

roles, 11-30

system privileges, 11-30, 11-31

HEXTORAW function, 3-38

hiding PL/SQL code, 9-21

hostname, 18-10

HTML

displaying within PSP files, 18-16

retrieving from PL/SQL, 18-10

HTP and HTF packages, 18-13

HTTP URLs, 18-10

IBM datatypes, 3-37

image maps, 18-13

IN OUT parameter mode, 9-6

IN parameter mode, 9-6

INDEX privilege, 11-24

indexes

creating, 5-7

dropping, 5-6

function-based, 5-8

guidelines, 5-3

order of columns, 5-4

privileges, 5-6

SQL*Loader and, 5-3

temporary segments and, 5-2

when to create, 5-2

index-organized tables, 6-1 to 6-20

INDICATOR property, 10-25

initialization parameters

DML_LOCKS, 7-11

OPEN_CURSORS, 7-9

REMOTE_DEPENDENCIES_MODE, 9-29

ROW_LOCKING, 7-11

SERIALIZABLE, 7-11

INITRANS parameter, 7-24

INSERT privilege, 11-24

INSERT statement

column values and triggers, 15-16

read consistency, 7-11

instance_num event attribute, 16-3

INSTEAD OF triggers, 15-7

on nested table view columns, 15-17

integrity constraints

CHECK, 4-16 to ??

composite UNIQUE keys, 4-7

defining, 4-18

disabling, 4-20, 4-22, 4-23

dropping, 4-26

enabling when violations exist, 4-21

examples, 4-2

exceptions to, 4-24

listing definitions of, 4-29

naming, 4-20

NOT NULL, 4-3

performance considerations, 4-3

PRIMARY KEY, 4-6

privileges required for creating, 4-19

renaming, 4-25

triggers vs., 15-2, 15-38

UNIQUE, 4-7

violations, 4-21

when to use, 4-2

interactive block execution, 9-44

INTERVAL DAY TO SECOND datatype, 3-14

INTERVAL YEAR TO MONTH datatype, 3-14

invalid views, 2-14

invoker's rights stored procedures, 11-18

invoker-rights

dynamic SQL, 8-7

is_alter_column event attribute, 16-3

ISDBA attribute, USERENV, 12-5

ISOLATION LEVEL

changing, 7-24

SERIALIZABLE, 7-24

J2EE, 1-20

JAAS, 1-20

Java

calling methods through call specifications, 10-4

generating wrapper classes with JPublisher, 1-16

in the RDBMS, 1-8

loading into the database, 10-5

overview of JDBC, 1-9

overview of SQLJ, 1-14

vs PL/SQL, 1-42

Javascript

translating to PSP, 18-15

JavaServer Pages

translating to PSP, 18-15

JDBC

See Oracle JDBC, 1-9

join views, 2-15

DELETE statements, 2-18

key-preserved tables in, 2-16

mergeable, 2-9, 2-16

modifying, 2-17

UPDATE statements, 2-18

when modifiable, 2-15

JPublisher, 1-18

JScript

translating to PSP, 18-15

JSPs, 1-20

key-preserved tables

in join views, 2-16

in outer joins, 2-21

keys

foreign keys, 4-27

unique

composite, 4-7

large datatypes

representing with LOBs, 3-27

libraries, 1-41

library units

remote dependencies, 9-22

Lightweight Directory Access Protocol (LDAP), 12-20

lightweight sessions, 13-6

loadjava utility, 1-20

loadpsp command, 18-23

LOB datatype

support in OO4O, 1-37

use in triggers, 15-17

LOCK TABLE statement, 7-12

locking

manual (explicit), 7-11

locks

distributed, 7-11

LOCK TABLE statement, 7-12, 7-13

privileges for manual acquirement, 7-15

user locks, 7-18

UTLLOCKT.SQL script, 7-20

login triggers, 12-13, 12-16, 12-17

LONG columns

restrictions on, 3-30

where referenced from, 3-29

LONG datatype, 3-10

in triggers, 3-31

switching to LOB datatypes, 3-28

use in triggers, 15-21

LONG RAW datatype, 3-32

LOWER function, 5-9

mail

sending from PL/SQL, 18-9

manual locking, 7-11

LOCK TABLE statement, 7-12

MAX_ENABLED_ROLES parameter, 11-26, 11-29

MDSYS.SDO_GEOMETRY datatype, 3-27

memory

scalability, 9-64

middle tier systems, 12-4

migration

ROWID format, 3-35

modes

of parameters, 9-6

modifiable join view

definition of, 2-15

mutating tables, 15-23

name resolution, 2-32

native dynamic SQL

advantages of, 8-12

differences with DBMS_SQL package, 8-11

fetching into records, 8-15

performance, 8-14

See Also dynamic SQL

user-defined types, 8-15

native execution

of PL/SQL procedures, 9-22

NCHAR datatype, 3-2, 3-10

NCLOB datatype, 3-10

NEW correlation name, 15-16

new features, xxix

NEXTVAL pseudo-column, 2-25

restrictions, 2-26

NLS_DATE_FORMAT parameter, 3-15

NLSSORT order, and indexes, 5-9

NOCACHE option

CREATE SEQUENCE statement, 2-28

NOT NULL constraint

CHECK constraint and, 4-18

data integrity, 4-21

when to use, 4-3

NOWAIT option, 7-12

NUMBER datatype, 3-13

numeric data

representing, 3-13

NVARCHAR2 datatype, 3-2, 3-10

OAS, 18-13

object columns, indexes on, 5-9

object support in OO4O, 1-37

objects

GRANT OPTION, 11-33

granting privileges, 11-23, 11-32

privileges, 11-22

revoking privileges, 11-34

objects, schema

listing information, 2-34

name resolution, 2-32

renaming, 2-33

OC4J, 1-20

OCCI

overview, 1-26

OCI, 9-2

applications, 9-4

cancelling cursors, 7-10

closing cursors, 7-10

enabling roles, 11-7

overview, 1-26

parts of, 1-28

vs precompilers, 1-40

OLD correlation name, 15-16

one-to-many relationship

with foreign keys, 4-11

one-to-one relationship

with foreign keys, 4-11

OO4O

See Oracle Objects for OLE, 1-32

open string for XA, 20-10

OPEN_CURSORS parameter, 7-9

operating system

roles, 11-29

OR REPLACE clause

for creating packages, 9-16

ora_dictionary_obj_owner event attribute, 16-3

ora_dictionary_obj_owner_list event attribute, 16-3

ora_dictionary_obj_type event attribute, 16-3

ora_grantee event attribute, 16-3

ora_instance_num event attribute, 16-3

ora_is_alter_column event attribute, 16-3

ora_is_creating_nested_table event attribute, 16-4

ora_is_drop_column event attribute, 16-4

ora_is_servererror event attribute, 16-4

ora_login_user event attribute, 16-4

ora_privileges event attribute, 16-4

ora_revokee event attribute, 16-4

ora_server_error event attribute, 16-4

ora_sysevent event attribute, 16-4

ora_with_grant_option event attribute, 16-7

ORA-21301 error, fixing, 20-15

OraAQ object, 1-36

OraAQAgent object, 1-37

OraAQMsg object, 1-37

OraBFILE object, 1-38

OraBLOB object, 1-38

Oracle Advanced Security, 11-21

Oracle Application Server (OAS), 18-13

Oracle Call Interface

See OCI

Oracle Data Control (ODC), 1-39

Oracle errors, 9-3

Oracle Internet Directory, 13-5

Oracle JDBC

definition, 1-9

example, 1-12

OCI driver, 1-10

Oracle extensions, 1-11

server driver, 1-11

stored procedures, 1-9

thin driver, 1-10

Oracle Objects for OLE

automation server, 1-32

C++ Class Library, 1-39

data control, 1-39

LOB and object support, 1-37

object model, 1-32

overview, 1-31

Oracle SQLJ

advantages over JDBC, 1-15

definition, 1-14

design, 1-15

example, 1-17

in the server, 1-20

stored programs, 1-20

Oracle supplied packages, 9-17

OraCLOB object, 1-38

OraDatabase object, 1-34

OraDynaset object, 1-34

OraField object, 1-35

OraMeta Data object, 1-35

OraParamArray object, 1-36

OraParameter object, 1-35

OraServer object, 1-33

OraSession object, 1-33

OraSQLStmt object, 1-36

OS_ROLES parameter, 11-29

OS_USER attribute, USERENV, 12-6

OUT parameter mode, 9-6

outer joins, 2-20

key-preserved tables in, 2-21

overloading

of packaged functions, 9-63

stored procedure names, 9-13

using RESTRICT_REFERENCES, 9-63

OWA* packages, 18-13

package body, 9-13

package specification, 9-13

packages, 1-41

creating, 9-15

DBMS_OUTPUT

example of use, 9-3

DEBUG_EXTPROC, 10-50

dropping, 9-12

in PL/SQL, 9-13

naming of, 9-16

privileges for execution, 9-46

privileges required to create, 9-16

privileges required to create procedures in, 9-10

serially reusable packages, 9-64

session state and, 9-17

synonyms, 9-50

where documented, 9-17

parallel execution servers, 12-15

parallel query, and SYS_CONTEXT, 12-15

parallel server

distributed locks, 7-11

sequence numbers and, 2-24

PARALLEL_ENABLE keyword, 9-57

parameters

default values, 9-9

with stored functions, 9-53

modes, 9-6

parse tree, 15-27

Pascal Calling Standard, 10-10

passwords

role, 11-8, 11-27

pcode

when generated for triggers, 15-27

performance

index column order, 5-4

native dynamic SQL, 8-14

PL, 8-7

PL/SQL, 9-2

advantages, 1-4

anonymous blocks, 9-2, 11-18

calling remote stored procedures, 9-48

cursor variables, 9-31

dependencies among library units, 9-22

dynamically modifying SQL statements, 12-59

exception handlers, 9-2

features, 1-4

functions

arguments, 9-53

overloading, 9-63

parameter default values, 9-53

purity level, 9-63

RESTRICT_REFERENCES pragma, 9-60

using, 9-50

hiding source code, 9-21

invoking with dynamic SQL, 8-6

objects, 1-6

packages, 9-13

program units, 9-2

replaced views and, 2-12

RAISE statement, 9-38

sample code, 1-4

serially reusable packages, 9-64

server pages, 18-13 to 18-24

setting context, 12-13

tables, 9-9

of records, 9-9

trigger bodies, 15-13, 15-16

user-defined errors, 9-37

vs Java, 1-42

web toolkit, 18-13

wrapper to hide code, 9-21

PLSQL_COMPILER_FLAGS initialization parameter, 9-22

pragma, 7-31, 7-39

RESTRICT_REFERENCES pragma, 9-60

SERIALLY_REUSABLE pragma, 9-64, 9-65

precompilers, 9-45

applications, 9-4

calling stored procedures and packages, 9-45

vs OCI, 1-40

PRIMARY KEY constraints

choosing a primary key, 4-6

disabling, 4-22

enabling, 4-21

multiple columns in, 4-6

UNIQUE key constraint vs., 4-7

private SQL areas

cursors and, 7-9

privileges

altering sequences, 2-24

creating integrity constraints, 4-19

creating triggers, 15-26

dropping a view, 2-14

dropping sequences, 2-28

dropping triggers, 15-29

encapsulating in stored procedures, 11-7

granting, 11-30, 11-32

granting to PUBLIC, 11-37

index creation, 5-6

managing, 11-13, 11-22

manually acquiring locks, 7-15

middle tier, 13-7

object, 11-23

on selected columns, 11-34

recompiling triggers, 15-28

renaming objects, 2-34

replacing views, 2-12

revoking, 11-34

sequence creation, 2-24

SQL statements permitted, 11-23

stored procedure execution, 9-46

synonym creation, 2-29

triggers, 15-26

using a view, 2-14

using sequences, 2-28

view creation, 2-11

Pro*C/C++

overview of application development, 1-21 to ??

Pro*COBOL

overview of application development, 1-24 to ??

procedures

called by triggers, 15-21

external, 10-3

PRODUCT_USER_PROFILE table, 11-7, 12-58

program units in PL/SQL, 9-2

property

CHARSETFORM, 10-26

CHARSETID, 10-26

INDICATOR, 10-25

proxy authentication, 11-5

PROXY_USER attribute, 12-4, 12-6

pseudocolumns

modifying views, 15-8

PSP

See PL/SQL server pages

.psp files, 18-15

PUBLIC user group, 11-37

publish-subscribe, 17-2 to 17-6

purity level, 9-55

queries

capturing as views, 2-9

dynamic, 8-4

errors in distributed queries, 9-40

reconstructing data at a point in time, 7-43

speeding up with temporary tables, 2-4

RAISE statement, 9-38

RAISE_APPLICATION_ERROR procedure, 9-36

remote procedures, 9-40

raising exceptions

triggers, 15-19

RAW datatype, 3-32

RAWTOHEX function, 3-38

RAWTONHEX function, 3-38

read-only transactions, 7-8

reauthenticating clients, 13-5, 13-8

records

in SQL INSERT and UPDATE statements, 8-21

REF column

indexes on, 5-9

REFERENCES privilege, 11-24, 11-35

REFERENCING option, 15-18

referential integrity

distributed databases and, 4-15

one-to-many relationship, 4-11

one-to-one relationship, 4-11

privileges required to create foreign keys, 4-27

self-referential constraints, 15-42

triggers and, 15-39 to 15-43

REFRESH_GROUPED_POLICY procedure, 12-50, 12-51

REFRESH_POLICY procedure, 12-50, 12-51

remote dependencies, 9-22

signatures, 9-24

specifying timestamps or signatures, 9-29

remote exception handling, 9-40, 15-19

REMOTE_DEPENDENCIES_MODE parameter, 9-29

RENAME statement, 2-33

reparsing, 12-16

repeatable reads, 7-8, 7-11

resource manager, 20-2

events, 16-8

RESOURCE privilege, 11-20

RESTRICT_REFERENCES pragma

syntax for, 9-60

using to control side effects, 9-60

restrictions

system triggers, 15-25

resumable storage allocation, 7-40

examples, 7-41

reusable packages, 9-64

REVOKE statement, 11-38

revoking

privileges on selected columns, 11-34

roles and privileges, 11-31

RM (resource manager), 20-2

RNDS argument, 9-60

RNPS argument, 9-60

ROLE_SYS_PRIVS view, 11-14

ROLE_TAB_PRIVS view, 11-14

roles

ADMIN OPTION, 11-31

advantages, 11-14

application, 11-17, 11-22, 12-58

central management, 11-24

creating, 11-24

default, 11-26

dropping, 11-29

enabling, 11-17

enabling and disabling, 11-6

GRANT and REVOKE statements, 11-29

granting, 11-30

granting to PUBLIC, 11-37

managing, 11-22

operating system, 11-29

passwords, 11-8, 11-27

protecting, 11-24

recommended practices, 11-6

restricting from tool users, 12-58

revoking, 11-31

secure application, 11-4

SET ROLE statement, 11-29

system privileges, 11-30

usefulness compromised, 11-12

user, 11-17, 11-22

WITH GRANT OPTION, 11-33

ROLLBACK statement, 7-6

rolling back transactions

to savepoints, 7-6

routines

autonomous, 7-31

external, 10-3

service, 10-37

row locking

manually locking, 7-17

row triggers

defining, 15-11

REFERENCING option, 15-18

timing, 15-6

UPDATE statements and, 15-6, 15-19

ROW_LOCKING parameter, 7-11

ROWID datatype, 3-33

extended ROWID format, 3-33

migration, 3-35

ROWIDTOCHAR function, 3-38

ROWIDTONCHAR function, 3-38

rows

format, 2-2

header, 2-2

shown in ROWIDs, 3-34

size, 2-2

violating integrity constraints, 4-21

ROWTYPE_MISMATCH exception, 9-34

RR date format, 3-20

RS locks

LOCK TABLE statement, 7-13

run-time error handling, 9-36

RX locks

LOCK TABLE statement, 7-13

S locks

LOCK TABLE statement, 7-13

SAVEPOINT statement, 7-6

savepoints

maximum number of, 7-6

rolling back to, 7-6

scalability

serially reusable packages, 9-64

schema-independent users, 11-21, 11-22

schemas

default, 12-6

unique, 11-20

scope, autonomous, 7-31

scripting, 18-13

scrollable cursors, 1-24

search data

representing, 3-27

secure application, xxxv, 11-14

Secure Sockets Layer (SSL) protocol, 13-5

security

application context, 12-2

enforcement in application, 11-12

enforcement in database, 11-12

features of Oracle8i, 11-4

fine-grained access control, 12-40

policies

administering, 12-49

applied within database, 12-60

centrally managed, 12-59

example, 12-43

implementing, 12-8

multiple policies per table, 12-42

on tables or views, 12-40

technical issues, 11-3

policy for applications, 11-10, 12-58

roles, advantages, 11-14

table- or view-based, 12-40

threats and countermeasures, 11-2

SELECT privilege, 11-24

SELECT statement

read consistency, 7-11

SELECT ... FOR UPDATE, 7-17

SEQUENCE_CACHE_ENTRIES parameter, 2-27

sequences

accessing, 2-24

altering, 2-24

caching numbers, 2-23

caching sequence numbers, 2-27

creating, 2-23, 2-27, 2-28

CURRVAL, 2-24, 2-26

dropping, 2-28

initialization parameters, 2-23

NEXTVAL, 2-25

parallel server, 2-24

privileges needed, 2-24 to 2-28

reducing serialization, 2-25

SERIALIZABLE option

for ISOLATION LEVEL, 7-24

SERIALIZABLE parameter, 7-11

serializable transactions, 7-20

serially reusable PL/SQL packages, 9-64

SERIALLY_REUSABLE pragma, 9-65

service routine, 10-37

examples, 10-37

servlets, 1-20

session primitives, 12-3

SESSION_USER attribute, USERENV, 12-5

sessions

package state and, 9-17

SET ROLE statement

ALL EXCEPT option, 11-28

ALL option, 11-28

associating privileges with role, 11-17

at startup, 11-6

disabling, 11-7

enabling roles, 11-27

equivalent to SET_ROLE, 11-18

operating system roles, 11-29

protecting role use, 11-25

role passwords, 11-8

SET TRANSACTION statement, 7-8

ISOLATION LEVEL clause, 7-24

SERIALIZABLE, 7-24

SET_CONTEXT procedure, 12-16

SET_ROLE procedure, 11-18

SGA

See Also system global area

share locks (S)

LOCK TABLE statement, 7-13

share row exclusive locks (SRX)

LOCK TABLE statement, 7-14

side effects, 9-6, 9-55

signatures

PL/SQL library unit dependencies, 9-22

to manage remote dependencies, 9-24

SOAP, 1-20

SORT_AREA_SIZE parameter

index creation and, 5-2

sorting

with function-based indexes, 5-8

SQL statements

dynamic, 12-14

execution, 7-2

in trigger bodies, 15-16, 15-21

not allowed in triggers, 15-21

privileges required for, 11-23

restricting ad hoc use, 12-57

SQL*Loader

indexes and, 5-3

SQL*Module

applications, 9-4

SQL*Plus

anonymous blocks, 9-4

compile-time errors, 9-34

invoking stored procedures, 9-43

loading a procedure, 9-10

restricting ad hoc use, 12-57

SET SERVEROUTPUT ON command, 9-3

SHOW ERRORS command, 9-35

SQL/DS datatypes, 3-37

SQLStmt object, 1-36

SRX locks

LOCK Table statement, 7-14

standards

ANSI, 7-16

state

session, of package objects, 9-17

statement triggers

conditional code for statements, 15-18

row evaluation order, 15-22

specifying SQL statement, 15-5

timing, 15-6

trigger evaluation order, 15-22

UPDATE statements and, 15-6, 15-19

valid SQL statements, 15-21

storage allocation errors

resuming execution after, 7-40

stored functions, 9-5

creating, 9-9

stored procedures, 9-5

argument values, 9-46

creating, 9-9

distributed query creation, 9-40

encapsulating privileges, 11-7

exceptions, 9-36, 9-38

invoker's rights, 11-18

invoking, 9-43

names of, 9-5

overloading names of, 9-13

parameter

default values, 9-9

privileges, 9-46

remote, 9-47

remote objects and, 9-48

storing, 9-9

synonyms, 9-50

turning into a web page, 18-13

synonyms, 2-29

stored procedures and packages, 9-50

SYS schema, 12-16

SYS_CONTEXT function

access control, 12-22

dynamic SQL statements, 12-14

parallel query, 12-15

storing session variables, 12-16

syntax, 12-14

USERENV namespace, 12-4

SYS_XMLAGG function, 3-44

SYS_XMLGEN function, 3-44

SYSDATE function, 3-16

system events, 16-1

attributes, 16-2

client events, 16-9

resource manager events, 16-8

tracking, 15-50, 16-1

system global area

holds sequence number cache, 2-27

system privileges, 11-30, 11-31

system-specific Oracle documentation

PL/SQL wrapper, 9-22

table- or view-based security, 12-40

tables

constraining, 15-23

creating, 2-3

designing, 2-3

guidelines, 2-2, 2-3

in PL/SQL, 9-9

index-organized, 6-1 to 6-20

key-preserved, 2-16

mutating, 15-23

tablespaces

automatic segment-space management, 3-30

TCP/IP, 18-10

temporary segments

index creation and, 5-2

TERMINAL attribute, USERENV, 12-5

text search

using Oracle9i Text, 3-27

The DBMS_OBFUSCATION_TOOLKIT Package, 14-2

third generation language, 9-2

time and date data

representing, 3-14

time zones

functions for, 3-18

TIMESTAMP datatype, 3-14

TIMESTAMP WITH LOCAL TIME ZONE datatype, 3-14

TIMESTAMP WITH TIME ZONE datatype, 3-14

timestamps

PL/SQL library unit dependencies, 9-22

TM (transaction manager), 20-2

TO_CHAR function, 3-38

CC date format, 3-21

RR date format, 3-12

TO_CLOB function, 3-38

TO_DATE function, 3-15, 3-38

RR date format, 3-20

TO_NCHAR function, 3-38

TO_NCLOB function, 3-38

TO_NUMBER function, 3-38

transaction manager, 20-2

transactions

autonomous, 7-31 to 7-39

manual locking, 7-11

read-only, 7-8

serializable, 7-20

SET TRANSACTION statement, 7-8

triggers

about, 9-21

accessing column values, 15-16

AFTER, 15-6, 15-16, 15-34, 15-37

auditing with, 15-33, 15-34

BEFORE, 15-6, 15-16, 15-46, 15-47

body, 15-13, 15-18, 15-19, 15-21

check constraints, 15-45, 15-46

client events, 16-9

column list in UPDATE, 15-6, 15-19

compiled, 15-27

conditional predicates, 15-13, 15-18

CREATE TRIGGER ON, 11-24

creating, 15-2, 15-21, 15-26

data access restrictions, 15-46

debugging, 15-29

designing, 15-2

disabling, 15-29, 15-30

distributed query creation, 9-40

enabling, 15-29

error conditions and exceptions, 15-19

event, 12-23

events, 15-5

examples, 15-32 to 15-48

FOR EACH ROW clause, 15-11

generating derived column values, 15-47

illegal SQL statements, 15-21

INSTEAD OF triggers, 15-7

integrity constraints vs., 15-2, 15-38

listing information about, 15-30

login, 12-13, 12-16, 12-17

migration issues, 15-28

modifying, 15-29

multiple same type, 15-22

mutating tables and, 15-23

naming, 15-5

package variables and, 15-22

privileges, 15-26

to drop, 15-29

procedures and, 15-21

recompiling, 15-28

REFERENCING option, 15-18

referential integrity and, 15-39 to 15-43

remote dependencies and, 15-21

remote exceptions, 15-19

resource manager events, 16-8

restrictions, 15-12, 15-21

row, 15-11

row evaluation order, 15-22

scan order, 15-22

stored, 15-27

system triggers, 15-4

on DATABASE, 15-4

on SCHEMA, 15-4

trigger evaluation order, 15-22

use of LONG and LONG RAW datatypes, 15-21

username reported in, 15-26

WHEN clause, 15-12

TRUNC function, 3-16

TRUST keyword, 9-62

tuning

using LONGs, 3-31

UDDI, 1-20

unhandled exceptions, 9-39

UNIQUE key constraints

combining with NOT NULL constraint, 4-5

composite keys and nulls, 4-7

disabling, 4-22

enabling, 4-21

PRIMARY KEY constraint vs., 4-7

when to use, 4-7

UPDATE statement

column values and triggers, 15-16

data consistency, 7-11

triggers and, 15-6, 15-19

triggers for referential integrity, 15-41, 15-42

UPPER function, 5-9

URLs, 18-10

USER function, 4-5

user locks

requesting, 7-18

USER_ERRORS view

debugging stored procedures, 9-36

USER_SOURCE view, 9-36

user-defined errors, 9-36, 9-38

USERENV function, 12-4, 13-9, 14-7

USERENV namespace, 12-3, 12-5

usernames

as reported in a trigger, 15-26

schemas, 11-20

users

dropped roles, 11-29

enabling roles for, 11-17

enterprise, 11-21

PUBLIC group, 11-37

restricting application roles, 12-58

schema-independent, 11-21

UTL_HTTP package, 18-10

UTL_INADDR package, 18-10

UTL_SMTP package, 18-9

UTL_TCP package, 18-10

UTLLOCKT.SQL script, 7-20

validation function, 12-3

VARCHAR datatype, 3-10

VARCHAR2 datatype, 3-2, 3-10

column length, 3-11

when to use, 3-11

VBScript

translating to PSP, 18-15

versioning, in application context, 12-16

views

containing expressions, 15-8

creating, 2-9

creating with errors, 2-11

dropping, 2-14

FOR UPDATE clause and, 2-9

inherently modifiable, 15-7

invalid, 2-14

join views, 2-15

modifiable, 15-7

ORDER BY clause and, 2-9

privileges, 2-11

pseudocolumns, 15-8

replacing, 2-11

restrictions, 2-13

using, 2-12

when to use, 2-9

WITH CHECK OPTION, 2-9

virtual private database (VPD), 11-7, 11-12, 12-8, 12-59, 12-60

web pages

dynamic, 18-13

WebDB, 18-13

WHEN clause, 15-12

cannot contain PL/SQL expressions, 15-13

correlation names, 15-16

examples, 15-2, 15-11, 15-31, 15-40

EXCEPTION examples, 15-19, 15-40, 15-45, 15-46

WHERE clause, dynamic SQL, 12-59

WITH clause, 2-7

using to simplify complex queries, 2-7

WITH CONTEXT clause, 10-31

WNDS argument, 9-60

WNPS argument, 9-60

wrapper to hide PL/SQL code, 9-21

X locks

LOCK TABLE statement, 7-15

XA library, 20-1 to 20-36

xa_open string, 20-10

XML

as document type for PSP file, 18-16

searching with Oracle9i Text, 3-27

XML data

representing, 3-44

X/Open distributed transaction processing architecture, 20-2

year 2000, 3-20

Index

Symbols

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