Oracle9i Application Developer's Guide

%ROWTYPE attribute, 9-7

used in stored functions, 9-8

%TYPE attribute, 9-7

access

database, 11-30

objects

sequences, 2-25

schema objects, 11-32, 11-33

remote integrity constraints, 4-16

triggers, 15-3, 15-47

Active Data Object

translating to PSP, 18-14

Active Server Pages

translating to PSP, 18-14

ADD_CONTEXT procedure, 12-51

ADD_GROUPED_POLICY procedure, 12-51

ADD_POLICY procedure, 12-51

AFTER SUSPEND event

handling suspended storage allocation, 7-41

AFTER triggers

auditing and, 15-34, 15-37

correlation names and, 15-16

specifying, 15-7

ALL_ERRORS view

debugging stored procedures, 9-37

ALL_SOURCE view, 9-37

ALTER privilege, 11-24

ALTER SEQUENCE statement, 2-25

ALTER SESSION SET SCHEMA statement, 12-6

ALTER SESSION statement

SERIALIZABLE clause, 7-25

SET SCHEMA, 11-20

ALTER TABLE statement

defining integrity constraints, 4-19

DISABLE ALL TRIGGERS clause, 15-31

DISABLE CONSTRAINT clause, 4-23

DROP CONSTRAINT clause, 4-25

ENABLE ALL TRIGGERS clause, 15-30

ENABLE CONSTRAINT clause, 4-22

INITRANS parameter, 7-25

ALTER TRIGGER statement

DISABLE clause, 15-30

ENABLE clause, 15-30

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-36

AnyDataSet datatype, 3-36

AnyType datatype, 3-36

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-44

database users, 11-11

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

roles, 11-10, 11-16

security, 11-12, 12-58

unhandled exceptions in, 9-40

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-25

autonomous routine, 7-32

autonomous scope

versus autonomous transaction, 7-32

autonomous transactions, 7-32 to 7-40

AUTONOMOUS_TRANSACTION pragma, 7-32

BEFORE triggers

complex security authorizations, 15-47

correlation names and, 15-16

derived column values, 15-48

specifying, 15-7

BFILEs, 3-6

binary data

RAW and LONG RAW, 3-27

bind variables, 12-9

blank padding data

performance considerations, 3-9

BLOB (binary large object) datatype, 3-5

body

triggers, 15-14 to 15-20

Boolean expressions, 3-36

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

CACHE option

CREATE SEQUENCE command, 2-29

CREATE SEQUENCE statement, 2-29

caches

sequence, 2-28

sequence numbers, 2-24

call specifications, 10-4 to 10-52

callbacks, 10-47 to 10-48

canceling a cursor, 7-10

CASCADE CONSTRAINTS option, 11-34

CATPROC.SQL script, 15-4, 16-2

CC datetime format element, 3-16

century, 3-14

date format masks, 3-11

CGI variables, 18-13

CHAR datatype, 3-6

column length, 3-7

CHAR qualifier for column lengths, 3-7

character data

representing, 3-6

CHARSETFORM property, 10-26

CHARSETID property, 10-26

CHARTOROWID function, 3-33

CHECK constraint

triggers and, 15-39, 15-46

check constraints

how to use, 4-16 to 4-22

client events, 16-9

CLIENT_INFO attribute, USERENV, 12-5

CLOB datatype, 3-6

columns

accessing in triggers, 15-16

default values, 4-5

generating derived values with triggers, 15-48

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

multiple foreign key constraints, 4-12

number of CHECK constraints limit, 4-17

specifying length in bytes or characters, 3-7

columns, privileges, 11-32, 11-34

COMMIT statement, 7-5

comparison operators

blank padding data, 3-8

comparing dates, 3-12

compile-time errors, 9-35

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

composite keys

restricting nulls in, 4-18

concurrency, 7-22

conditional predicates

trigger bodies, 15-14, 15-17

consistency

read-only transactions, 7-8

constraining tables, 15-22

constraints

composite UNIQUE keys, 4-7

restriction on stored functions, 9-50

context switches

reducing with bulk binds, 9-18

conversion functions, 3-33

TO_CHAR function, 3-16

TO_CHAR function, year and century considerations, 3-15

TO_DATE function, 3-15

converting data, 3-33

ANSI datatypes, 3-32

assignments, 3-33

expression evaluation, 3-35

SQL/DS and DB2 datatypes, 3-32

cookies, 18-13

correlation names, 15-13 to 15-17

NEW, 15-16

OLD, 15-16

REFERENCING option and, 15-17

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-32, 11-20

privileges required, 2-32

CREATE SEQUENCE statement

CACHE option, 2-24, 2-29

examples, 2-29

NOCACHE option, 2-29

CREATE SESSION statement, 11-20

CREATE TABLE statement, 2-3, 2-4

defining integrity constraints, 4-18

INITRANS parameter in, 7-25

CREATE TRIGGER statement, 15-3

REFERENCING option, 15-17

CREATE VIEW statement, 2-10

OR REPLACE option, 2-13

WITH CHECK OPTION, 2-10, 2-14

CREATE_POLICY_GROUP procedure, 12-51

creating

indexes, 5-7

integrity constraints, 4-2

multiple objects, 2-32

packages, 9-15

sequences, 2-29

synonyms, 2-30

tables, 2-3, 2-4

triggers, 15-3, 15-20

views, 2-10

CURRENT_SCHEMA attribute, USERENV, 12-6

CURRENT_USER attribute, USERENV, 12-5

CURRVAL pseudo-column, 2-26

restrictions, 2-27

cursors, 7-9

cancelling, 7-10

closing, 7-10

declaring and opening cursor variables, 9-32

maximum number of, 7-9

pointers to, 9-32

private SQL areas and, 7-9

shared, 12-9

data blocks

shown in ROWIDs, 3-29

data control in OO4O, 1-38

data conversion, 3-33

ANSI datatypes, 3-32

assignments, 3-33

expression evaluation, 3-35

SQL/DS and DB2 datatypes, 3-32

data dictionary

compile-time errors, 9-36

integrity constraints in, 4-28

procedure source code, 9-37

schema object views, 2-36

data encryption, 11-5

data object number

extended ROWID, 3-28, 3-29

database

application administrator, 11-13

applications and security, 11-10

event notification, 16-1, 17-5

global name in a distributed system, 2-33

security and schemas, 11-20

user and application user, 11-11

datafiles

shown in ROWIDs, 3-29

datatypes, 3-2

ANSI/ISO, 3-32

BFILE, 3-3

BLOB, 3-3

CHAR, 3-2, 3-6

choosing a character datatype, 3-7

CLOB, 3-2

column lengths for character types, 3-7

data conversion, 3-33

DATE, 3-2, 3-14

DB2, 3-32

INTERVAL DAY TO SECOND, 3-2

INTERVAL YEAR TO MONTH, 3-2

LONG, 3-2

LONG RAW, 3-3

MDSYS.SDO_GEOMETRY, 3-21

NCHAR, 3-2, 3-6

NCLOB, 3-2

NUMBER, 3-2

NVARCHAR2, 3-2, 3-6

RAW, 3-3

ROWID, 3-3, 3-28

SQL/DS, 3-32

summary of datatypes, 3-3

TIMESTAMP, 3-2

TIMESTAMP WITH LOCAL TIME ZONE, 3-3

TIMESTAMP WITH TIME ZONE, 3-3

VARCHAR, 3-6

VARCHAR2, 3-2, 3-6

date and time data

representing, 3-10

date arithmetic, 3-35

functions for, 3-12

DATE datatype, 3-10

centuries, 3-14

data conversion, 3-33

DB_DOMAIN attribute, USERENV, 12-6

DB2 datatypes, 3-32

DBA_ERRORS view

debugging stored procedures, 9-37

DBA_ROLE_PRIVS view, 11-14

DBA_SOURCE view, 9-37

DBMS_FLASHBACK package

using in applications, 7-45

DBMS_LOCK package, 7-20

DBMS_OBFUSCATION_TOOLKIT, 14-1, 14-6

DBMS_RESUMABLE package

handling suspended storage allocation, 7-41

DBMS_RLS package, 12-50

DBMS_SESSION package

SET_CONTEXT procedure, 12-16

SET_ROLE procedure, 11-17, 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-36

DBMS_XMLGEN package, 3-38

DBMS_XMLQUERY package, 3-38

DBMS_XMLSAVE package, 3-38

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-50

maximum savepoints, 7-6

parameters in stored functions, 9-52

role, 11-26

DELETE privilege, 11-24

DELETE statement

column values and triggers, 15-16

data consistency, 7-11

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

dependencies

among PL/SQL library objects, 9-22

in stored triggers, 15-28

schema objects

trigger management, 15-20

the timestamp model, 9-23

DESC function, 5-9

DETERMINISTIC keyword, 9-56

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-30

Distinguished Name, 11-21

distributed databases

referential integrity and, 4-15

remote stored procedures, 9-46, 9-47

triggers and, 15-20

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-51

DROP_GROUPED_POLICY procedure, 12-51

DROP_POLICY procedure, 12-51

dropping

indexes, 5-6

integrity constraints, 4-25

packages, 9-12

procedures, 9-11

roles, 11-29

sequences, 2-29

synonyms, 2-31

triggers, 15-29

views, 2-15

dynamic SQL, 12-44, 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-36

e-mail

sending from PL/SQL, 18-9

embedded SQL, 9-2

ENABLE_GROUPED_POLICY procedure, 12-51

ENABLE_POLICY procedure, 12-51

enabling

integrity constraints, 4-21

roles, 11-6

triggers, 15-30

encryption, 11-5

enterprise user management, 11-12

enterprise users, 11-21

errors

application errors raised by Oracle packages, 9-37

creating views with errors, 2-12

remote procedures, 9-40

user-defined, 9-37, 9-38

event attribute functions, 16-2

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

triggering, 15-53

event triggers, 12-23

exception handlers

in PL/SQL, 9-2

exceptions

anonymous blocks, 9-3

during trigger execution, 15-18

effects on applications, 9-40

remote procedures, 9-40

unhandled, 9-39

exclusive locks

LOCK TABLE statement, 7-16

EXECUTE privilege, 11-24

explicit locking

manual locking, 7-11

extended ROWID format, 3-28

external procedure, 10-3

DEBUG_EXTPROC package, 10-50

debugging, 10-50

maximum number of parameters, 10-52

restrictions, 10-52

specifying datatypes, 10-18

EXTERNAL_NAME attribute, USERENV, 12-6

EXTPROC process

running on different machines, 10-6

extproc process, 10-36

features, new, xxxi

fine grained access control, 11-4

fine-grained access control

application context, 11-9, 12-8

features, 12-41

introduction, 12-41

performance, 12-44

fine-grained auditing

introduction, 11-4

FIPS flagger

interactive SQL statements and, 7-2

FIXED_DATE initialization parameter, 3-12

flashback query

examples, 7-47

using, 7-43

FOR EACH ROW clause, 15-12

FORALL statement

using, 9-18

foreign key constraints

defining, 4-26, 4-27

enabling, 4-21, 4-28

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-11

full-text search

using Oracle9i Text, 3-22

functions

See Also PL/SQL

geographic coordinate data

representing, 3-21

GRANT OPTION, 11-33

GRANT statement

ADMIN OPTION, 11-30

effective time, 11-37

last DDL time, 11-35

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-33

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-32

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-25

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-16

integrity constraints

application uses, 4-2

defining, 4-18

disabling, 4-21, 4-22, 4-23

dropping, 4-25

enabling, 4-20

enabling when violations exist, 4-21

examples, 4-2

exceptions to, 4-24

listing definitions of, 4-28

naming, 4-20

performance considerations, 4-3

privileges required for creating, 4-19

triggers vs., 15-2, 15-39

violations, 4-21

when to disable, 4-21

when to use, 4-2

interactive block execution, 9-43

INTERVAL DAY TO SECOND datatype, 3-10

INTERVAL YEAR TO MONTH datatype, 3-10

invalid views, 2-15

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-25

SERIALIZABLE, 7-25

Java

calling methods through call specifications, 10-4

generating wrapper classes with JPublisher, 1-16

in the RDBMS, 1-9

loading into the database, 10-5

overview of JDBC, 1-10

overview of SQLJ, 1-14

vs PL/SQL, 1-41

Javascript

translating to PSP, 18-14

JavaServer Pages

translating to PSP, 18-14

JDBC

See Oracle JDBC, 1-10

join views, 2-16

DELETE statements, 2-19

key-preserved tables in, 2-17

mergeable, 2-10, 2-17

modifying, 2-18

UPDATE statements, 2-18

when modifiable, 2-16

JPublisher, 1-18

JScript

translating to PSP, 18-14

key-preserved tables

in join views, 2-17

in outer joins, 2-22

keys

foreign keys, 4-26

unique

composite, 4-7

large datatypes

representing with LOBs, 3-22

libraries, 1-40

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-36

use in triggers, 15-20

LOB support in OO4O, 1-36

LOCK TABLE command, 7-12

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-16

user locks, 7-20

UTLLOCKT.SQL script, 7-21

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

LONG columns

restrictions on, 3-25

where referenced from, 3-24

LONG datatype, 3-6

in triggers, 3-26

switching to LOB datatypes, 3-22

use in triggers, 15-20

LONG RAW datatype, 3-27

use in triggers, 15-20

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-21

memory

scalability, 9-63

middle tier systems, 12-4

migration

ROWID format, 3-30

modes

of parameters, 9-6

modifiable join view

definition of, 2-16

mutating tables, 15-22

name resolution, 2-33

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-6

NCLOB datatype, 3-6

NEW correlation name, 15-16

new features, xxxi

NEXTVAL pseudo-column, 2-26

restrictions, 2-27

NLS_DATE_FORMAT parameter, 3-11

NLSSORT order, and indexes, 5-9

NOCACHE option

CREATE SEQUENCE statement, 2-29

NOT NULL constraint

CHECK constraint and, 4-18

data integrity, 4-21

when to use, 4-3

NOWAIT option, 7-12

NUMBER datatype, 3-9

numeric data

representing, 3-9

NVARCHAR2 datatype, 3-2, 3-6

OAS, 18-13

object columns, indexes on, 5-9

object support in OO4O, 1-36

objects

GRANT OPTION, 11-33

granting privileges, 11-23, 11-32

privileges, 11-22

revoking privileges, 11-33

objects, schema

listing information, 2-36

name resolution, 2-33

renaming, 2-35

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-27

vs precompilers, 1-39

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-31

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-35

OraAQAgent object, 1-36

OraAQMsg object, 1-36

OraBFILE object, 1-37

OraBLOB object, 1-37

Oracle Advanced Security, 11-21

Oracle Application Server (OAS), 18-13

Oracle Call Interface

See OCI

Oracle Data Control (ODC), 1-38

Oracle errors, 9-3

Oracle Internet Directory, 13-5

Oracle JDBC

definition, 1-10

example, 1-12

OCI driver, 1-11

Oracle extensions, 1-12

server driver, 1-11

stored procedures, 1-10

thin driver, 1-11

Oracle Objects for OLE

automation server, 1-31

C++ Class Library, 1-38

data control, 1-38

LOB and object support, 1-36

object model, 1-31

overview, 1-30

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-37

OraDatabase object, 1-33

OraDynaset object, 1-33

OraField object, 1-34

OraMeta Data object, 1-34

OraParamArray object, 1-35

OraParameter object, 1-34

OraServer object, 1-32

OraSession object, 1-32

OraSQLStmt object, 1-35

OS_ROLES parameter, 11-29

OS_USER attribute, USERENV, 12-6

OUT parameter mode, 9-6

outer joins, 2-21

key-preserved tables in, 2-22

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-40

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-45

privileges required to create, 9-16

privileges required to create procedures in, 9-10

serially reusable packages, 9-63

session state and, 9-17

synonyms, 9-49

where documented, 9-17

parallel execution servers, 12-15

parallel query, and SYS_CONTEXT, 12-15

PARALLEL_ENABLE keyword, 9-56

parameters

default values, 9-9

with stored functions, 9-52

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-5

anonymous blocks, 9-2, 11-18

calling remote stored procedures, 9-47

cursor variables, 9-32

dependencies among library units, 9-22

dynamically modifying SQL statements, 12-59

exception handlers, 9-2

features, 1-5

functions

arguments, 9-52

overloading, 9-63

parameter default values, 9-52

purity level, 9-62

RESTRICT_REFERENCES pragma, 9-59

using, 9-49

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-13

RAISE statement, 9-38

sample code, 1-4

serially reusable packages, 9-63

server pages, 18-13 to 18-24

setting context, 12-13

tables, 9-9

of records, 9-9

trigger bodies, 15-14, 15-16

user-defined errors, 9-38

vs Java, 1-41

web toolkit, 18-13

wrapper to hide code, 9-21

PLSQL_COMPILER_FLAGS initialization parameter, 9-22

pragma, 7-32, 7-40

RESTRICT_REFERENCES pragma, 9-59

SERIALLY_REUSABLE pragma, 9-63, 9-64

precompilers, 9-44

applications, 9-4

calling stored procedures and packages, 9-44

vs OCI, 1-39

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-25

creating integrity constraints, 4-19

creating triggers, 15-26

dropping a view, 2-15

dropping sequences, 2-30

dropping triggers, 15-29

encapsulating in stored procedures, 11-7

granting, 11-30, 11-32

granting to PUBLIC, 11-36

index creation, 5-6

managing, 11-13, 11-22

manually acquiring locks, 7-16

middle tier, 13-7

object, 11-23

on selected columns, 11-34

recompiling triggers, 15-28

renaming objects, 2-35

replacing views, 2-13

revoking, 11-33

sequence creation, 2-25

SQL statements permitted, 11-23

stored procedure execution, 9-45

synonym creation, 2-30

triggers, 15-26

using a view, 2-15

using sequences, 2-29

view creation, 2-12

Pro*C/C++

overview of application development, 1-21 to ??

Pro*COBOL

overview of application development, 1-24 to ??

procedures

called by triggers, 15-20

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-36

publish-subscribe, 17-2 to 17-6

purity level, 9-54

queries

capturing as views, 2-10

dynamic, 8-4

errors in distributed queries, 9-40

reconstructing data at a point in time, 7-43

speeding up with temporary tables, 2-5

RAISE statement, 9-38

RAISE_APPLICATION_ERROR procedure, 9-37

remote procedures, 9-40

raising exceptions

triggers, 15-18

RAW datatype, 3-27

RAWTOHEX function, 3-33

RAWTONHEX function, 3-33

read-only transactions, 7-8

reauthenticating clients, 13-5, 13-8

REF column

indexes on, 5-9

REFERENCES privilege, 11-24, 11-34

REFERENCING option, 15-17

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-43

triggers and, 15-40 to 15-44

REFRESH_GROUPED_POLICY procedure, 12-51, 12-52

REFRESH_POLICY procedure, 12-51, 12-52

remote dependencies, 9-22

signatures, 9-24

specifying timestamps or signatures, 9-29

remote exception handling, 9-40, 15-18

REMOTE_DEPENDENCIES_MODE parameter, 9-29

RENAME statement, 2-34

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-59

using to control side effects, 9-59

restrictions

system triggers, 15-25

resumable storage allocation, 7-41

examples, 7-42

reusable packages, 9-63

REVOKE statement, 11-37

revoking

privileges on selected columns, 11-34

roles and privileges, 11-31

RM (resource manager), 20-2

RNDS argument, 9-59

RNPS argument, 9-59

ROLE_SYS_PRIVS view, 11-14

ROLE_TAB_PRIVS view, 11-14

roles

ADMIN OPTION, 11-31

advantages, 11-14

application, 11-16, 11-22, 12-58

central management, 11-24

creating, 11-24

default, 11-26

dropping, 11-29

enabling, 11-16

enabling and disabling, 11-6

GRANT and REVOKE statements, 11-29

granting, 11-30

granting to PUBLIC, 11-36

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-16, 11-22

WITH GRANT OPTION, 11-33

ROLLBACK statement, 7-6

rolling back transactions

to savepoints, 7-6

routines

autonomous, 7-32

external, 10-3

service, 10-37

row locking

manually locking, 7-18

row triggers

defining, 15-12

REFERENCING option, 15-17

timing, 15-7

UPDATE statements and, 15-6, 15-18

ROW_LOCKING parameter, 7-11

ROWID datatype, 3-28

extended ROWID format, 3-28

migration, 3-30

ROWIDTOCHAR function, 3-33

ROWIDTONCHAR function, 3-33

rows

format, 2-3

header, 2-3

shown in ROWIDs, 3-29

size, 2-3

violating integrity constraints, 4-21

ROWTYPE_MISMATCH exception, 9-35

RR date format, 3-15

RS locks

LOCK TABLE statement, 7-13

run-time error handling, 9-37

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-63

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

schemas

default, 12-6

unique, 11-20

scope, autonomous, 7-32

scripting, 18-13

search data

representing, 3-22

secure application, xxxiv, 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-41

policies

administering, 12-50

applied within database, 12-60

centrally managed, 12-59

example, 12-44

implementing, 12-8

multiple policies per table, 12-43

on tables or views, 12-41

technical issues, 11-3

policy for applications, 11-10, 12-58

roles, advantages, 11-14

table- or view-based, 12-41

threats and countermeasures, 11-2

SELECT privilege, 11-24

SELECT statement

read consistency, 7-11

SELECT ... FOR UPDATE, 7-18

SEQUENCE_CACHE_ENTRIES parameter, 2-28

sequences

accessing, 2-25

altering, 2-25

caching numbers, 2-24

caching sequence numbers, 2-28

creating, 2-24, 2-29

CURRVAL, 2-25, 2-27

dropping, 2-29

initialization parameters, 2-24

NEXTVAL, 2-26

privileges needed, 2-25 to 2-30

reducing serialization, 2-26

SERIALIZABLE option

for ISOLATION LEVEL, 7-25

SERIALIZABLE parameter, 7-11

serializable transactions, 7-22

serially reusable PL/SQL packages, 9-63

SERIALLY_REUSABLE pragma, 9-64

service routine, 10-37

examples, 10-37

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-17

operating system roles, 11-29

protecting role use, 11-25

role passwords, 11-8

SET TRANSACTION statement, 7-8

ISOLATION LEVEL clause, 7-25

SERIALIZABLE, 7-25

SET_CONTEXT procedure, 12-16

SET_ROLE procedure, 11-17

SGA

See Also system global area

share locks (S)

LOCK TABLE statement, 7-13

share row exclusive locks (SRX)

LOCK TABLE statement, 7-15

side effects, 9-6, 9-54

signatures

PL/SQL library unit dependencies, 9-22

to manage remote dependencies, 9-24

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-20

not allowed in triggers, 15-20

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-35

invoking stored procedures, 9-42

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-32

SQLStmt object, 1-35

SRX locks

LOCK Table statement, 7-15

standards

ANSI, 7-16

state

session, of package objects, 9-17

statement triggers

conditional code for statements, 15-17

row evaluation order, 15-21

specifying SQL statement, 15-5

timing, 15-7

trigger evaluation order, 15-21

UPDATE statements and, 15-6, 15-18

valid SQL statements, 15-20

storage allocation errors

resuming execution after, 7-41

stored functions, 9-5

creating, 9-9

stored procedures, 9-5

argument values, 9-45

creating, 9-9

distributed query creation, 9-40

encapsulating privileges, 11-7

exceptions, 9-37, 9-38

invoker's rights, 11-18

invoking, 9-42

names of, 9-5

overloading names of, 9-13

parameter

default values, 9-9

privileges, 9-45

remote, 9-46

remote objects and, 9-47

storing, 9-9

synonyms, 9-49

turning into a web page, 18-13

synonyms

how to use, 2-30 to 2-31

stored procedures and packages, 9-49

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-38

SYS_XMLGEN function, 3-38

SYSDATE function, 3-12

system events, 16-1

attributes, 16-2

client events, 16-9

resource manager events, 16-8

tracking, 15-51, 16-1

system global area

holds sequence number cache, 2-28

system privileges, 11-30, 11-31

system-specific Oracle documentation, 15-4

PL/SQL wrapper, 9-22

table- or view-based security, 12-41

tables

constraining, 15-22

creating, 2-3, 2-4

designing, 2-3

guidelines, 2-3, 2-4

in PL/SQL, 9-9

index-organized, 6-1 to 6-20

key-preserved, 2-17

mutating, 15-22

tablespaces

automatic segment-space management, 3-25

TCP/IP, 18-10

temporary segments

index creation and, 5-2

TERMINAL attribute, USERENV, 12-5

text search

using Oracle9i Text, 3-22

The DBMS_OBFUSCATION_TOOLKIT Package, 14-2

third generation language, 9-2

time and date data

representing, 3-10

time zones

functions for, 3-13

TIMESTAMP datatype, 3-10

TIMESTAMP WITH LOCAL TIME ZONE datatype, 3-10

TIMESTAMP WITH TIME ZONE datatype, 3-10

timestamps

PL/SQL library unit dependencies, 9-22

TM (transaction manager), 20-2

TO_CHAR function, 3-33

CC date format, 3-16

RR date format, 3-8

TO_CLOB function, 3-33

TO_DATE function, 3-11, 3-33

RR date format, 3-15

TO_NCHAR function, 3-33

TO_NCLOB function, 3-33

TO_NUMBER function, 3-33

transaction manager, 20-2

transactions

autonomous, 7-32 to 7-40

manual locking, 7-11

read-only, 7-8

serializable, 7-22

SET TRANSACTION statement, 7-8

triggers

about, 9-21

accessing column values, 15-16

AFTER, 15-7, 15-16, 15-34, 15-37

auditing with, 15-33, 15-35

BEFORE, 15-7, 15-16, 15-47, 15-48

body, 15-14, 15-17, 15-18, 15-20

check constraints, 15-46, 15-47

client events, 16-9

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

compiled, 15-27

conditional predicates, 15-14, 15-17

CREATE TRIGGER ON, 11-24

creating, 15-3, 15-20, 15-26

data access restrictions, 15-47

debugging, 15-29

designing, 15-2

disabling, 15-30

distributed query creation, 9-40

enabling, 15-30

error conditions and exceptions, 15-18

event, 12-23

events, 15-5

examples, 15-33 to 15-49

FOR EACH ROW clause, 15-12

generating derived column values, 15-48

illegal SQL statements, 15-20

INSTEAD OF triggers, 15-7

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

listing information about, 15-31

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

migration issues, 15-29

modifying, 15-29

multiple same type, 15-21

mutating tables and, 15-22

naming, 15-5

package variables and, 15-21

prerequisites before creation, 15-4

privileges, 15-26

to drop, 15-29

procedures and, 15-20

recompiling, 15-28

REFERENCING option, 15-17

referential integrity and, 15-40 to 15-44

remote dependencies and, 15-20

remote exceptions, 15-18

resource manager events, 16-8

restrictions, 15-13, 15-20

row, 15-12

row evaluation order, 15-21

scan order, 15-21

stored, 15-27

system triggers, 15-4

on DATABASE, 15-4

on SCHEMA, 15-4

trigger evaluation order, 15-21

use of LONG, LONG RAW, and LOB datatypes, 15-20

username reported in, 15-26

WHEN clause, 15-13

TRUNC function, 3-12

TRUST keyword, 9-61

tuning

using LONGs, 3-26

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-18

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

UPPER function, 5-9

URLs, 18-10

USER function, 4-5

user locks

requesting, 7-20

USER_ERRORS view

debugging stored procedures, 9-36

USER_SOURCE view, 9-37

user-defined errors, 9-37, 9-38

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

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-16

enterprise, 11-21

PUBLIC group, 11-36

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-21

validation function, 12-3

VARCHAR datatype, 3-6

VARCHAR2 datatype, 3-2, 3-6

column length, 3-7

when to use, 3-7

VBScript

translating to PSP, 18-14

versioning, in application context, 12-16

views

containing expressions, 15-8

creating, 2-10

creating with errors, 2-12

dropping, 2-15

FOR UPDATE clause and, 2-10

inherently modifiable, 15-8

invalid, 2-15

join views, 2-16

modifiable, 15-8

ORDER BY clause and, 2-10

privileges, 2-12

pseudocolumns, 15-8

replacing, 2-12

restrictions, 2-14

using, 2-13

when to use, 2-10

WITH CHECK OPTION, 2-10

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-13

cannot contain PL/SQL expressions, 15-13

correlation names, 15-16

examples, 15-3, 15-12, 15-31, 15-40

EXCEPTION examples, 15-18, 15-40, 15-46, 15-47

WHERE clause, dynamic SQL, 12-59

WITH clause, 2-8

using to simplify complex queries, 2-8

WITH CONTEXT clause, 10-31

WNDS argument, 9-59

WNPS argument, 9-59

wrapper to hide PL/SQL code, 9-21

X locks

LOCK TABLE statement, 7-16

XA library, 20-1 to 20-35

xa_open string, 20-10

XML

as document type for PSP file, 18-16

searching with Oracle9i Text, 3-22

XML data

representing, 3-38

X/Open distributed transaction processing architecture, 20-2

year 2000, 3-14

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