integrity constraints and, 4-38

dropping, 4-39

index creation, 4-38

keys, 4-35

performance considerations, 4-36

privileges for creating, 4-38

collections

table items, 14-12

COLUMN_VALUE procedure, 14-6, 14-20

representing unnamed nested table, 7-15

COLUMN_VALUE_LONG procedure, 14-6, 14-21

columns

accessing in triggers, 13-8

default values, 9-4

generating derived values with triggers, 13-34

granting privileges for selected, 17-14

increasing length, 4-8

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

multiple FOREIGN KEY constraints,
9-10

number of CHECK constraints limit, 9-15

revoking privileges from, 17-16

COMMIT command, 3-5

COMMIT procedure, 10-59

COMMIT_COMMENT procedure, 10-59

COMMIT_FORCE procedure, 10-59

communication between sessions, 12-2

comparison methods, 7-10

comparison operators

blank padding data, 5-5

comparing dates, 5-8

COMPILE option

of ALTER PROCEDURE command, 15-5

compile time errors, 10-32

COMPILE_SCHEMA procedure, 10-60

complexity of information-handling, 11-4

compliance with industry standards, 2-7

composite keys

restricting nulls in, 9-15

concurrency, 3-26

conditional predicates

trigger bodies, 13-8 to 13-9

consistency

read-only transactions, 3-8

constraining tables, 13-13

constraints, 7-14

composite UNIQUE keys, 9-6

restriction on stored functions, 10-41

conversion functions, 5-17

TO_CHAR function, 5-8, 5-20

TO_DATE function, 5-8

TO_LABEL function, 5-20

Trusted Oracle Server, 5-20

converting data, 5-17

ANSI datatypes, 5-15

assignments, 5-17

expression evaluation, 5-19

SQL/DS and DB2 datatypes, 5-16

Trusted Oracle Server, 5-20

copy semantics for internal LOBs, 6-33

copying LOBs, 6-33

external, 6-33

internal LOBs, 6-33

correlation identifier, 11-9

correlation names, 13-8 to 13-9

NEW, 13-8

OLD, 13-8

REFERENCING option and, 13-9

when preceded by a colon, 13-9

COUNT attribute of collection types, 7-12, 8-7

CREATE CLUSTER command, 4-5, 4-37

hash clusters, 4-40

HASH IS option, 4-41

HASHKEYS option, 4-41

CREATE INDEX command, 4-5, 4-34

ON CLUSTER option, 4-38

CREATE PACKAGE BODY command, 10-12

CREATE PACKAGE command, 10-12

CREATE ROLE command, 17-9

CREATE SCHEMA command, 4-43

privileges required, 4-44

CREATE SEQUENCE command

CACHE option, 4-24, 4-28

examples, 4-28

NOCACHE option, 4-28

CREATE TABLE command, 4-2 to 4-3, 4-5

CLUSTER option, 4-37

defining integrity constraints, 9-15

INITRANS parameter in, 3-29

CREATE TRIGGER command, 13-2

CREATE TYPE statement, 7-8

CREATE VIEW command, 4-10

OR REPLACE option, 4-13

WITH CHECK OPTION, 4-10, 4-14

CREATE_PIPE procedure, 12-4

creating

clusters, 4-37

hash clusters, 4-40

indexes, 4-34

integrity constraints, 9-2

multiple objects, 4-43

packages, 10-12

sequences, 4-28

synonyms, 4-29

tables, 4-2 to 4-3

triggers, 13-2, 13-11

views, 4-10

creation of prioritized message queue table and queue, 11-18

creation of queue table and queue of object type, 11-18

creation of queue table and queue of RAW type, 11-18

creation of queue tables and queues, 11-18

CURRVAL pseudo-column, 4-25

restrictions, 4-26

cursor variables, 10-24

declaring and opening, 10-24

cursors, 3-9

cancelling, 3-10

closing, 3-10, 14-6

DBMS_SQL package, 14-4

maximum number of, 3-9

pointers to, 10-24

private SQL areas and, 3-9

daemon, Pro*C, 12-16

data blocks

factors affecting size of, 4-5

shown in ROWIDs, 5-13

data conversion, 5-17

ANSI datatypes, 5-15

assignments, 5-17

expression evalutation, 5-19

SQL/DS and DB2 datatypes, 5-16

Trusted Oracle labels, 5-20

data dictionary

compile time errors, 10-33

information about procedures and packages, 10-72

integrity constraints in, 9-27

procedure source code, 10-33

schema object views, 4-46

data object number

extended ROWID, 5-12 to 5-13

database

administrator

application administrator vs., 17-2

designing, 2-2

global name in a distributed system, 4-44

normalizing, 2-3

security

applications and, 17-2

schemas and, 17-7

triggers in applications, 2-5

database links

Trusted Database List, 10-62

datafiles shown in ROWIDs, 5-13

datatypes, 5-2

date arithmetic, 5-19

DATE datatype, 5-7

centuries, 5-8

data conversion, 5-17

DBA_ERRORS view

debugging stored procedures, 10-33

DBA_QUEUE_TABLES, 11-63

DBA_QUEUES, 11-64

DBA_ROLE_PRIVS view, 17-3

DBA_SOURCE view, 10-33

DBMS_ALERT package, 10-61

about, 16-2

creating, 16-3

DBMS_APPLICATION_INFO package,
10-62

DBMS_AQ package, 10-62

DBMS_AQ.DEQUEUE, 11-46

DBMS_AQ.ENQUEUE, 11-44

DBMS_AQADM package, 10-62

DBMS_AQADM.ADD_SUBSCRIBER, 11-58

DBMS_AQADM.ALTER_QUEUE, 11-54

DBMS_AQADM.CREATE_QUEUE, 11-51

DBMS_AQADM.CREATE_QUEUE_TABLE, 11-49

DBMS_AQADM.DROP_QUEUE, 11-53

DBMS_AQADM.DROP_QUEUE_TABLE, 11-53

DBMS_AQADM.QUEUE_SUBSCRIBER, 11-65

DBMS_AQADM.START_QUEUE, 11-55

DBMS_AQADM.START_TIME_MANAGE, 11-57

DBMS_AQADM.STOP_QUEUE, 11-56

DBMS_AQADM.STOP_TIME_MANAGER, 11-57

DBMS_DDL package, 10-57 to 10-58

DBMS_DEFER package, 10-63

DBMS_DEFER_QUERY package, 10-63

DBMS_DEFER_SYS package, 10-63

DBMS_DESCRIBE package, 10-61, 10-64

creating, 10-64

DBMS_DISTRIBUTED_TRUST_ADMIN package, 10-62

DBMS_HS package, 10-63

DBMS_HS_EXTPROC package, 10-63

DBMS_HS_PASSTHROUGH package, 10-63

DBMS_JOB package, 10-61

DBMS_LOB package, 6-50 to 6-51, 10-62

constants, 6-53

exceptions, 6-53

multi-threaded server (MTS), 6-17

routines, 6-51

datatypes, 6-51

security, 6-54

usage for BFILES, 6-54

usage, general, 6-54

DBMS_LOB.APPEND(), 6-58

DBMS_LOB.COMPARE(), 6-59

DBMS_LOB.COPY(), 6-62

DBMS_LOB.ERASE(), 6-64

DBMS_LOB.FILECLOSE(), 6-66

DBMS_LOB.FILECLOSEALL(), 6-67

DBMS_LOB.FILEEXISTS(), 6-68

DBMS_LOB.FILEGETNAME(), 6-69

DBMS_LOB.FILEISOPEN(), 6-70

DBMS_LOB.FILEOPEN(), 6-72

DBMS_LOB.GETLENGTH(), 6-73

DBMS_LOB.LOADFROMFILE(), 6-77

DBMS_LOB.READ(), 6-79

DBMS_LOB.SUBSTR(), 6-82

DBMS_LOB.TRIM(), 6-84

DBMS_LOB.WRITE(), 6-86

DBMS_LOCK package, 3-17, 10-61

creating, 3-19

security, 3-18

DBMS_OUTPUT package, 10-62, 12-19

creating, 12-20

examples, 12-23

GET_LINE procedure, 12-19

NEW_LINE procedure, 12-19

PUT procedure, 12-19

PUT_LINE procedure, 12-19

DBMS_PIPE package, 10-62, 12-2

creating, 12-2

DBMS_REFRESH package, 10-63

DBMS_REPCAT package, 10-63

DBMS_REPCAT_ADMIN package, 10-63

DBMS_REPCAT_AUTH package, 10-63

DBMS_ROWID package, 10-62

DBMS_SESSION package, 10-57 to 10-58

DBMS_SHARED_POOL package, 10-62

DBMS_SNAPSHOT package, 10-63

DBMS_SPACE package, 10-62

DBMS_SQL package, 10-62, 14-2

creating, 14-2

functions, 14-4, 14-7

DBMS_SYSTEM package, 10-62

DBMS_TRANSACTION package, 10-57,
10-59

DBMS_UTILITY package, 10-57, 10-60

DBMSALRT.SQL file, 16-3

DBMSDESC.SQL file, 10-64

DBMSLOCK.SQL file, 3-19

DBMSOTPT.SQL file, 12-20

DBMSPIPE.SQL file, 12-2

DBMSSQL.SQL file, 14-2

DDL statements

dynamic SQL, 14-2

package state and, 10-14

debugging stored procedures, 10-34

DECLARE

not used in stored procedures, 10-8

default

column values, 9-4, 10-41

maximum savepoints, 3-6

parameters in stored functions, 10-43

PCTFREE option, 4-3

PCTUSED option, 4-5

role, 17-10

deferred messaging, 11-7

DEFINE_ARRAY function, 14-16

DEFINE_ARRAY procedure, 14-6

DEFINE_COLUMN procedure, 14-5, 14-15

DEFINE_COLUMN_LONG procedure, 14-6, 14-18

DELETE command

column values and triggers, 13-8

data consistency, 3-10

triggers for referential integrity, 13-28 to 13-29

deleting external LOBs, 6-34

deleting internal LOBs, 6-33

deleting LOBs, 6-33

dependencies

among PL/SQL library objects, 10-15

in stored triggers, 13-17

listing information about, 15-7

schema objects

trigger management, 13-12

UTLDTREE.SQL, 15-8

the timestamp model, 10-16

dequeue of messages after preview, 11-26

DEREF operator, 7-12

dereferencing, 7-12

dereferencing, implicit, 7-12, 8-6

DESC_TAB datatype, 14-25

DESCRIBE_COLUMNS procedure, 14-24

DESCRIBE_PROCEDURE procedure, 10-65

Designer/2000, 1-3, 2-3

designing applications, 2-4

assessing needs, 2-2

Developer/2000, 1-3

dictionary, 10-72

See also data dictionary

directories

catalog views, 6-16

guidelines for usage, 6-16

ownership and privileges, 6-14

DIRECTORY name specification, 6-14

directory objects, 6-13

DISABLE procedure, 12-19, 12-21

disabling

integrity constraints, 9-19

triggers, 13-19

distributed databases

referential integrity and, 9-13

remote stored procedures, 10-38 to 10-39

triggers and, 13-12

distributed queries

handling errors, 10-31

distributed transactions

DISTRIBUTED_LOCK_TIMEOUT parameter, 3-12

DMBS_ROWID package, 10-74

DMBS_SQL package, 14-2, 14-7

locating errors, 14-26

See also dynamic SQL

DML_LOCKS parameter, 3-11

DROP CLUSTER command, 4-39, 4-41

DROP INDEX command, 4-35

privileges required, 4-35

DROP ROLE command, 17-13

DROP TABLE command, 4-9

DROP TRIGGER command, 13-19

dropping

clusters, 4-39

hash clusters, 4-41

indexes, 4-35

integrity constraints, 9-24

dropping AQ objects, 11-35

dynamic SQL

anonymous blocks and, 14-2

DBMS_SQL functions, using, 14-2

DBMS_SQL package, 14-2, 14-7

errors, locating, 14-26

examples, 14-28

execution flow in, 14-3

LAST_ERROR_POSITION function,
14-27

LAST_ROW_COUNT function, 14-27

LAST_ROW_ID function, 14-27

LAST_SQL_FUNCTION_CODE function, 14-27

security, 14-7

embedded SQL, 10-2

EMPTY_BLOB() function, 6-45

EMPTY_CLOB() function, 6-45

ENABLE procedure, 12-19 to 12-20

enabling

integrity constrains

at creation, 9-19

integrity constraints, 9-20

at creation, 9-18

reporting exceptions, 9-22

when violations exist, 9-19

roles, 17-11

triggers, 13-19 to 13-20

enqueue and dequeue of messages

by Correlation and Message Id Using
Pro*C/C++, 11-29

by priority, 11-24

of object type, 11-19

of object type using Pro*C/C++, 11-20

of RAW type, 11-22

to/from multiconsumer queues, 11-33

with time delay and expiration, 11-29

Enterprise Manager

DMBS_OUTPUT messages, 12-21

ENABLE procedure for output, 12-20

Entity-Relationship model, 2-2

errors

application errors raised by Oracle packages, 10-28

creating views with errors, 4-12

data dictionary views, 10-72

locating in dynamic SQL, 14-26

remote procedures, 10-31

returned by DBMS_ALERT package,
16-3

returned by DBMS_DESCRIBE package, 10-64

returned by DBMS_OUTPUT, 12-20

returned by DBMS_PIPES package, 12-4

user-defined, 10-28 to 10-29

events, signalling with alerters, 16-2

examples

LOB buffering, 6-40

purchase order, 7-2

read consistent locators, 6-22

repercussions of mixing SQL DML with DBMS_LOB, 6-25

updated LOB locators, 6-27

updating a LOB with a PL/SQL variable, 6-29

exception handlers

in PL/SQL, 10-2

exceptions

anonymous blocks, 10-3

during trigger execution, 13-10

effects on applications, 10-30

remote procedures, 10-31

ROWID_INVALID, 10-76

unhandled, 10-30

UTL_FILE package, 12-28

exclusive locks

LOCK TABLE command, 3-14

EXECUTE function, 14-6, 14-18

EXECUTE_AND_FETCH function, 14-6,
14-19

execution flow

in dynamic SQL, 14-3

explicit locking

manual locking, 3-10

extended ROWID format, 5-12

extents

allocating, 4-38

dropped tabled and, 4-9

external callout, 6-39

external LOBs (BFILEs), 6-5

FCLOSE procedure, 12-31

FCLOSE_ALL procedure, 12-31

FETCH_ROWS function, 14-6, 14-19

FFLUSH procedure, 12-36

file I/O in PL/SQL, 12-25

file ownership

with the UTL_FILE package, 12-27

FIPS flagger

interactive SQL statements and, 3-2

FIXED_DATE initialization parameter, 5-8

flushing the LOB's buffer, 6-35

FOPEN function, 12-29

FOR EACH ROW clause, 13-6

FOR UPDATE clause

LOBs, 6-20 to 6-21

FOREIGN KEY constraint

defining, 9-25 to 9-26

enabling, 9-20, 9-26

NOT NULL constraint and, 9-9

number of rows referencing parent table, 9-9

one-to-many relationship, 9-9

one-to-one relationship, 9-9

UNIQUE key constraint and, 9-9

updating tables, 9-10 to 9-11

foreign key, representing many-to-one entity relationship with, 7-5

format masks

TO_DATE function, 5-7

FORMAT_CALL_STACK function, 10-60

FORMAT_ERROR_STACK function, 10-60

FREE_UNUSED_MEMORY procedure,
10-58

functions, 10-41

See also PL/SQL

GET_LINE procedure, 12-19, 12-22, 12-32

GET_LINES procedure, 12-19, 12-22

GET_TIME function, 10-60

GRANT command, 17-13

ADMIN option, 17-13

object privileges, 17-14

system privileges, 17-13

when in effect, 17-19

WITH GRANT option, 17-15

granting privileges and roles, 17-13

hash clusters

Heterogeneous Services, 10-63

pass-through SQL, 10-63

security for distributed external procedures, 10-63

HEXTORAW function, 5-17

hiding PL/SQL code, 10-27

HTTP callouts, 10-63, 10-82

ICX, UTL_HTTP package, 10-82

implicit dereferencing, 7-12, 8-6

IN OUT parameter mode, 10-6

IN parameter mode, 10-6

incomplete object types, 7-8

indexes

creating, 4-34

specifying PCTFREE for, 4-5

dropping, 4-35

guidelines, 4-32

order of columns, 4-33

privileges, 4-34

SQL*Loader and, 4-32

temporary segments and, 4-31

when to create, 4-31

industry standards compliance, 2-7

initialization parameters

advanced queuing, 11-37

DISTRIBUTED_LOCK_TIMEOUT, 3-12

DML_LOCKS, 3-11

OPEN_CURSORS, 3-9

REMOTE_DEPENDENCIES_MODE, 10-21

ROW_LOCKING, 3-11, 3-16

SERIALIZABLE, 3-11

initialization part of package

avoiding problems with, 10-48

INITRANS parameter, 3-29

INSERT command

column values and triggers, 13-8

read consistency, 3-10

INSTEAD OF triggers, 8-5, 13-4

NEW keyword, 8-6

OLD keyword, 8-6

integrity constraints

interactive block execution, 10-35

interfaces

operational, for Advanced Queuing,
11-44

OCI for LOBs, 6-49

internal LOBs, 6-5

Internet data, 10-63, 10-82

invalid views, 4-15

IS_OPEN function, 12-30, 14-24

IS_PARALLEL_SERVER function, 10-60

IS_ROLE_ENABLED function, 10-58

ISOLATION LEVEL

changing, 3-29

SERIALIZABLE, 3-29

join view, 4-16

DELETE statements, 4-19

key-preserved tables in, 4-17

mergeable, 4-16

rule for modifying, 4-18

UPDATE statements, 4-18

when modifiable, 4-16

key-preserved tables

keys

unique, composite, 9-6

labels

data conversion, 5-20

MLSLABEL datatype, 5-15

LAST_ERROR_POSITION function, 14-27

LAST_ROW_COUNT function, 14-27

LAST_ROW_ID function, 14-27

LAST_SQL_FUNCTION_CODE function,
14-27

LBS

See LOB Buffering Subsystem, 6-35

leaf level scalar attributes, 7-15

library units

remote dependencies, 10-15

listing information about procedures and packages, 10-72

LOB Buffering System (LBS), 6-35

LOB locators cannot span transactions, 6-31

LOBs, 6-1

accessing through a locator, 6-20

bind variables, 6-29

buffering

caveats, 6-35

pages can be aged out, 6-38

buffering operations, 6-37

buffering subsystem, 6-35

DBMS_LOB package, 6-51

external LOBs (BFILEs), 6-5

copying, 6-33

deleting, 6-34

flushing, 6-35

in the object cache, 6-34

ENABLE | DISABLE STORAGE IN ROW, 6-10

initializing, 6-11

locators, 6-19

locking before updating, 6-21

LOGGING / NOLOGGING, 6-9

PCTVERSION, 6-8

setting to empty, 6-12

tablespace and LOB index, 6-7

tablespace and storage characteristics, 6-7

LOB locators, 6-18, 6-21

manipulating with Oracle Call Interface, 6-49

object cache, 6-34

performance, best practices, 6-42

performing SELECT on, 6-20

piecewise operations, 6-5, 6-25

placing in tables, 6-6

read consistent locators, 6-21

restrictions, 6-88

setting to contain a locator, 6-19

setting to NULL, 6-12

typical uses, 6-4

updated LOB locators, 6-24

value, 6-18

varying-width character data, 6-6, 6-43

local procedures in a package body, 10-13

LOCAL_TRANSACTION_ID function,
10-59

locators, 6-18

accessing a LOB through, 6-20

cannot span transactions, 6-31

multiple, 6-22

read consistent, 6-21 to 6-22, 6-29, 6-31, 6-38 to 6-40, 6-42

read consistent locators, 6-21

selecting, 6-20

setting column / attribute to contain, 6-19

updated, 6-21, 6-24, 6-29, 6-31, 6-38

LOCK TABLE command, 3-11 to 3-12

locking

application design and, 2-6

indexed foreign keys and, 9-11

manual (explicit), 3-10

row locking mode, 3-16

serializable mode, 3-16

unindexed foreign keys and, 9-10

locks

distributed, 3-10

LOCK TABLE command, 3-11 to 3-12

monitoring, 3-25

privileges for manual acquirement, 3-14

user locks, 3-17

UTLLOCKT.SQL script, 3-25

LOGGING / NOLOGGING, 6-9

LONG datatype, 5-9

restrictions on, 5-9

use in triggers, 13-12

LONG RAW datatype, 5-9, 5-11

restrictions on, 5-9

use in triggers, 13-12

maintaining applications, 2-10

MAKE_REF operator, 8-4

manual locking, 3-10

LOCK TABLE command, 3-11

map methods, 7-9, 7-12

MAX_ENABLED_ROLES parameter

default roles and, 17-10

MAXTRANS option, 4-5

memory

scalability, 10-50

message grouping, 11-9

message properties, specification, 11-40

message recipients, definition, 11-16

messages

between sessions, 12-2

producers and consumers, 11-12

messages as business assets, 11-5

messages as events, 11-4

messages, definition, 11-11

messaging system

metrics, 11-6

requirements, 11-6

methods of object types, 7-11

methods, comparison, 7-10

methods, map, 7-9, 7-12

methods, order, 7-9, 7-13

migration

ROWID format, 5-14

MLSLABEL datatype, 5-15

modes of parameters, 10-6

modifiable join view, 4-16

MULTISET operator, 8-5

multi-threaded server (MTS)

BFILEs, 6-17

mutating tables, 13-13

name resolution, 4-44

NAME_RESOLVE procedure, 10-61

national language support, 2-6

NCLOBs, 6-6, 6-53

NCHAR datatype, 5-2, 5-4

NCLOB datatype, 6-6

nested tables, 7-15

nested tables vs VARRAYs, 7-8, 7-10

nested tables, querying, 7-10

nested tables, uniqueness in, 7-16

NESTED_TABLE_ID hidden column, 7-16

NEW correlation name, 13-8

NEW_LINE procedure, 12-19, 12-33

NEXT_ITEM_TYPE function, 12-9

NEXTVAL pseudo-column, 4-25

restrictions, 4-26

NLS_DATE_FORMAT parameter, 5-7

NOCACHE option

CREATE SEQUENCE statement, 4-28

normalization, 2-3

NOT NULL constraint

CHECK constraint and, 9-15

NVARCHAR2 datatype, 5-2, 5-4

object cache for LOBs, 6-34

Object Database Designer, 2-3

object tables, 7-13

object types, comparison methods for, 7-10

object types, incomplete, 7-8

object types, methods of, 7-11

object views, 8-2

object views, creating, 8-3

object views, updating, 8-6

object-relational approach, implementing with object tables, 7-7

object-relational database management system (ORDBMS), 7-2

Object Database Designer, 2-3

objects, schema

granting privileges, 17-14

listing information, 4-46

name resolution, 4-44

renaming, 4-46

revoking privileges, 17-16

when revoking object privileges, 17-18

OCI, 2-7

interface for LOBs, 6-49

See also Oracle Call Interface

OLD correlation name, 13-8

one-to-many relationship

with foreign keys, 9-9

one-to-one relationship

with foreign keys, 9-9

OPEN_CURSOR function, 14-4, 14-9

OPEN_CURSORS parameter, 3-9

operating system

roles and, 17-12

optimizer

using hints in applications, 2-5

OR REPLACE clause

for creating packages, 10-12

Oracle Advanced Queuing (Oracle AQ), 11-1

DBMS_AQADM package, 11-48

Oracle Call Interface, 10-2

applications, 10-4

cancelling cursors, 3-10

closing cursors, 3-10

functionality in, 2-7

Oracle errors, 10-3

Oracle Precompilers

calling stored procedures and packages, 10-36

Oracle Procedure Builder, 1-3

Oracle Web Server Cartridges, 10-82

Oracle-supplied packages, 10-57, 10-61

where documented, 1-5

ORDBMS, 7-2

Object Database Designer, 2-3

order methods, 7-9, 7-13

OUT parameter mode, 10-6

outer joins, 4-20

key-preserved tables in, 4-21

overloading

of packaged functions, 10-49

stored procedure names, 10-5

using RESTRICT_REFERENCES, 10-49

PACK_MESSAGE procedure, 12-6

package body, 10-10

package specification, 10-10

packages

parallel server

distributed locks, 3-10

sequence numbers and, 4-24

parameter

default values, 10-8

with stored functions, 10-43

file (INIT.ORA), 12-26 to 12-27

modes, 10-6

PARSE procedure, 14-5, 14-9

parsing large SQL statements, 14-10

parse tree, 13-17

pass-through SQL, 10-63

pcode

when generated for triggers, 13-17

PCTFREE storage parameter

altering, 4-8

block overhead and, 4-6

default, 4-3

guidelines for setting, 4-4, 4-6

indexes for, 4-5

non-clustered tables, 4-5

PCTUSED storage parameter

altering, 4-8

block overhead and, 4-6

default, 4-5

guidelines for setting, 4-5 to 4-6

non-clustered tables, 4-5

PCTVERSION, 6-8

performance

clusters, 4-36

index column order, 4-33

ROW_LOCKING parameter, 3-16

SERIALIZABLE option, 3-16

pipes, 12-2

communication between sessions, 12-2

domain of, 12-3

examples, 12-12

managing, 12-11

public or private, 12-2

PL/SQL, 7-9, 10-2

pragmas, 7-9, 7-11, 10-45

EXCEPTION_INIT pragma, 10-76

RESTRICT_REFERENCES pragma,
10-45, 10-47

SERIALLY_REUSABLE pragma, 10-50 to 10-51

precompilers, 10-36

applications, 10-4

PRIMARY KEY constraint

altering, 9-24

choosing a primary key, 9-5

disabling, 9-20

enabling, 9-20

multiple columns in, 9-6

UNIQUE key constraint vs., 9-6

private SQL areas, cursors and, 3-9

privileges

altering sequences, 4-24

altering tables, 4-9

cluster creation, 4-38

creating integrity constraints, 9-17

creating tables, 4-7

creating triggers, 13-16

disabling triggers, 13-20

dropping a view, 4-15

dropping sequences, 4-29

dropping tables, 4-10

dropping triggers, 13-19

enabling roles and, 17-10

enabling triggers, 13-20

granting, 17-13 to 17-14

index creation, 4-34

managing, 17-7, 17-13

manually acquiring locks, 3-14

on selected columns, 17-16

recompiling packages or procedures,
15-6

recompiling triggers, 13-18, 15-6

recompiling views, 15-5

renaming objects, 4-46

replacing views, 4-13

revoking, 17-13, 17-15 to 17-16

sequence creation, 4-24

stored procedure execution, 10-37

synonym creation, 4-29

triggers, 13-16

using a view, 4-15

using sequences, 4-28

view creation, 4-12

when revoking object privileges, 17-18

Pro*C daemon, 12-16

procedures

avoiding runtime compilation, 15-2

called by triggers, 13-12

data dictionary views, 10-72

listing compilation errors, 10-72

listing information about, 10-72

listing source code, 10-73

local, 10-13

size information, 10-73

supplied, 10-57

using in applications, 2-5

profiles

application design and, 2-7

program units in PL/SQL, 10-2

pseudocolumns

modifying views, 13-4

PUBLIC user group

granting and revoking privileges to, 17-18

procedures and, 17-19

purchase order example, 7-2

PURGE_MIXED procedure, 10-59

purity level, 10-44

PUT procedure, 12-19, 12-21, 12-33

maximum output size for, 12-34

PUT_LINE procedure, 12-19, 12-21, 12-34

maximum output size for, 12-34

PUTF procedure, 12-35

queries

errors in distributed queries, 10-31

queue options, specification, 11-42

queue subscribers, definition, 11-15

queue tables, definition, 11-11

queues, definition, 11-11

queuing, 11-1, 11-4

DBMS_AQADM package, 11-48

RAISE statement, 10-29

RAISE_APPLICATION_ERROR procedure, 10-28

remote procedures, 10-31

raising exceptions

triggers, 13-10

RAW datatype, 5-11

RAWTOHEX function, 5-17

read consistency

LOBs, 6-21

read consistent locators, 6-21 to 6-22, 6-29, 6-31, 6-38 to 6-40, 6-42

READ_ONLY procedure, 10-59

READ_WRITE procedure, 10-59

read-only transactions, 3-8

RECEIVE_MESSAGE function, 12-8

recompilation

avoiding runtime, 15-2

reference semantics for BFILEs, 6-13

distributed databases and, 9-13

referential integrity

one-to-many relationship, 9-9

one-to-one relationship, 9-9

privileges to create foreign keys, 9-25

self-referential constraints, 13-29

triggers and, 13-27 to 13-30

REFs, constructing from object identifiers, 8-5

REFs, dereferencing of, 7-12

REFs, implicit dereferencing of, 7-12, 8-6

REFs, scoped, 7-15

REGISTER procedure, 16-4

remote dependencies, 10-15, 15-4

signatures, 10-16

specifying timestamps or signatures, 10-21

remote exception handling, 10-31, 13-10

REMOTE_DEPENDENCIES_MODE parameter, 10-21

REMOVE procedure, 16-5

REMOVE_PIPE procedure, 12-10

RENAME command, 4-46

renaming objects, 4-46

repeatable reads, 3-8, 3-10

RESET_PACKAGE procedure, 10-58

RESTRICT_REFERENCES pragma

syntax for, 10-45

using to control side effects, 10-45, 10-47

variant, 10-47

retention and message history, 11-8

reusable packages, 10-50

REVOKE command, 17-15

when in effect, 17-19

revoking privileges and roles

on selected columns, 17-16

REVOKE command, 17-15

revoking roles and privelieges (AQ), 11-36

RNDS argument, 10-45

RNPS argument, 10-46

ROLE_SYS_PRIVS view, 17-3

ROLE_TAB_PRIVS view, 17-3

roles

ROLLBACK command, 3-6

ROLLBACK procedure, 10-59

ROLLBACK_FORCE procedure, 10-59

ROLLBACK_SAVEPOINT procedure, 10-59

rolling back transactions

to savepoints, 3-6

roundtrips to the server, avoiding, 6-35, 6-39

row locking

manually locking, 3-14

row triggers

defining, 13-6

UPDATE statements and, 13-6, 13-10

timing, 13-3

ROW_LOCKING parameter, 3-11, 3-16

ROWID datatype, 5-12

DBMS_ROWID package, 10-74

extended format, 10-80

extended ROWID format, 5-12

migration, 5-14

ROWIDTOCHAR function, 5-17

ROWLABEL column, 5-15

rows

chaining across blocks, 4-5

format, 4-2

header, 4-2

shown in ROWIDs, 5-13

size, 4-2

violating integrity constraints, 9-19

ROWTYPE_MISMATCH exception, 10-27

RR date format, 5-8

RS locks

RX locks

S locks

SAVEPOINT procedure, 10-59

sample programs

daemon.pc, 12-16

daemon.sql, 12-14

SAVEPOINT command, 3-6

savepoints

maximum number of, 3-6

rolling back to, 3-6

scalability

serially reusable packages, 10-50

schemas, 17-7

scoped REFs, 7-15

security

dynamic SQL, 14-7

enforcing in applications, 2-8

in PL/SQL file I/O, 12-27

policy for applications, 17-2, 17-5

roles, advantages, 17-3

when using the UTL_FILE package, 12-26

SELECT command

FOR UPDATE, 6-20

read consistency, 3-10, 6-21

SELECT ... FOR UPDATE, 3-14

SELF keyword, 7-12

semantics

copy-based for internal LOBs, 6-33

reference based for BFILEs, 6-13

SEND_MESSAGE function, 12-6

sequence of messages

retrieving, 11-5

SEQUENCE_CACHE_ENTRIES parameter, 4-27

sequences

accessing, 4-24

altering, 4-24

caching numbers, 4-24

caching sequence numbers, 4-27

creating, 4-23, 4-28

CURRVAL, 4-24, 4-26

dropping, 4-29

initialization parameters, 4-23

NEXTVAL, 4-25

parallel server, 4-24

privileges for creating, 4-24

privileges to alter, 4-24

privileges to drop, 4-29

privileges to use, 4-28

reducing serialization, 4-25

using in applications, 2-7

SERIALIZABLE option, 3-16

for ISOLATION LEVEL, 3-29

SERIALIZABLE parameter, 3-11

serializable transactions, 3-26

serially reusable PL/SQL packages, 10-50

SERIALLY_REUSABLE pragma, 10-51

SESSION_MAX_OPEN_FILES parameter, 6-17

sessions

communicating between, 12-2

package state and, 10-14

SET ROLE command, 17-4, 17-11

when using operating system roles, 17-12

SET TRANSACTION command, 3-8

ISOLATION LEVEL clause, 3-29

SERIALIZABLE, 3-16, 3-29

SET_CLOSE_CACHED_OPEN_CURSORS procedure, 10-58

SET_DEFAULTS procedure, 16-7

SET_NLS procedure, 10-58

SET_ROLE procedure, 10-58

SET_SQL_TRACE procedure, 10-58

setting internal LOBs to empty, 6-12

setting LOBs to NULL, 6-12

SGA, 4-27

See also system global area

share locks (S)

using in applications, 2-6

share row exclusive locks (SRX)

LOCK TABLE command, 3-13

shared SQL areas

side effects, 10-6, 10-44

SIGNAL procedure, 16-5

signatures

PL/SQL library unit dependencies,
10-15

to manage remote dependencies, 10-16

SORT_AREA_SIZE parameter

index creation and, 4-31

SQL DDL

BFILE security, 6-15

SQL DML

BFILE security, 6-15

SQL statements

access in PL/SQL, 10-57

application design and, 2-8

dynamic SQL, 14-2

execution, 3-2

in trigger bodies, 13-8, 13-11

larger than 32 KB, 14-10

not allowed in triggers, 13-11

pass-through SQL, 10-63

privileges required for, 17-8

when constraint checking occurs, 9-15

SQL*Loader

indexes and, 4-32

SQL*Module

applications, 10-4

calling stored procedures from, 10-5

SQL*Plus

anonymous blocks, 10-4

compile time errors, 10-32

creating a sequence, 10-12

DMBS_OUTPUT messages, 12-21

ENABLE procedure for output, 12-20

invoking stored procedures, 10-34

loading a procedure, 10-9

SET SERVEROUTPUT ON command, 10-3

SHOW ERRORS command, 10-32

SRX locks

LOCK Table command, 3-13

standards

ANSI, 3-16

compliance, 2-7

state

session, of package objects, 10-14

statement triggers

conditional code for statements, 13-9

row evaluation order, 13-12

specifying SQL statement, 13-6

timing, 13-3

trigger evaluation order, 13-13

UPDATE statements and, 13-6, 13-10

valid SQL statements, 13-11

STEP_ID function, 10-59

storage

object tables, 7-15

storage parameters

PCTFREE, 4-8

PCTUSED, 4-8

stored functions, 10-4

creating, 10-8

stored procedures, 10-4

structs

arrays of in C, 2-7

structured payload, 11-8

subscription & recipient lists, 11-9

supplied procedures, 10-57

synchronous communication, 11-7

synonyms

creating, 4-29

stored procedures and packages, 10-40

dropping, 4-30

privileges, 4-29 to 4-30

using, 4-29

SYSDATE function, 5-8

system global area

buffers DBMS_OUTPUT data, 12-21

buffers pipes information, 12-2

holds sequence number cache, 4-27

system-specific Oracle documentation, 3-18 to 3-19, 10-64, 12-3, 12-20, 13-3, 14-2, 15-8, 16-3

PL/SQL wrapper, 10-27

UTLDTREE.SQL script, 15-2

tables

tables, nested, 7-15

tables, object, See object tables, 7-13

temporary segments

index creation and, 4-31

third generation language, 10-2

thread safety

in OCI applications, 2-7

timestamps

library unit dependencies, 10-15

TO_CHAR function, 5-17

CC date format, 5-8

converting Trusted Oracle labels, 5-20

RR date format, 5-8

TO_DATE function, 5-7, 5-17

RR date format, 5-8

TO_LABEL function

converting Trusted Oracle labels, 5-20

TO_NUMBER function, 5-17

transactions

external LOBs do not participate, 6-5

internal LOBs participate fully, 6-5 to 6-6

LOB locators cannot span, 6-31

SET TRANSACTION command, 3-8

triggers

about, 10-4

accessing column values, 13-8

AFTER, 13-3, 13-9, 13-23 to 13-24

auditing with, 13-22 to 13-23

BEFORE, 13-3, 13-9, 13-33 to 13-34

body, 13-8 to 13-11

check constraints, 13-31, 13-33

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

compiled, 13-17

conditional predicates, 13-8 to 13-9

creating, 13-2, 13-11, 13-16

data access restrictions, 13-32

debugging, 13-18

designing, 13-2

disabling, 13-19

distributed query creation, 10-31

error conditions and exceptions, 13-10

enabling, 13-19 to 13-20

events, 13-6

examples, 13-22 to 13-23, 13-25, 13-27, 13-31, 13-33 to 13-34

FOR EACH ROW clause, 13-6

generating derived column values, 13-34

illegal SQL statements, 13-11

INSTEAD OF triggers, 13-4

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

listing information about, 13-20

migration issues, 13-18

modifying, 13-18

multiple same type, 13-13

mutating tables and, 13-13

naming, 13-3

package variables and, 13-12

prerequisites before creation, 13-3

privileges, 13-16

privileges to disable, 13-20

privileges to drop, 13-19

privileges to recompile, 15-6

procedures and, 13-12

recompiling, 13-18, 15-6