Index

access

database

granting privileges, 17-14

revoking privileges, 17-16

objects

sequences, 4-26

schema objects

granting privileges, 17-14

remote integrity constraints, 9-13

revoking privileges, 17-16

triggers, 13-2, 13-35

ADMIN option, 17-14

Advanced Queuing, 11-1

administration topics, 11-97

administrative interface, 11-78

enumerated constants, 11-97

privileges and access control, 11-78

creation of queue tables and queues, 11-28

data structures

object name, 11-67

database objects, 11-97

DBMS_AQADM package, 11-78

deferred execution of messages, 11-5

error messages, 11-103

features, 11-8

correlation identifier, 11-10

exception handling, 11-12

integrated database level support, 11-9

integrated transactions, 11-9

message grouping, 11-10

modes of dequeuing, 11-11

multiple recipients, 11-11

navigation of messages in dequeuing, 11-11

optimization of waiting for messages, 11-11

optional transaction protection, 11-11

priority and ordering of messages in enqueuing, 11-10

retention and message history, 11-9

retries with delays, 11-11

SQL access, 11-8

structured payload, 11-9

subscription & recipient list, 11-10

time specification, 11-10

tracking and event journals, 11-9

message properties, 11-68

messaging system requirements, 11-7

operational interface, 11-73

search criteria and dequeue order for messages, 11-76

queue options

dequeue options, 11-72

enqueue options, 11-71

reference to demos, 11-109

revoking roles and privelieges, 11-64

securing messages, 11-5

sequence of messages, 11-6

typical applications, 11-3

windows of opportunity, 11-5

Advanced Queuing, basics, 11-15

Advanced Queuing, multiple-consumer dequeuing of one message, 11-18

ADVISE_COMMIT procedure, 10-65

ADVISE_NOTHING procedure, 10-65

ADVISE_ROLLBACK procedure, 10-65

AFTER triggers

auditing and, 13-24, 13-25

correlation names and, 13-9

specifying, 13-3

agents, definition, 11-13

alerters, 16-2

ALL_ERRORS view

debugging stored procedures, 10-37

ALL_SOURCE view, 10-37

allocation

extents, 4-40

ALTER CLUSTER command, 4-5

ALLOCATE EXTENT option, 4-40

ALTER FUNCTION command, 15-5

ALTER INDEX command, 4-5

ALTER PACKAGE command, 15-5

ALTER PROCEDURE command, 15-5

ALTER SEQUENCE command, 4-25

ALTER SESSION command

SERIALIZABLE, 3-16, 3-31

ALTER TABLE command, 4-5, 4-8

defining integrity constraints, 9-16

DISABLE ALL TRIGGERS option, 13-20

DISABLE integrity constraint option, 9-21

DROP integrity constraint option, 9-25

ENABLE ALL TRIGGERS option, 13-21

ENABLE integrity constraint option, 9-21

INITRANS parameter, 3-31

ALTER TRIGGER command, 15-6

DISABLE option, 13-20

ENABLE option, 13-21

ALTER VIEW command, 15-5

ALTER_COMPILE procedure, 10-63

altering

storage parameters, 4-8

tables, 4-7, 4-8

American National Standards Institute (ANSI)

ANSI-compatible locking, 3-16

ANALYZE_OBJECT procedure, 10-63

ANALYZE_PART_OBJECT procedure, 10-66

ANALYZE_SCHEMA procedure, 10-66

anonymous PL/SQL blocks

about, 10-2

compared to triggers, 10-4

dynamic SQL and, 14-2, 14-3

ANSI SQL92

FIPS flagger, 3-2

applications

calling stored procedures and packages, 10-39

designing, 2-2, 2-4

designing database, 2-2

maintaining, 2-9

roles, 17-3

security, 17-2, 17-5

tuning, 2-8

unhandled exceptions in, 10-33

arrays, 7-18

BIND_ARRAY procedure, 14-6, 14-11

bulk DML using DBMS_SQL, 14-13

DEFINE_ARRAY procedure, 14-18

See also VARRAYs

arrays of C structs, 2-7

attributes, 7-20

auditing

triggers and, 13-23

BEFORE triggers

complex security authorizations, 13-35

correlation names and, 13-9

derived column values, 13-36

specifying, 13-3

BEGIN_DISCRETE_TRANSACTION procedure, 10-65

BFILE datatype, 6-8

BFILENAME(), 6-16, 6-60, 6-62

BFILEs, 6-5

copying, 6-39

initializing, 6-16

maximum number of open, 6-20

multi-threaded server (MTS), 6-21

binary data

RAW and LONG RAW, 5-13

BIND_ARRAY procedure, 14-6, 14-11

BIND_VARIABLE procedure, 14-6, 14-11

blank padding data

performance considerations, 5-6

BLOB datatype, 6-6

body

triggers, 13-8, 13-10, 13-11, 13-12

Boolean expressions, 5-21

buffers

LOBs, 6-47

Business Process Management, 11-5

business rules, 1-3

CACHE / NOCACHE, 6-10

CACHE option

CREATE SEQUENCE command, 4-29

caches

object cache, 6-47

sequence cache, 4-28

sequence numbers, 4-25

cancelling a cursor, 3-10

cartridges, 10-70, 10-91

CASCADE option

integrity constraints, 4-41

CASE tools, 1-3

CAST operator, 8-6

CATPROC.SQL file, 3-19, 12-23, 13-3

CC date format, 5-9

century, 5-9

date format masks, 5-9

CHAR datatype, 5-2, 5-5

column length, 5-6

increasing column length, 4-7

when to use, 5-5

character sets

ANY_CS, 6-69

CHARARR datatype

in DBMS_OUTPUT, 12-27

CHARTOROWID function, 5-19

CHECK constraint

data integrity, 9-20

designing, 9-14

NOT NULL constraint and, 9-15

number of, 9-15

restricting nulls using, 9-15

restrictions on, 9-14

triggers and, 13-28, 13-34

when to use, 9-13

CHUNK, 6-11

client-side development tools, 1-3

CLOB datatype, 6-7

NCLOBs, 6-7, 6-69

CLOSE_CURSOR procedure, 14-7, 14-29

CLOSE_DATABASE_LINK procedure, 10-64

clusters

allocating extents, 4-40

choosing data, 4-37, 4-38

creating, 4-38

dropped tables and, 4-9

dropping, 4-40

index creation, 4-39

integrity constraints and, 4-39

keys, 4-37

performance considerations, 4-38

privileges for creating, 4-40

collections

table items, 14-13

COLUMN_VALUE procedure, 14-7, 14-22

COLUMN_VALUE, representing unnamed nested table by, 7-20

COLUMN_VALUE_LONG procedure, 14-7, 14-24

columns

accessing in triggers, 13-9

default values, 9-4

generating derived values with triggers, 13-36

granting privileges for selected, 17-14

increasing length, 4-7

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

multiple FOREIGN KEY constraints, 9-10

number of CHECK constraints limit, 9-15

revoking privileges from, 17-17

COMMIT command, 3-5

COMMIT procedure, 10-65

COMMIT_COMMENT procedure, 10-65

COMMIT_FORCE procedure, 10-65

communication

between sessions, 12-2

comparison methods, 7-13

comparison operators

blank padding data, 5-6

comparing dates, 5-9

COMPILE option

of ALTER PROCEDURE command, 15-5

compile time errors, 10-36

COMPILE_SCHEMA procedure, 10-66

compliance with industry standards, 2-7

composite keys

restricting nulls in, 9-15

concurrency, 3-28

conditional predicates

trigger bodies, 13-8, 13-10

consistency

read-only transactions, 3-8

constraining tables, 13-14

constraints, 7-17

composite UNIQUE keys, 9-6

restriction on stored functions, 10-45

conversion functions, 5-19

TO_CHAR function, 5-9, 5-22

TO_DATE function, 5-9

TO_LABEL function, 5-22

Trusted Oracle Server, 5-22

converting data, 5-19

ANSI datatypes, 5-18

assignments, 5-19

expression evaluation, 5-21

SQL/DS and DB2 datatypes, 5-18

Trusted Oracle Server, 5-22

copy semantics for internal LOBs, 6-39

copying LOBs, 6-39

external, 6-39

internal LOBs, 6-39

correlation identifier, 11-10

correlation names, 13-8, 13-9, 13-10

NEW, 13-9

OLD, 13-9

REFERENCING option and, 13-10

when preceded by a colon, 13-9

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

CREATE CLUSTER command, 4-5, 4-38

hash clusters, 4-42

HASH IS option, 4-43

HASHKEYS option, 4-43

CREATE INDEX command, 4-5, 4-36

ON CLUSTER option, 4-39

CREATE PACKAGE BODY command, 10-14

CREATE PACKAGE command, 10-14

CREATE ROLE command, 17-9

CREATE SCHEMA command, 4-45

privileges required, 4-46

CREATE SEQUENCE command

CACHE option, 4-25, 4-29

examples, 4-29

NOCACHE option, 4-29

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

CLUSTER option, 4-39

defining integrity constraints, 9-16

INITRANS parameter in, 3-31

CREATE TRIGGER command, 13-2

REFERENCING option, 13-10

CREATE TYPE statement, 7-9

CREATE VIEW command, 4-10

OR REPLACE option, 4-12

WITH CHECK OPTION, 4-10, 4-14

CREATE_PIPE procedure, 12-4

creating

clusters, 4-38

hash clusters, 4-42

indexes, 4-35

integrity constraints, 9-2

multiple objects, 4-45

triggers, 13-2, 13-12

views, 4-10

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

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

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

creation of queue tables and queues, 11-28

CURRVAL pseudo-column, 4-26

restrictions, 4-27

cursor variables, 10-26

declaring and opening, 10-27

cursors, 3-9

cancelling, 3-10

closing, 3-10, 14-7

DBMS_SQL package, 14-4

maximum number of, 3-9

pointers to, 10-26

private SQL areas and, 3-9

daemon, Pro*C, 12-19

data blocks

factors affecting size of, 4-5

shown in ROWIDs, 5-15

data conversion, 5-19

ANSI datatypes, 5-18

assignments, 5-19

expression evalutation, 5-21

SQL/DS and DB2 datatypes, 5-18

Trusted Oracle labels, 5-22

data dictionary

compile time errors, 10-37

dropped tables and, 4-9

information about procedures and packages, 10-79

integrity constraints in, 9-27

procedure source code, 10-37

schema object views, 4-48

data object number

extended ROWID, 5-14, 5-15

database

administrator

application administrator vs., 17-2

designing, 2-2

global name in a distributed system, 4-46

normalizing, 2-3

security

applications and, 17-2

schemas and, 17-7

triggers

using in applications, 2-5

database links

Trusted Database List, 10-69

datafiles

shown in ROWIDs, 5-15

datatypes, 5-2

date arithmetic, 5-21

DATE datatype, 5-8

centuries, 5-9

data conversion, 5-19

DBA_ERRORS view

debugging stored procedures, 10-37

DBA_QUEUE_TABLES, 11-99

DBA_QUEUES, 11-100

DBA_ROLE_PRIVS view, 17-3

DBA_SOURCE view, 10-37

DBMS_ALERT package, 10-68

about, 16-2

creating, 16-3

DBMS_APPLICATION_INFO package, 10-68

DBMS_AQ package, 10-69

DBMS_AQADM package, 10-69

DBMS_AQADM.ADD_SUBSCRIBER, 11-91

DBMS_AQADM.CREATE_QUEUE, 11-82

DBMS_AQADM.CREATE_QUEUE_TABL, 11-80

DBMS_AQADM.DROP_QUEUE, 11-85

DBMS_AQADM.DROP_QUEUE_TABLE, 11-84

DBMS_AQADM.QUEUE_SUBSCRIBER, 11-101

DBMS_AQADM.START_QUEUE, 11-88

DBMS_AQADM.STOP_QUEUE, 11-90

DBMS_AQ.DEQUEUE, 11-75

DBMS_AQ.ENQUEUE, 11-74

DBMS_DDL package, 10-62, 10-63

DBMS_DEFER package, 10-70

DBMS_DEFER_QUERY package, 10-70

DBMS_DEFER_SYS package, 10-70

DBMS_DESCRIBE package, 10-68, 10-71

creating, 10-71

DBMS_DISTRIBUTED_TRUST_ADMIN package, 10-69

DBMS_HS package, 10-69

DBMS_HS_EXTPROC package, 10-69

DBMS_HS_PASSTHROUGH package, 10-69

DBMS_JOB package, 10-68

DBMS_LOB package, 6-68, 10-69

constants, 6-70

exceptions, 6-70

multi-threaded server (MTS), 6-21

routines, 6-68

datatypes, 6-68

security, 6-71

usage for BFILES, 6-71

usage, general, 6-71

DBMS_LOB.APPEND(), 6-75

DBMS_LOB.COMPARE(), 6-76

DBMS_LOB.COPY(), 6-79

DBMS_LOB.ERASE(), 6-81

DBMS_LOB.FILECLOSE(), 6-83

DBMS_LOB.FILECLOSEALL(), 6-84

DBMS_LOB.FILEEXISTS(), 6-85

DBMS_LOB.FILEGETNAME(), 6-86

DBMS_LOB.FILEISOPEN(), 6-87

DBMS_LOB.FILEOPEN(), 6-89

DBMS_LOB.GETLENGTH(), 6-90

DBMS_LOB.LOADFROMFILE(), 6-94

DBMS_LOB.READ(), 6-97

DBMS_LOB.SUBSTR(), 6-100

DBMS_LOB.TRIM(), 6-102

DBMS_LOB.WRITE(), 6-104

DBMS_LOCK package, 3-18, 10-68

creating, 3-19

security, 3-19

SLEEP procedure, 3-26

DBMS_OUTPUT package, 10-68, 12-22

creating, 12-23

examples, 12-27

GET_LINE procedure, 12-23

NEW_LINE procedure, 12-23

PUT procedure, 12-23

PUT_LINE procedure, 12-23

DBMS_PIPE package, 10-68, 12-2

creating, 12-3

DBMS_REFRESH package, 10-70

DBMS_REPCAT package, 10-70

DBMS_REPCAT_ADMIN package, 10-70

DBMS_REPCAT_AUTH package, 10-70

DBMS_ROWID package, 10-69

DBMS_SESSION package, 10-62, 10-64

DBMS_SHARED_POOL package, 10-68

DBMS_SNAPSHOT package, 10-70

DBMS_SPACE package, 10-68

DBMS_SQL package, 10-68, 14-2

creating, 14-2

functions, 14-4, 14-8

DBMS_SYSTEM package, 10-68

DBMS_TRANSACTION package, 10-63, 10-65

DBMS_UTILITY package, 10-63, 10-66

DBMSALRT.SQL file, 16-3

DBMSDESC.SQL file, 10-71

DBMSLOCK.SQL file, 3-19

DBMSOTPT.SQL file, 12-23

DBMSPIPE.SQL file, 12-3

DBMSSQL.SQL file, 14-2

DDL statements

dynamic SQL, 14-2

package state and, 10-16

debugging

stored procedures, 10-37

triggers, 13-19

DECLARE

not used in stored procedures, 10-9

default

column values, 9-4, 10-45

maximum savepoints, 3-6

parameters in stored functions, 10-47

PCTFREE option, 4-3

PCTUSED option, 4-5

role, 17-11

deferred messaging, 11-7

DEFINE_ARRAY function, 14-18

DEFINE_ARRAY procedure, 14-6

DEFINE_COLUMN procedure, 14-6, 14-16

DEFINE_COLUMN_LONG procedure, 14-6, 14-20

DELETE command

column values and triggers, 13-9

data consistency, 3-10

triggers for referential integrity, 13-30, 13-31

deleting external LOBs, 6-40

deleting internal LOBs, 6-39

deleting LOBs, 6-39

dependencies

among PL/SQL library objects, 10-16

in stored triggers, 13-18

listing information about, 15-6

schema objects

trigger management, 13-13

UTLDTREE.SQL, 15-7

the timestamp model, 10-17

dequeue of messages after preview, 11-41

DEREF operator, 7-14

dereferencing, 7-15

dereferencing, implicit, 7-15, 8-7

DESC_TAB datatype, 14-28

DESCRIBE_COLUMNS procedure, 14-27

DESCRIBE_PROCEDURE procedure, 10-72

Designer/2000, 1-3, 2-3

designing applications, 2-4

assessing needs, 2-2

Developer/2000, 1-3

dictionary

See data dictionary

directories

catalog views, 6-19

guidelines for usage, 6-19

ownership and privileges, 6-18

DIRECTORY name specification, 6-17

directory objects, 6-15

DISABLE procedure, 12-23, 12-24

disabling

integrity constraints, 9-20

triggers, 13-20

distributed databases

referential integrity and, 9-13

remote stored procedures, 10-42

triggers and, 13-13

distributed queries

handling errors, 10-34

DMBS_ROWID package, 10-81

DMBS_SQL package

locating errors, 14-30

DML_LOCKS parameter, 3-11

DROP CLUSTER command, 4-41, 4-43

DROP INDEX command, 4-36

privileges required, 4-37

DROP ROLE command, 17-13

DROP TABLE command, 4-8

DROP TRIGGER command, 13-19

dropping

clusters, 4-40

hash clusters, 4-43

indexes, 4-36

integrity constraints, 9-25

tables, 4-8

dropping AQ objects, 11-63

dynamic SQL

anonymous blocks and, 14-3

DBMS_SQL functions, using, 14-3

DBMS_SQL package, 14-2, 14-8

errors, locating, 14-30

examples, 14-31

execution flow in, 14-4

LAST_ERROR_POSITION function, 14-30

LAST_ROW_COUNT function, 14-30

LAST_ROW_ID function, 14-30

LAST_SQL_FUNCTION_CODE function, 14-30

security, 14-7

embedded SQL, 10-2

EMPTY_BLOB() function, 6-60

EMPTY_CLOB() function, 6-60

ENABLE procedure, 12-23, 12-24

enabling

integrity constrains

at creation, 9-20

integrity constraints, 9-20

at creation, 9-18

reporting exceptions, 9-23

when violations exist, 9-19

roles, 17-11

triggers, 13-20

enqueue and dequeue of messages

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

by priority, 11-33

of object type, 11-30

of RAW type, 11-33

of RAW type using Pro*C/C++, 11-36, 11-38

to/from multiconsumer queues, 11-52, 11-55

with time delay and expiration, 11-45

Entity-Relationship model, 2-2

errors

application errors raised by Oracle packages, 10-31

creating views with errors, 4-11

data dictionary views, 10-79

locating in dynamic SQL, 14-30

remote procedures, 10-35

returned by DBMS_ALERT package, 16-3

returned by DBMS_DESCRIBE package, 10-71

returned by DBMS_OUTPUT, 12-23

returned by DBMS_PIPES package, 12-4

user-defined, 10-30, 10-32

events

signalling with alerters, 16-2

example, purchase order, 7-2

examples

LOB buffering, 6-55

read consistent locators, 6-26

repercussions of mixing SQL DML with DBMS_LOB, 6-29

updated LOB locators, 6-31

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

exception handlers

in PL/SQL, 10-2

exceptions

anonymous blocks, 10-3

during trigger execution, 13-11

effects on applications, 10-33

remote procedures, 10-35

ROWID_INVALID, 10-83

unhandled, 10-33

UTL_FILE package, 12-33

exclusive locks

LOCK TABLE command, 3-14

EXECUTE function, 14-6, 14-20

EXECUTE_AND_FETCH function, 14-6, 14-21

execution flow

in dynamic SQL, 14-4

explicit locking

manual locking, 3-10

extended ROWID format, 5-14

extents

allocating, 4-40

dropped tabled and, 4-9

external callout, 6-52

external LOBs (BFILEs), 6-5

FCLOSE procedure, 12-37

FCLOSE_ALL procedure, 12-38

features, 2-4

features of Advanced Queuing, 11-8

FETCH_ROWS function, 14-6, 14-21

FFLUSH procedure, 12-43

file I/O

in PL/SQL, 12-30

file ownership

with the UTL_FILE package, 12-32

FIPS flagger

interactive SQL statements and, 3-2

FIXED_DATE initialization parameter, 5-9

flushing the LOB's buffer, 6-48

FOPEN function, 12-34

FOR EACH ROW clause, 13-7

FOR UPDATE clause

LOBs, 6-24, 6-25

FOREIGN KEY constraint

defining, 9-25, 9-26

enabling, 9-20, 9-27

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

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

format masks

TO_DATE function, 5-8

FORMAT_CALL_STACK function, 10-67

FORMAT_ERROR_STACK function, 10-66, 10-67

FREE_UNUSED_MEMORY procedure, 10-64

functions

See PL/SQL

GET_LINE procedure, 12-23, 12-26, 12-38

GET_LINES procedure, 12-23, 12-26

GRANT command, 17-13

ADMIN option, 17-14

object privileges, 17-14

system privileges, 17-14

when in effect, 17-19

WITH GRANT option, 17-15

granting privileges and roles, 17-13

hash clusters

Heterogeneous Services, 10-69

pass-through SQL, 10-69

security for distributed external procedures, 10-69

HEXTORAW function, 5-19

hiding PL/SQL code, 10-30

HTTP callouts, 10-70, 10-91

ICX

UTL_HTTP package, 10-91

implicit dereferencing, 7-15, 8-7

IN OUT parameter mode, 10-6

IN parameter mode, 10-6

incomplete object types, 7-9

indexes

creating, 4-35

dropped tables and, 4-9

dropping, 4-36

guidelines, 4-33

order of columns, 4-34

privileges, 4-36

specifying PCTFREE for, 4-5

SQL*Loader and, 4-33

temporary segments and, 4-32

when to create, 4-32

industry standards compliance, 2-7

initialization parameters

DML_LOCKS, 3-11

OPEN_CURSORS, 3-9

REMOTE_DEPENDENCIES_MODE, 10-24

ROW_LOCKING, 3-11, 3-16

SERIALIZABLE, 3-11

initialization part of package

avoiding problems with, 10-52

INIT.ORA parameter, 11-65

INITRANS parameter, 3-31

INSERT command

column values and triggers, 13-9

read consistency, 3-10

INSTEAD OF triggers, 8-6, 13-4

integrity constraints

interactive block execution, 10-38

interface

operational, for Advanced Queuing, 11-73

internal LOBs, 6-5

Internet data, 10-70, 10-91

invalid views, 4-14

IS_OPEN function, 12-36, 14-27

IS_ROLE_ENABLED function, 10-64

ISOLATION LEVEL

changing, 3-31

SERIALIZABLE, 3-31

join view, 4-15

DELETE statements, 4-19

key-preserved tables in, 4-18

mergeable, 4-16

modifying

rule for, 4-19

UPDATE statements, 4-19

when modifiable, 4-16

key-preserved tables

keys

unique

labels

data conversion, 5-22

MLSLABEL datatype, 5-17

LAST_ERROR_POSITION function, 14-30

LAST_ROW_COUNT function, 14-30

LAST_ROW_ID function, 14-30

LAST_SQL_FUNCTION_CODE function, 14-30

LBS

SeeLOB Buffering Subsystem

leaf level scalar attributes, 7-20

library units

remote dependencies, 10-16

listing information about procedures and packages, 10-79

LOB Buffering System (LBS)

LOB locators cannot span transactions, 6-34

LOBS

external (BFILEs), 6-5

LOBs, 6-1

accessing through a locator, 6-24

bind variables, 6-32

buffering

caveats, 6-48

pages can be aged out, 6-52

buffering operations, 6-50

buffering subsystem, 6-47

DBMS_LOB package, 6-68

external LOBs

in the object cache, 6-47

inline storage, 6-21

internal LOBs, 6-5

CACHE / NOCACHE, 6-10

CHUNK, 6-11

copying, 6-39

deleting, 6-39

ENABLE | DISABLE STORAGE IN ROW, 6-12

initializing, 6-14

locators, 6-22

locking before updating, 6-24

LOGGING / NOLOGGING, 6-11

PCTVERSION, 6-10

setting to empty, 6-15

tablespace and LOB index, 6-9

tablespace and storage characteristics, 6-8

LOB locators, 6-25

locators, 6-22

object cache, 6-47

performance, best practices, 6-58

performing SELECT on, 6-24

piecewise operations, 6-6, 6-28

read consistent locators, 6-25

setting to contain a locator, 6-23

setting to NULL, 6-14

typical uses, 6-4

updated LOB locators, 6-28

value, 6-21

varying-width character data, 6-7, 6-58

local procedures

in a package body, 10-15

LOCAL_TRANSACTION_ID function, 10-65

locators, 6-22

LOCK TABLE command, 3-11, 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, 3-13

privileges for manual acquirement, 3-15

user locks, 3-18

UTLLOCKT.SQL script, 3-27

LOGGING / NOLOGGING, 6-11

LONG datatype, 5-10

restrictions on, 5-10

use in triggers, 13-13

LONG RAW datatype, 5-10, 5-13

restrictions on, 5-10

use in triggers, 13-13

maintaining applications, 2-9

MAKE_REF operator, 8-5

manual locking, 3-10

LOCK TABLE command, 3-11

map methods, 7-11

MAX_ENABLED_ROLES parameter

default roles and, 17-11

MAXTRANS option, 4-5

memory

scalability, 10-54

message grouping, 11-10

message properties, specification, 11-68

message recipients, definition, 11-18

messages

between sessions, 12-2

producers and consumers, 11-13

messages as events, 11-4

messages, definition, 11-12

messaging system

metrics, 11-7

requirements, 11-7

methods of object types, 7-13

methods, comparison, 7-13

methods, map, 7-11

methods, order, 7-11, 7-15

migration

ROWID format, 5-16

MLSLABEL datatype, 5-17

modes

of parameters, 10-6

modifiable join view

definition of, 4-16

MULTISET operator, 8-5

multi-threaded server (MTS)

BFILEs, 6-21

mutating tables, 13-14

name resolution, 4-46

NAME_RESOLVE procedure, 10-67

national language support, 2-6

NCLOBs, 6-7, 6-69

NCHAR datatype, 5-2, 5-5

NCLOB datatype, 6-7

nested tables, 7-19

nested tables vs VARRAYs, 7-9, 7-12

nested tables, querying, 7-12

nested tables, uniqueness in, 7-21

NESTED_TABLE_ID hidden column, 7-20

NEW

correlation name, 13-9

NEW_LINE procedure, 12-23, 12-40

NEXT_ITEM_TYPE function, 12-11

NEXTVAL pseudo-column, 4-26

restrictions, 4-27

NLS_DATE_FORMAT parameter, 5-8

NOCACHE option

CREATE SEQUENCE statement, 4-29

normalization, 2-3

NOT NULL constraint

CHECK constraint and, 9-15

NVARCHAR2 datatype, 5-2, 5-5

object cache, 6-47

LOBs, 6-47

Object Database Designer, 2-3

object tables, 7-16

object types, comparison methods for, 7-13

object types, incomplete, 7-9

object types, methods of, 7-13

object views, 8-2

object views, creating, 8-3

object views, updating, 8-6

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

object-relational database management systems (ORDBMSs), 7-2

objects, schema

granting privileges, 17-14

listing information, 4-48

name resolution, 4-46

renaming, 4-48

revoking privileges, 17-16

when revoking object privileges, 17-18

OCI

See Oracle Call Interface

OLD

correlation name, 13-9

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

optimizer

using hints in applications, 2-5

OR REPLACE clause

for creating packages, 10-14

Oracle Advanced Queuing (Oracle AQ), 11-1

DBMS_AQADM package, 11-78

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

Oracle Procedure Builder, 1-3

Oracle Web Server Cartridges, 10-91

Oracle-supplied packages, 10-62, 10-67

where documented, 1-5

ORDBMS

Object Database Designer, 2-3

ORDBMSs, 7-2

order methods, 7-11, 7-15

OUT parameter mode, 10-6

outer joins, 4-21

key-preserved tables in, 4-22

overloading

of packaged functions, 10-54

stored procedure names, 10-5

using RESTRICT_REFERENCES, 10-54

PACK_MESSAGE procedure, 12-6

package body, 10-12

package specification, 10-12

packages

parallel server

distributed locks, 3-10

sequence numbers and, 4-25

parameter

default values, 10-9

with stored functions, 10-47

file

INIT.ORA, 12-31, 12-32

modes, 10-6

PARSE procedure, 14-4, 14-10

parsing large SQL statements, 14-11

parse tree, 13-18

pass-through SQL, 10-69

pcode

when generated for triggers, 13-18

PCTFREE storage parameter

altering, 4-8

block overhead and, 4-6

default, 4-3

guidelines for setting, 4-4, 4-5

indexes for, 4-5

non-clustered tables, 4-4

PCTUSED storage parameter

altering, 4-8

block overhead and, 4-6

default, 4-5

guidelines for setting, 4-5

non-clustered tables, 4-5

PCTVERSION, 6-10

performance

clusters, 4-38

index column order, 4-34

ROW_LOCKING parameter, 3-16

SERIALIZABLE option, 3-16

pipes, 12-2

communication between sessions, 12-2

domain of, 12-3

examples, 12-13

managing, 12-12

public or private, 12-2

PL/SQL, 10-2

pragma, 10-49

EXCEPTION_INIT pragma, 10-83

RESTRICT_REFERENCES pragma, 10-49, 10-51

SERIALLY_REUSABLE pragma, 10-54, 10-55

pragmas, 7-13

precompiler

applications, 10-4

precompilers, 10-40

preface

Send Us Your Comments, xxi

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

altering tables, 4-8

cluster creation, 4-40

creating integrity constraints, 9-17

creating tables, 4-7

creating triggers, 13-17

disabling triggers, 13-21

dropping a view, 4-15

dropping sequences, 4-30

dropping tables, 4-9

dropping triggers, 13-19

enabling roles and, 17-10

enabling triggers, 13-21

granting, 17-13, 17-14

index creation, 4-36

managing, 17-7, 17-13

manually acquiring locks, 3-15

on selected columns, 17-17

recompiling packages or procedures, 15-6

recompiling triggers, 13-19, 15-6

recompiling views, 15-5

renaming objects, 4-48

replacing views, 4-13

revoking, 17-13, 17-16

sequence creation, 4-25

stored procedure execution, 10-40

synonym creation, 4-30

triggers, 13-17

using a view, 4-15

using sequences, 4-30

view creation, 4-12

when revoking object privileges, 17-18

Pro*C daemon, 12-19

procedures

avoiding runtime compilation, 15-2

called by triggers, 13-13

data dictionary views, 10-79

listing compilation errors, 10-80

listing information about, 10-79

listing source code, 10-80

local, 10-15

size information, 10-81

SLEEP, 3-26

supplied, 10-62

using in applications, 2-5

profiles

application design and, 2-6

program units in PL/SQL, 10-2

pseudocolumns

modifying views, 13-4

PUBLIC user group

granting and revoking privileges to, 17-19

procedures and, 17-19

purchase order example, 7-2

PURGE_MIXED procedure, 10-65

purity level, 10-48

PUT procedure, 12-23, 12-25, 12-39

maximum output size for, 12-41

PUT_LINE procedure, 12-23, 12-25, 12-41

maximum output size for, 12-41

PUTF procedure, 12-41

queries

errors in distributed queries, 10-34

queue subscribers, definition, 11-18

queue tables, definition, 11-13

queues, definition, 11-12

queuing, 11-1, 11-5

DBMS_AQADM package, 11-78

RAISE statement, 10-32

RAISE_APPLICATION_ERROR procedure, 10-30

remote procedures, 10-35

raising exceptions

triggers, 13-11

RAW datatype, 5-13

RAWTOHEX function, 5-19

read consistency

LOBs, 6-25

read consistent locators, 6-24, 6-25, 6-26, 6-32, 6-34, 6-52, 6-54, 6-55, 6-57

READ_ONLY procedure, 10-65

READ_WRITE procedure, 10-65

read-only transactions, 3-8

RECEIVE_MESSAGE function, 12-9

recompilation

avoiding runtime, 15-2

reference semantics for BFILEs, 6-17

REFERENCING option, 13-10

referential integrity

distributed databases and, 9-13

one-to-many relationship, 9-9

one-to-one relationship, 9-9

privileges required to create foreign keys, 9-26

self-referential constraints, 13-31

triggers and, 13-28, 13-29, 13-30, 13-31, 13-32

REFs, constructing from object identifiers, 8-5

REFs, dereferencing of, 7-15

REFs, implicit dereferencing of, 7-15, 8-7

REFs, scoped, 7-19

REGISTER procedure, 16-5

remote dependencies, 10-16, 15-4

signatures, 10-18

specifying timestamps or signatures, 10-24

remote exception handling, 10-35, 13-11

REMOTE_DEPENDENCIES_MODE parameter, 10-24

REMOVE procedure, 16-5

REMOVE_PIPE procedure, 12-12

RENAME command, 4-47

renaming objects, 4-47

repeatable reads, 3-8, 3-10

RESET_PACKAGE procedure, 10-64

RESTRICT_REFERENCES pragma

syntax for, 10-49

using to control side effects, 10-49, 10-51

variant, 10-51

retention and message history, 11-9

reusable packages, 10-54

REVOKE command, 17-16

when in effect, 17-19

revoking privileges and roles

on selected columns, 17-17

REVOKE command, 17-16

revoking roles and privelieges (AQ), 11-64

RNDS argument, 10-49

RNPS argument, 10-49

ROLE_SYS_PRIVS view, 17-3

ROLE_TAB_PRIVS view, 17-3

roles

ROLLBACK command, 3-6

ROLLBACK procedure, 10-65

ROLLBACK_FORCE procedure, 10-65

ROLLBACK_SAVEPOINT procedure, 10-65

rolling back transactions

to savepoints, 3-6

roundtrips to the server, avoiding, 6-48, 6-54

row locking

manually locking, 3-15

row triggers

defining, 13-7

REFERENCING option, 13-10

timing, 13-3

UPDATE statements and, 13-7, 13-10

ROW_LOCKING parameter, 3-11, 3-16

ROWID datatype, 5-14

DBMS_ROWID package, 10-81

extended format, 10-88

extended ROWID format, 5-14

migration, 5-16

ROWIDTOCHAR function, 5-19

ROWLABEL column, 5-17

rows

chaining across blocks, 4-4

format, 4-2

header, 4-2

shown in ROWIDs, 5-15

size, 4-2

violating integrity constraints, 9-19

ROWTYPE_MISMATCH exception, 10-29

RR date format, 5-9

RS locks

LOCK TABLE command, 3-12

RX locks

LOCK TABLE command, 3-12

S locks

LOCK TABLE command, 3-13

sample programs

daemon.pc, 12-19

daemon.sql, 12-16

SAVEPOINT command, 3-6

SAVEPOINT procedure, 10-65

savepoints

maximum number of, 3-6

rolling back to, 3-6

scalability

serially reusable packages, 10-54

schemas, 17-7

scoped REFs, 7-19

security

dynamic SQL, 14-7

enforcing in applications, 2-8

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

policy for applications, 17-2, 17-5

roles, advantages, 17-3

when using the UTL_FILE package, 12-31

SELECT command

FOR UPDATE, 6-24

read consistency, 3-10, 6-25

SELECT ... FOR UPDATE, 3-15

SELF keyword, 7-15

semantics

copy-based for internal LOBs, 6-39

reference based for BFILEs, 6-17

Send Us Your Comments

boilerplate, xxi

SEND_MESSAGE function, 12-7

sequence of messages

retrieving, 11-6

SEQUENCE_CACHE_ENTRIES parameter, 4-29

sequences

accessing, 4-26

altering, 4-25

caching numbers, 4-25

caching sequence numbers, 4-28

creating, 4-24, 4-29

CURRVAL, 4-26, 4-27

dropping, 4-30

initialization parameters, 4-24

NEXTVAL, 4-26

parallel server, 4-25

privileges for creating, 4-25

privileges to alter, 4-25

privileges to drop, 4-30

privileges to use, 4-30

reducing serialization, 4-26

using in applications, 2-7

SERIALIZABLE option, 3-16

for ISOLATION LEVEL, 3-31

SERIALIZABLE parameter, 3-11

serializable transactions, 3-28

serially reusable PL/SQL packages, 10-54

SERIALLY_REUSABLE pragma, 10-55

Server Manager

DMBS_OUTPUT messages, 12-25

ENABLE procedure for output, 12-24

SESSION_MAX_OPEN_FILES parameter, 6-20

sessions

communicating between, 12-2

package state and, 10-16

SLEEP procedure, 3-26

SET ROLE command, 17-3, 17-11

when using operating system roles, 17-13

SET TRANSACTION command, 3-8

ISOLATION LEVEL clause, 3-31

SERIALIZABLE, 3-16, 3-31

SET_CLOSE_CACHED_OPEN_CURSORS procedure, 10-64

SET_DEFAULTS procedure, 16-8

SET_NLS procedure, 10-64

SET_ROLE procedure, 10-64

SET_SQL_TRACE procedure, 10-64

setting internal LOBs to empty, 6-15

setting LOBs to NULL, 6-14

SGA

See system global area

share locks (S)

LOCK TABLE command, 3-13

share row exclusive locks (SRX)

LOCK TABLE command, 3-14

shared SQL areas

using in applications, 2-6

side effects, 10-6, 10-48

SIGNAL procedure, 16-6

signatures

PL/SQL library unit dependencies, 10-16

to manage remote dependencies, 10-18

SLEEP procedure, 3-26

SORT_AREA_SIZE parameter

index creation and, 4-32

SQL DDL

BFILE security, 6-18

SQL DML

BFILE security, 6-19

SQL statements

access in PL/SQL, 10-62

application design and, 2-7

dynamic SQL, 14-2

execution, 3-2

in trigger bodies, 13-9, 13-12

larger than 32 KB, 14-11

not allowed in triggers, 13-12

pass-through SQL, 10-69

privileges required for, 17-8

when constraint checking occurs, 9-15

SQL*Loader

indexes and, 4-33

SQL*Module

applications, 10-4

calling stored procedures from, 10-5

SQL*Plus

anonymous blocks, 10-4

compile time errors, 10-36

creating a sequence, 10-13

DMBS_OUTPUT messages, 12-25

ENABLE procedure for output, 12-24

invoking stored procedures, 10-38

loading a procedure, 10-10

SET SERVEROUTPUT ON command, 10-3

SHOW ERRORS command, 10-36

SRX locks

LOCK Table command, 3-14

standards

ANSI, 3-16

compliance, 2-7

state

session, of package objects, 10-16

statement triggers

conditional code for statements, 13-10

row evaluation order, 13-13

specifying SQL statement, 13-6

timing, 13-3

trigger evaluation order, 13-14

UPDATE statements and, 13-7, 13-10

valid SQL statements, 13-12

STEP_ID function, 10-65

storage

object tables, 7-20

storage parameters

PCTFREE, 4-8

PCTUSED, 4-8

stored functions, 10-4

creating, 10-9

stored procedures, 10-4

structs

arrays of in C, 2-7

structured payload, 11-9

subscription & recipient lists, 11-10

supplied procedures, 10-62

synchronous communication, 11-7

synonyms

creating, 4-30

dropped tables and, 4-9

dropping, 4-31

privileges, 4-30, 4-31

stored procedures and packages, 10-44

using, 4-31

SYSDATE function, 5-9

system global area

buffers DBMS_OUTPUT data, 12-25

buffers pipes information, 12-2

holds sequence number cache, 4-28

system-specific Oracle documentation, 3-19, 10-71, 12-3, 12-23, 13-3, 14-2, 15-7, 16-3

PL/SQL wrapper, 10-30

UTLDTREE.SQL script, 15-3

tables

tables, nested, 7-19

tables, object, See object tables

temporary segments

index creation and, 4-32

third generation language, 10-2

thread safety

in OCI applications, 2-7

timestamps

PL/SQL library unit dependencies, 10-16

TO_CHAR function, 5-19

CC date format, 5-9

converting Trusted Oracle labels, 5-22

RR date format, 5-9

TO_DATE function, 5-8, 5-19

RR date format, 5-9

TO_LABEL function

converting Trusted Oracle labels, 5-22

TO_NUMBER function, 5-19

transactions

external LOBs do not participate, 6-6

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

LOB locators cannot span, 6-34

SET TRANSACTION command, 3-8

triggers

about, 10-4

accessing column values, 13-9

AFTER, 13-3, 13-9, 13-24, 13-25

auditing with, 13-23, 13-24

BEFORE, 13-3, 13-9, 13-35, 13-36

body, 13-8, 13-10, 13-11, 13-12

check constraints, 13-34, 13-35

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

compiled, 13-18

conditional predicates, 13-8, 13-10

creating, 13-2, 13-12, 13-17

data access restrictions, 13-35

debugging, 13-19

designing, 13-2

disabling, 13-20

distributed query creation, 10-34

dropped tables and, 4-9

enabling, 13-20

error conditions and exceptions, 13-11

events, 13-6

examples, 13-22, 13-24, 13-26, 13-29, 13-34, 13-35, 13-37

FOR EACH ROW clause, 13-7

generating derived column values, 13-36

illegal SQL statements, 13-12

INSTEAD OF triggers, 13-4

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

listing information about, 13-21

migration issues, 13-19

modifying, 13-19

multiple same type, 13-14

mutating tables and, 13-14

naming, 13-3

package variables and, 13-13

prerequisites before creation, 13-3

privileges, 13-17

privileges to disable, 13-21

privileges to drop, 13-19

privileges to recompile, 15-6

procedures and, 13-13

recompiling, 13-18, 15-6

REFERENCING option, 13-10

referential integrity and, 13-28, 13-30, 13-31, 13-32

remote dependencies and, 13-13

remote exceptions, 13-11

restrictions, 13-8, 13-12

row, 13-7

row evaluation order, 13-13

scan order, 13-13

stored, 13-18

trigger evaluation order, 13-14

use of LONG and LONG RAW datatypes, 13-13

username reported in, 13-17

using in applications, 2-5

WHEN clause, 13-8

TRUNC function, 5-9

TRUNCATE TABLE command, 4-9

Trusted Oracle Server

converting labels, 5-22

dynamic SQL, 14-7

maintaining the Trusted Database List, 10-69

MLSLABEL datatype, 5-17

tuning

overview, 2-8

using LONGs, 5-11

unhandled exceptions, 10-33

UNIQUE key constraints

altering, 9-24

combining with NOT NULL constraint, 9-4

composite keys and nulls, 9-6

data integrity, 9-24

disabling, 9-20

enabling, 9-20

PRIMARY KEY constraint vs., 9-6

when to use, 9-6

UNIQUE_SESSION_ID function, 10-64

UNPACK_MESSAGE procedures, 12-11

UPDATE command

column values and triggers, 13-9

data consistency, 3-10

triggers and, 13-7, 13-10

triggers for referential integrity, 13-30, 13-31

updated locators, 6-25, 6-28, 6-33, 6-34, 6-51

updating applications, 2-9

updating tables

with parent keys, 9-10, 9-11

USE_ROLLBACK_SEGMENT procedure, 10-65

USER function, 9-4

user locks

requesting, 3-18

USER_ERRORS view

debugging stored procedures, 10-37

USER_QUEUE_TABLES, 11-99

USER_QUEUES, 11-100

USER_SOURCE view, 10-37

user-defined errors, 10-30, 10-32

usernames

as reported in a trigger, 13-17

schemas and, 17-7

users

dropped roles and, 17-13

enabling roles for, 17-3

PUBLIC group, 17-19

restricting application roles, 17-5

UTL_FILE package, 12-30

security issues, 12-31

UTL_HTTP package, 10-70, 10-91

UTLDTREE.SQL file, 15-2, 15-7

UTLEXCPT.SQL file, 9-23

UTLLOCKT.SQL script, 3-27

value of LOBs, 6-21

VARCHAR datatype, 5-5

VARCHAR2 datatype, 5-2, 5-5

column length, 5-6

when to use, 5-5

VARCHAR2S datatype, 14-11

VARIABLE_VALUE procedure, 14-7, 14-25

VARRAYs vs nested tables, 7-9, 7-12

VARRAYs, See arrays

views

violating integrity constraints, 9-19

WAITANY procedure, 16-6

WAITONE procedure, 16-7

WHEN clause, 13-8

cannot contain PL/SQL expressions, 13-8

correlation names, 13-9

examples, 13-2, 13-7, 13-21, 13-29

EXCEPTION examples, 13-11, 13-29, 13-34, 13-35

WITH GRANT OPTION, 17-15

WNDS argument, 10-49

WNPS argument, 10-49

Workflow, 11-5

World Wide Web callouts, 10-70, 10-91

wrapper to hide PL/SQL code, 10-30

X locks

LOCK TABLE command, 3-14

year 2000, 5-9

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