Index

Active Server Pages

ADD_POLICY procedure, 11-38

advantages

OCI, 1-7

AFTER triggers

auditing and, 13-35, 13-38

correlation names and, 13-16

specifying, 13-7

ALL_ERRORS view

debugging stored procedures, 9-43

ALL_SOURCE view, 9-43

allocation

extents, 5-17

ALTER CLUSTER command, 2-6

ALLOCATE EXTENT option, 5-17

ALTER INDEX command, 2-6

ALTER privilege, 11-22

ALTER SEQUENCE command, 2-30

ALTER SESSION command

SERIALIZABLE, 7-25

SET SCHEMA, 11-19

ALTER SESSION SET SCHEMA statement, 11-44

ALTER TABLE command, 2-6, 2-9

defining integrity constraints, 4-18

DISABLE ALL TRIGGERS option, 13-32

DISABLE integrity constraint option, 4-21

DROP integrity constraint option, 4-24

ENABLE ALL TRIGGERS option, 13-31

ENABLE integrity constraint option, 4-21

INITRANS parameter, 7-25

ALTER TRIGGER command

DISABLE option, 13-31

ENABLE option, 13-31

altering

storage parameters, 2-9

tables, 2-9

American National Standards Institute (ANSI)

ANSI-compatible locking, 7-17

anonymous PL/SQL blocks, 11-10

about, 9-2

compared to triggers, 9-28

ANSI SQL92

FIPS flagger, 7-2

application context

as secure data cache, 11-47

bind variables, 11-48

creating, 11-52

design principles, 11-45

examples, 11-53

fine-grained access control, 11-14, 11-18, 11-47

how to use, 11-49

introduction, 11-4, 11-40

parallel query, 11-51

performance, 11-56

returning predicate, 11-48

security features, 11-41

setting, 11-52

USERENV namespace, 11-42

using in policy, 11-52

versioning, 11-51

application security

considerations for use, 11-6

introduction, 11-4, 11-5

limitations, 11-15

specifying attributes, 11-41

through validation, 11-41

applications

administrator, 11-8

calling stored procedures and packages, 9-51

database users, 11-6

One Big Application User model, 11-6, 11-7

roles, 11-5, 11-9

security, 11-7, 11-12

unhandled exceptions in, 9-46

attributes, USERENV, 11-43

auditing

compromised by One Big Application User, 11-6

n-tier systems, 11-66, 11-71

triggers and, 13-34

authentication

compromised by One Big Application User, 11-6

middle tier, 11-4, 11-64, 11-65, 11-66, 11-67

n-tier systems, 11-69

AUTHENTICATION_DATA attribute, 11-44

AUTHENTICATION_TYPE attribute, 11-44

autonomous routine, 7-32

autonomous scope

versus autonomous transaction, 7-32

autonomous transactions, 7-32 to 7-40

AUTONOMOUS_TRANSACTION, 7-32

BEFORE triggers

complex security authorizations, 13-48

correlation names and, 13-16

derived column values, 13-49

specifying, 13-7

binary data

RAW and LONG RAW, 3-20

bind variables, 11-48

blank padding data

performance considerations, 3-6

body

triggers, 13-14 to 13-20

Boolean expressions, 3-27

bulk binds, 9-24

DML statements, 9-25

FOR loops, 9-27

SELECT statements, 9-26

usage, 9-25

BY REF phrase, 10-28

CACHE option

CREATE SEQUENCE command, 2-34

caches

sequence cache, 2-33

sequence numbers, 2-29

call specifications, 10-3 to 10-50

callbacks, 10-44 to 10-46

cancelling a cursor, 7-10

CASCADE CONSTRAINTS option, 11-32

CASCADE option

integrity constraints, 5-18

CATPROC.SQL script, 13-4, 14-2

CC date format, 3-12

century, 3-10

date format masks, 3-9

CGI variables, 12-2

CHAR datatype, 3-5

column length, 3-6

increasing column length, 2-9

CHARSETFORM property, 10-24

CHARSETID property, 10-24

CHARTOROWID function, 3-25

CHECK constraint

triggers and, 13-40, 13-47

check constraints

how to use, 4-15 to 4-20

client events, 14-7

CLIENT_INFO attribute, USERENV, 11-43

clusters, 5-14 to 5-19

allocating extents, 5-17

performance considerations, 5-15

columns

accessing in triggers, 13-15

default values, 4-4

generating derived values with triggers, 13-49

increasing length, 2-9

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

multiple foreign key constraints, 4-11

number of CHECK constraints limit, 4-16

columns, privileges, 11-30, 11-31

commands, SQL

when constraint checking occurs, 4-17

COMMIT command, 7-5

comparison operators

blank padding data, 3-6

comparing dates, 3-9

compile-time errors, 9-41

composite keys

restricting nulls in, 4-16

concurrency, 7-22

conditional predicates

trigger bodies, 13-14, 13-17

consistency

read-only transactions, 7-8

constraining tables, 13-22

constraints

composite UNIQUE keys, 4-7

restriction on stored functions, 9-57

conversion functions, 3-25

TO_CHAR function, 3-12

TO_CHAR function, year and century considerations, 3-11

TO_DATE function, 3-11

converting data, 3-25

ANSI datatypes, 3-24

assignments, 3-25

expression evaluation, 3-27

SQL/DS and DB2 datatypes, 3-24

cookies, 12-2

correlation names, 13-13 to 13-17

NEW, 13-16

OLD, 13-16

REFERENCING option and, 13-17

when preceded by a colon, 13-16

CREATE CLUSTER command, 2-6, 5-15

hash clusters, 5-19

HASH IS option, 5-20

HASHKEYS option, 5-20

CREATE CONTEXT statement, 11-52

CREATE INDEX command, 2-6, 5-5

ON CLUSTER option, 5-16

CREATE PACKAGE BODY command, 9-15

CREATE PACKAGE command, 9-15

CREATE ROLE statement, 11-23

CREATE SCHEMA command, 2-37

privileges required, 2-37

CREATE SCHEMA statement, 11-19

CREATE SEQUENCE command

CACHE option, 2-29, 2-34

examples, 2-34

NOCACHE option, 2-34

CREATE SESSION statement, 11-19

CREATE TABLE command, 2-3, 2-4, 2-6

CLUSTER option, 5-16

defining integrity constraints, 4-17

INITRANS parameter in, 7-25

CREATE TRIGGER command, 13-3

WITH CHECK OPTION, 2-15, 2-19

CREATE VIEW command, 2-15

OR REPLACE option, 2-18

creating

clusters, 5-15

hash clusters, 5-19

indexes, 5-5

integrity constraints, 4-2

multiple objects, 2-37

triggers, 13-3, 13-20

views, 2-15

cryptographic functions, 11-72

CURRENT_SCHEMA attribute, USERENV, 11-44

CURRENT_USER attribute, USERENV, 11-43

CURRVAL pseudo-column, 2-31

restrictions, 2-32

cursor variables, 9-38

declaring and opening, 9-38

cursor, shared, 11-48

cursors, 7-9

cancelling, 7-10

closing, 7-10

maximum number of, 7-9

pointers to, 9-38

private SQL areas and, 7-9

data blocks

factors affecting size of, 2-6

shown in ROWIDs, 3-22

data control in OO4O, 1-19

data conversion, 3-25

ANSI datatypes, 3-24

assignments, 3-25

expression evaluation, 3-27

SQL/DS and DB2 datatypes, 3-24

data dictionary

compile-time errors, 9-42

integrity constraints in, 4-27

procedure source code, 9-43

schema object views, 2-41

data encryption, 11-4, 11-72

Data Encryption Standard (DES), 11-72

data object number

extended ROWID, 3-21, 3-22

database

application administrator, 11-8

applications and security, 11-5

global name in a distributed system, 2-38

security and schemas, 11-19

user and application user, 11-6

database event notification, 14-1, 15-5

datafiles

shown in ROWIDs, 3-22

datatypes, 3-2

date arithmetic, 3-27

DATE datatype, 3-8

centuries, 3-10

data conversion, 3-25

DB_DOMAIN attribute, USERENV, 11-44

DBA_ERRORS view

debugging stored procedures, 9-43

DBA_ROLE_PRIVS view, 11-9

DBA_SOURCE view, 9-43

DBMS_LOCK package, 7-20

DBMS_OBFUSCATION_TOOLKIT package, 11-72

DBMS_RLS package, 11-38, 11-46

DBMS_SESSION package

SET_CONTEXT procedure, 11-52

SET_ROLE procedure, 11-10

DBMS_SQL package

advantages of, 8-17

bulk SQL, 8-17

client-side programs, 8-17

DESCRIBE, 8-17

differences with native dynamic SQL, 8-12

multiple row updates and deletes, 8-18

RETURNING clause, 8-18

See Also dynamic SQL

SET_ROLE procedure, 11-11

DDL statements

package state and, 9-17

DEBUG_EXTPROC package, 10-48

debugging

stored procedures, 9-47

triggers, 13-30

default

column values, 4-4, 9-57

maximum savepoints, 7-6

parameters in stored functions, 9-59

PCTFREE option, 2-4

PCTUSED option, 2-6

role, 11-24

DELETE command

column values and triggers, 13-16

data consistency, 7-11

triggers for referential integrity, 13-43, 13-44

DELETE privilege, 11-22

dependencies

among PL/SQL library objects, 9-28

in stored triggers, 13-28

schema objects

trigger management, 13-20

the timestamp model, 9-29

DESC function, 5-8

DETERMINISTIC keyword, 9-63

dictionary_obj_owner event attribute, 14-3

dictionary_obj_owner_list event attribute, 14-3

dictionary_obj_type event attribute, 14-3

disabling

integrity constraints, 4-20

roles, 11-16

triggers, 13-31

Distinguished Name, 11-20

distributed databases

referential integrity and, 4-14

remote stored procedures, 9-53, 9-54

triggers and, 13-20

distributed queries

handling errors, 9-46

distributed transaction processing architecture, 16-2

DML_LOCKS parameter, 7-11

DROP CLUSTER command, 5-18, 5-20

DROP INDEX command, 5-6

privileges required, 5-6

DROP ROLE statement, 11-27

DROP TABLE command, 2-10

DROP TRIGGER command, 13-30

DROP_POLICY procedure, 11-38

dropping

clusters, 5-17

hash clusters, 5-20

indexes, 5-5

integrity constraints, 4-24

dynamic SQL, 11-14, 11-37, 11-39

application development languages, 8-24

DML statements, 8-3

invoker-rights, 8-8

invoking PL/SQL blocks, 8-7

optimization, 8-6

queries, 8-4

scenario, 8-9

See Also DBMS_SQL package

See Also native dynamic SQL

usage, 8-3

dynamic web pages, 12-2

e-mail

sending from PL/SQL, 12-1

embedded SQL, 9-2

ENABLE_POLICY procedure, 11-38

enabling

integrity constraints, 4-20

roles, 11-16

triggers, 13-31

encryption, 11-4, 11-72, 11-73

enterprise users, 11-19, 11-20

errors

application errors raised by Oracle packages, 9-43

creating views with errors, 2-16

remote procedures, 9-46

user-defined, 9-43, 9-44

event attribute functions, 14-2

event publication, 13-54 to 13-56, 14-1

triggering, 13-54

event triggers, 11-59

exception handlers

in PL/SQL, 9-2

exceptions

anonymous blocks, 9-3

during trigger execution, 13-18

effects on applications, 9-46

remote procedures, 9-46

unhandled, 9-45

exclusive locks

LOCK TABLE command, 7-16

EXECUTE privilege, 11-22

explicit locking

manual locking, 7-11

extended ROWID format, 3-21

extents

allocating, 5-17

dropped tabled and, 2-10

external procedure, 10-2

DEBUG_EXTPROC package, 10-48

debugging, 10-47

maximum number of parameters, 10-50

restrictions, 10-50

specifying datatypes, 10-16

EXTERNAL_NAME attribute, USERENV, 11-44

extproc process, 10-33

fine-grained access control

application context, 11-14, 11-18, 11-45, 11-47

features, 11-36

introduction, 11-4, 11-35

performance, 11-37

FIPS flagger

interactive SQL statements and, 7-2

FIXED_DATE initialization parameter, 3-9

FOR EACH ROW clause, 13-12

foreign key constraints

defining, 4-25, 4-26

enabling, 4-20, 4-27

NOT NULL constraint and, 4-10

one-to-many relationship, 4-10

one-to-n relationships, 4-10

UNIQUE key constraint and, 4-10

format masks

TO_DATE function, 3-8

functions

See Also PL/SQL

GRANT command

ADMIN OPTION, 11-28

effective time, 11-34

last DDL time, 11-33

object privileges, 11-21, 11-30

system privileges, 11-28

GRANT OPTION, 11-30

grantee event attribute, 14-3

granting

roles, 11-28

system privileges, 11-28, 11-29

hash clusters

how to use, 5-19 to 5-20

HEXTORAW function, 3-25

hiding PL/SQL code, 9-28

hostname, 12-1

HTML

displaying within PSP files, 12-5

retrieving from PL/SQL, 12-2

HTP and HTF packages, 12-2

HTTP URLs, 12-2

image maps, 12-2

IN OUT parameter mode, 9-6

IN parameter mode, 9-6

INDEX privilege, 11-22

indexes

creating, 5-5

specifying PCTFREE for, 2-6

dropping, 5-5

function-based, 5-6

guidelines, 5-2

order of columns, 5-4

privileges, 5-5

SQL*Loader and, 5-2

temporary segments and, 5-2

when to create, 5-2

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

INDICATOR property, 10-23

initialization parameters

DML_LOCKS, 7-11

OPEN_CURSORS, 7-9

REMOTE_DEPENDENCIES_MODE, 9-35

ROW_LOCKING, 7-11

SERIALIZABLE, 7-11

INITRANS parameter, 7-25

INSERT command

column values and triggers, 13-16

read consistency, 7-11

INSERT privilege, 11-22

instance_num event attribute, 14-3

INSTEAD OF triggers, 13-7

on nested table view columns, 13-16

integrity constraints

interactive block execution, 9-50

invalid views, 2-20

invoker's rights stored procedures, 11-10

invoker-rights

dynamic SQL, 8-8

is_alter_column event attribute, 14-3

is_creating_nested_table event attribute, 14-4

is_drop_column event attribute, 14-4

is_servererror event attribute, 14-4

ISDBA attribute, USERENV, 11-43

ISOLATION LEVEL

changing, 7-25

SERIALIZABLE, 7-25

Java

calling methods through call specifications, 10-3

generating wrapper classes with JPublisher, 1-33

in the RDBMS, 1-29

loading into the database, 10-4

overview of JDBC, 1-26

overview of SQLJ, 1-30

vs PL/SQL, 1-39

Java Server Pages

Javascript

key-preserved tables in, 2-22

JDBC

See Oracle JDBC, 1-26

join views, 2-21

DELETE statements, 2-24

mergeable, 2-22

modifying, 2-23

UPDATE statements, 2-24

when modifiable, 2-21

JPublisher, 1-35

JScript

remote dependencies, 9-28

key-preserved tables

keys

unique

library units

Lightweight Directory Access Protocol (LDAP), 11-56

lightweight sessions, 11-69

loadjava utility, 1-36

loadpsp command, 12-11

LOB datatype

support in OO4O, 1-17

use in triggers, 13-20

LOB support in OO4O, 1-17

LOCK TABLE command, 7-12

locking

manual (explicit), 7-11

locks

distributed, 7-11

LOCK TABLE command, 7-12, 7-13

privileges for manual acquirement, 7-16

user locks, 7-20

UTLLOCKT.SQL script, 7-21

login triggers, 11-49, 11-52, 11-53

login_user event attribute, 14-4

LONG datatype, 3-18

restrictions on, 3-18

use in triggers, 13-20

LONG RAW datatype, 3-18, 3-20

restrictions on, 3-18

use in triggers, 13-20

LOWER function, 5-8

mail

sending from PL/SQL, 12-1

manual locking, 7-11

LOCK TABLE command, 7-12

MAX_ENABLED_ROLES parameter, 11-24, 11-27

MAXTRANS option, 2-6

memory

scalability, 9-70

middle tier systems, 11-42, 11-64, 11-65, 11-66

migration

ROWID format, 3-23

modes

of parameters, 9-6

modifiable join view

definition of, 2-21

mutating tables, 13-22

name resolution, 2-38

native dynamic SQL

advantages of, 8-13

differences with DBMS_SQL package, 8-12

fetching into records, 8-16

performance, 8-15

See Also dynamic SQL

user-defined types, 8-16

NCHAR datatype, 3-2, 3-5

NEW correlation name, 13-16

NEXTVAL pseudo-column, 2-31

restrictions, 2-32

NLS_DATE_FORMAT parameter, 3-8

NLSSORT order, and indexes, 5-8

NOCACHE option

CREATE SEQUENCE statement, 2-34

NOT NULL constraint

CHECK constraint and, 4-16

data integrity, 4-20

when to use, 4-3

NOWAIT option, 7-12

n-tier authentication, 11-64, 11-65, 11-66

NUMBER datatype, 3-7

NVARCHAR2 datatype, 3-2, 3-5

OAS, 12-2

object columns, indexes on, 5-7

object support in OO4O, 1-17

objects

GRANT OPTION, 11-30

granting privileges, 11-22, 11-30

privileges, 11-21

revoking privileges, 11-31

objects, schema

listing information, 2-41

name resolution, 2-38

renaming, 2-40

OCI, 9-2

advantages, 1-7

applications, 9-4

cancelling cursors, 7-10

closing cursors, 7-10

enabling roles, 11-17

overview, 1-7

parts of, 1-8

OCI vs precompilers, 1-37

OLD correlation name, 13-16

one-to-many relationship

with foreign keys, 4-10

one-to-one relationship

with foreign keys, 4-10

OO4O

See Oracle Objects for OLE, 1-12

open string for XA, 16-10

OPEN_CURSORS parameter, 7-9

operating system

roles, 11-27

OR REPLACE clause

for creating packages, 9-16

ora_dictionary_obj_owner event attribute, 14-3

ora_dictionary_obj_owner_list event attribute, 14-3

ora_dictionary_obj_type event attribute, 14-3

ora_grantee event attribute, 14-3

ora_instance_num event attribute, 14-3

ora_is_alter_column event attribute, 14-3

ora_is_creating_nested_table event attribute, 14-4

ora_is_drop_column event attribute, 14-4

ora_is_servererror event attribute, 14-4

ora_login_user event attribute, 14-4

ora_privileges event attribute, 14-4

ora_revokee event attribute, 14-4

ora_server_error event attribute, 14-4

ora_sysevent event attribute, 14-4

ora_with_grant_option event attribute, 14-5

ORA-21301 error, fixing, 16-15

OraAQ object, 1-16

OraAQAgent object, 1-17

OraAQMsg object, 1-17

OraBFILE object, 1-18

OraBLOB object, 1-18

Oracle Advanced Security, 11-19

Oracle Application Server (OAS), 12-2

Oracle Call Interface

See OCI

Oracle Data Control (ODC), 1-19

Oracle errors, 9-3

Oracle Internet Directory, 11-67

Oracle JDBC

definition, 1-26

example, 1-27

OCI driver, 1-26

Oracle extensions, 1-27

server driver, 1-27

stored procedures, 1-29

thin driver, 1-26

Oracle Objects for OLE

automation server, 1-12

C++ Class Library, 1-19

data control, 1-19

LOB and object support, 1-17

object model, 1-12

overview, 1-11

Oracle SQLJ

advantages over JDBC, 1-32

stored programs, 1-36

Oracle supplied packages, 9-17

OraCLOB object, 1-18

OraDatabase object, 1-14

OraDynaset object, 1-14

OraField object, 1-15

OraMeta Data object, 1-15

OraParamArray object, 1-16

OraParameter object, 1-15

OraServer object, 1-13

OraSession object, 1-13

OraSQLStmt object, 1-16

OS_ROLES parameter, 11-27

OS_USER attribute, USERENV, 11-44

OUT parameter mode, 9-6

outer joins, 2-26

key-preserved tables in, 2-27

overloading

of packaged functions, 9-70

stored procedure names, 9-13

using RESTRICT_REFERENCES, 9-70

OWA* packages, 12-2

package body, 9-12

package specification, 9-12

DBMS_OUTPUT

privileges for execution, 9-52

privileges required to create, 9-16

privileges required to create procedures in, 9-10

serially reusable packages, 9-70

session state and, 9-17

synonyms, 9-56

where documented, 9-17

parallel execution servers, 11-51

parallel query, and SYS_CONTEXT, 11-51

parallel server

distributed locks, 7-11

sequence numbers and, 2-30

PARALLEL_ENABLE keyword, 9-63

parameters

default values, 9-9

with stored functions, 9-59

modes, 9-6

parse tree, 13-28

Pascal Calling Standard, 10-8

passwords

role, 11-18, 11-25

pcode

when generated for triggers, 13-28

PCTFREE storage parameter, 2-4 to 2-9

PCTUSED storage parameter, 2-6 to 2-9

guidelines for setting, 2-6

performance

clusters, 5-15

index column order, 5-4

native dynamic SQL, 8-15

PL/SQL, 9-2

advantages, 1-3

anonymous blocks, 9-2, 11-10

calling remote stored procedures, 9-54

cursor variables, 9-38

dependencies among library units, 9-28

dynamically modifying SQL statements, 11-14

exception handlers, 9-2

features, 1-3

functions

arguments, 9-59

overloading, 9-70

parameter default values, 9-59

purity level, 9-69

RESTRICT_REFERENCES pragma, 9-66

using, 9-56

hiding source code, 9-28

invoking with dynamic SQL, 8-7

objects, 1-5

packages, 9-12

program units, 9-2

serially reusable packages, 9-70

replaced views and, 2-18

RAISE statement, 9-44

reparsing, 11-45, 11-46

sample code, 1-2

server pages, 12-2 to 12-12

setting context, 11-49

tables, 9-9

of records, 9-9

trigger bodies, 13-14, 13-15

user-defined errors, 9-44

vs Java, 1-39

web toolkit, 12-2

wrapper to hide code, 9-28

pragma, 7-32, 7-40

RESTRICT_REFERENCES pragma, 9-66

SERIALLY_REUSABLE pragma, 9-70, 9-71

precompilers, 9-51

applications, 9-4

calling stored procedures and packages, 9-51

precompilers vs OCI, 1-37

PRIMARY KEY constraint

choosing a primary key, 4-6

disabling, 4-20

enabling, 4-20

multiple columns in, 4-7

UNIQUE key constraint vs., 4-7

private SQL areas

cursors and, 7-9

privileges

altering sequences, 2-30

altering tables, 2-10

cluster creation, 5-16

creating integrity constraints, 4-18

creating tables, 2-8

creating triggers, 13-26

dropping a view, 2-20

dropping sequences, 2-35

dropping tables, 2-11

dropping triggers, 13-30

encapsulating in stored procedures, 11-17

granting, 11-28, 11-30

granting to PUBLIC, 11-34

index creation, 5-5

managing, 11-8, 11-21

manually acquiring locks, 7-16

middle tier, 11-70

n-tier systems, 11-66

object, 11-22

on selected columns, 11-31

recompiling triggers, 13-29

renaming objects, 2-40

replacing views, 2-18

revoking, 11-29, 11-31

sequence creation, 2-30

SQL statements permitted, 11-22

stored procedure execution, 9-52

synonym creation, 2-35

triggers, 13-26

using a view, 2-20

using sequences, 2-34

view creation, 2-17

privileges event attribute, 14-4

Pro*C/C++

overview of application development, 1-20 to 1-22

Pro*COBOL

overview of application development, 1-23 to 1-25

procedures

called by triggers, 13-20

external, 10-2

PRODUCT_USER_PROFILE table, 11-13, 11-14, 11-17

program units in PL/SQL, 9-2

property

CHARSETFORM, 10-24

CHARSETID, 10-24

INDICATOR, 10-23

PROXY_USER attribute, 11-42, 11-44

pseudocolumns

modifying views, 13-8

PSP

See PL/SQL server pages, 12-2 to ??

.psp files, 12-4

PUBLIC user group, 11-34

publish-subscribe, 15-2 to 15-6

purity level, 9-61

queries

capturing as views, 2-15

dynamic, 8-4

errors in distributed queries, 9-46

speeding up with temporary tables, 2-11

RAISE statement, 9-44

RAISE_APPLICATION_ERROR procedure, 9-43

remote procedures, 9-46

raising exceptions

triggers, 13-18

RAW datatype, 3-20

RAWTOHEX function, 3-25

read-only transactions, 7-8

reauthenticating clients, 11-67, 11-68, 11-71

REF column

indexes on, 5-7

REFERENCES privilege, 11-22, 11-32

distributed databases and, 4-14

referential integrity

one-to-many relationship, 4-10

one-to-one relationship, 4-10

privileges required to create foreign keys, 4-25

self-referential constraints, 13-44

triggers and, 13-41 to ??, 13-41, ?? to 13-45

REFRESH_POLICY procedure, 11-38, 11-46

remote dependencies, 9-28

signatures, 9-30

specifying timestamps or signatures, 9-35

remote exception handling, 9-46, 13-18

REMOTE_DEPENDENCIES_MODE parameter, 9-35

RENAME command, 2-39, 2-40

REPARSE statement, 11-46

reparsing, 11-37, 11-45, 11-46, 11-52

repeatable reads, 7-8, 7-11

resource manager, 16-2

resource manager events, 14-6

RESOURCE privilege, 11-19

RESTRICT_REFERENCES pragma

syntax for, 9-66

using to control side effects, 9-66

restrictions

system triggers, 13-25

reusable packages, 9-70

REVOKE command, 11-29, 11-34

revokee event attribute, 14-4

revoking

privileges on selected columns, 11-31

roles and privileges, 11-29

RM (resource manager), 16-2

RNDS argument, 9-66

RNPS argument, 9-66

ROLE_SYS_PRIVS view, 11-9

ROLE_TAB_PRIVS view, 11-9

roles

ROLLBACK command, 7-6

rolling back transactions

routines

row locking

manually locking, 7-18

row triggers

defining, 13-12

UPDATE statements and, 13-6, 13-18

timing, 13-7

ROW_LOCKING parameter, 7-11

ROWID datatype, 3-21

extended ROWID format, 3-21

migration, 3-23

ROWIDTOCHAR function, 3-25

rows

chaining across blocks, 2-5

format, 2-2

header, 2-2

shown in ROWIDs, 3-22

size, 2-2

violating integrity constraints, 4-20

ROWTYPE_MISMATCH exception, 9-41

RR date format, 3-11

RS locks

run-time error handling, 9-43

RX locks

S locks

serially reusable packages, 9-70

SAVEPOINT command, 7-6

savepoints

maximum number of, 7-6

rolling back to, 7-6

scalability

schema-independent users, 11-19, 11-20

schemas

default, 11-44

unique, 11-19

scope, autonomous, 7-32

scripting, 12-2

Secure Sockets Layer (SSL) protocol, 11-68

security

application context, 11-40

enforcement in application, 11-7

enforcement in database, 11-7

features of Oracle8i, 11-4

fine-grained access control, 11-35

policy for applications, 11-5, 11-12

roles, advantages, 11-8

table- or view-based, 11-35

threats and countermeasures, 11-2

security policies

administering, 11-38

applied within database, 11-15

centrally managed, 11-14

establishing, 11-1, 11-5

example, 11-39

implementing, 11-47

multiple policies per table, 11-37

on tables or views, 11-36

technical issues, 11-3

threats and countermeasures, 11-2

SELECT command

read consistency, 7-11

SELECT ... FOR UPDATE, 7-18

SELECT privilege, 11-22

SEQUENCE_CACHE_ENTRIES parameter, 2-33

sequences

accessing, 2-30

altering, 2-30

caching numbers, 2-29

caching sequence numbers, 2-33

creating, 2-29, 2-34

CURRVAL, 2-30, 2-32

dropping, 2-34

initialization parameters, 2-29

NEXTVAL, 2-31

parallel server, 2-30

privileges needed, 2-30 to 2-35

reducing serialization, 2-31

SERIALIZABLE option

for ISOLATION LEVEL, 7-25

SERIALIZABLE parameter, 7-11

serializable transactions, 7-22

serially reusable PL/SQL packages, 9-70

SERIALLY_REUSABLE pragma, 9-71

server_error event attribute, 14-4

service routine, 10-35

examples, 10-35

session primitives, 11-42

SESSION_USER attribute, USERENV, 11-43

sessions

package state and, 9-17

SET ROLE statement

ALL EXCEPT option, 11-26

ALL option, 11-26

associating privileges with role, 11-9

at startup, 11-16

disabling, 11-17

enabling roles, 11-25

equivalent to SET_ROLE, 11-10

operating system roles, 11-27

protecting role use, 11-23

role passwords, 11-18

SET TRANSACTION command, 7-8

ISOLATION LEVEL clause, 7-25

SERIALIZABLE, 7-25

SET_CONTEXT procedure, 11-52

SET_ROLE procedure, 11-10

SGA

See Also system global area

share locks (S)

PL/SQL library unit dependencies, 9-28

share row exclusive locks (SRX)

LOCK TABLE command, 7-15

side effects, 9-6, 9-61

signatures

to manage remote dependencies, 9-30

SORT_AREA_SIZE parameter

index creation and, 5-2

sorting

with function-based indexes, 5-6

SQL statements

dynamic, 11-50

execution, 7-2

in trigger bodies, 13-15, 13-20

not allowed in triggers, 13-20

privileges required for, 11-22

restricting ad hoc use, 11-12, 11-13

SQL*Loader

indexes and, 5-2

SQL*Module

applications, 9-4

SQL*Plus

anonymous blocks, 9-4

compile-time errors, 9-41

invoking stored procedures, 9-49

loading a procedure, 9-10

restricting ad hoc use, 11-12, 11-13

SET SERVEROUTPUT ON command, 9-3

SHOW ERRORS command, 9-41

SQLStmt object, 1-16

SRX locks

LOCK Table command, 7-15

standards

ANSI, 7-17

state

session, of package objects, 9-17

statement triggers

conditional code for statements, 13-17

row evaluation order, 13-21

specifying SQL statement, 13-5

timing, 13-7

trigger evaluation order, 13-21

UPDATE statements and, 13-6, 13-18

valid SQL statements, 13-20

storage parameters

PCTFREE, 2-9

PCTUSED, 2-9

stored functions, 9-5

creating, 9-9

stored procedures, 9-5

argument values, 9-52

creating, 9-9

distributed query creation, 9-46

encapsulating privileges, 11-17

exceptions, 9-43, 9-44

invoker's rights, 11-10

invoking, 9-49

names of, 9-5

overloading names of, 9-13

parameter

default values, 9-9

privileges, 9-52

remote, 9-53

remote objects and, 9-54

storing, 9-9

synonyms, 9-56

turning into a web page, 12-2

synonyms

how to use, 2-35 to ??, 2-36 to ??

stored procedures and packages, 9-56

SYS schema, 11-52

SYS_CONTEXT function

access control, 11-58

dynamic SQL statements, 11-50

parallel query, 11-51

storing session variables, 11-51

syntax, 11-50

USERENV namespace, 11-42

SYSDATE function, 3-9

sysevent event attribute, 14-4

system events, 14-1

attributes, 14-2

client events, 14-7

resource manager events, 14-6

tracking, 13-52, 14-1

system global area

holds sequence number cache, 2-33

system privileges, 11-28, 11-29

system-specific Oracle documentation, 13-4

PL/SQL wrapper, 9-28

table- or view-based security, 11-35

tables

TCP/IP, 12-1

temporary segments

index creation and, 5-2

TERMINAL attribute, USERENV, 11-43

third generation language, 9-2

three-tier systems, 11-64, 11-65, 11-66

timestamps

PL/SQL library unit dependencies, 9-28

TM (transaction manager), 16-2

TO_CHAR function, 3-25

CC date format, 3-12

RR date format, 3-6

TO_DATE function, 3-8, 3-25

RR date format, 3-11

TO_NUMBER function, 3-25

transaction manager, 16-2

transactions

autonomous, 7-32 to 7-40

manual locking, 7-11

read-only, 7-8

serializable, 7-22

SET TRANSACTION command, 7-8

triggers

about, 9-28

accessing column values, 13-15

AFTER, 13-7, 13-16, 13-35, 13-38

auditing with, 13-34, 13-36

BEFORE, 13-7, 13-16, 13-48, 13-49

body, 13-14, 13-17, 13-18, 13-20

check constraints, 13-47, 13-48

client events, 14-7

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

compiled, 13-28

conditional predicates, 13-14, 13-17

CREATE TRIGGER ON, 11-22

creating, 13-3, 13-20, 13-26

data access restrictions, 13-48

debugging, 13-30

designing, 13-2

disabling, 13-31

distributed query creation, 9-46

error conditions and exceptions, 13-18

enabling, 13-31

event, 11-59

events, 13-5

examples, 13-34 to 13-50

FOR EACH ROW clause, 13-12

generating derived column values, 13-49

illegal SQL statements, 13-20

INSTEAD OF triggers, 13-7

integrity constraints vs., 13-2, 13-40

listing information about, 13-32

login, 11-49, 11-52, 11-53

migration issues, 13-29

modifying, 13-30

multiple same type, 13-21

mutating tables and, 13-22

naming, 13-5

package variables and, 13-21

prerequisites before creation, 13-4

privileges, 13-26

to drop, 13-30

procedures and, 13-20

recompiling, 13-29

referential integrity and, 13-41 to 13-45

remote dependencies and, 13-20

remote exceptions, 13-18

resource manager events, 14-6

restrictions, 13-13, 13-20

row, 13-12

row evaluation order, 13-21

scan order, 13-21

stored, 13-28

system triggers, 13-4

on DATABASE, 13-4

on SCHEMA, 13-4

trigger evaluation order, 13-21

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

username reported in, 13-26

WHEN clause, 13-13

TRUNC function, 3-9

TRUNCATE TABLE command, 2-10

TRUST keyword, 9-68

tuning

using LONGs, 3-19

unhandled exceptions, 9-45

UNIQUE key constraints

combining with NOT NULL constraint, 4-4

composite keys and nulls, 4-7

disabling, 4-20

enabling, 4-20

PRIMARY KEY constraint vs., 4-7

when to use, 4-7

UPDATE command

column values and triggers, 13-16

data consistency, 7-11

triggers and, 13-6, 13-18

triggers for referential integrity, 13-43, 13-44

UPDATE_CHECK parameter, 11-38

UPPER function, 5-8

URLs, 12-2

USER function, 4-5

user locks

requesting, 7-20

USER_ERRORS view

debugging stored procedures, 9-42

USER_SOURCE view, 9-43

user-defined errors, 9-43, 9-44

USERENV function, 11-42

USERENV namespace, 11-42, 11-43

usernames

as reported in a trigger, 13-26

schemas, 11-19

users

dropped roles, 11-27

enabling roles for, 11-9

enterprise, 11-19

PUBLIC group, 11-34

restricting application roles, 11-12

schema-independent, 11-19

UTL_HTTP package, 12-2

UTL_INADDR package, 12-1

UTL_SMTP package, 12-1

UTL_TCP package, 12-1

UTLLOCKT.SQL script, 7-21

validation function, 11-41

VARCHAR datatype, 3-5

VARCHAR2 datatype, 3-2, 3-5

column length, 3-6

when to use, 3-5

VBScript

versioning, in application context, 11-51

views

containing expressions, 13-8

creating, 2-15

creating with errors, 2-16