Oracle8i Application Developer's Guide - Fundamentals
Release 8.1.5
A68003-01

Library
Product
Contents

Prev

Index

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  Z 

Symbols

%ROWTYPE attribute, 10 - 7
used in stored functions, 10 - 8
%TYPE attribute, 10 - 7

A

access
database
granting privileges, 12 - 16
revoking privileges, 12 - 18
objects
sequences, 3 - 37
schema objects
granting privileges, 12 - 17
remote integrity constraints, 5 - 14
revoking privileges, 12 - 19
triggers, 13 - 3, 13 - 48
advantages
object views, 17 - 3
OCI, 1 - 8
AFTER triggers
auditing and, 13 - 35, 13 - 38
correlation names and, 13 - 16
specifying, 13 - 7
ALL_ERRORS view
debugging stored procedures, 10 - 43
ALL_SOURCE view, 10 - 43
allocation
extents, 6 - 17
ALTER CLUSTER command, 3 - 6
ALLOCATE EXTENT option, 6 - 17
ALTER INDEX command, 3 - 6
ALTER SEQUENCE command, 3 - 37
ALTER SESSION command
SERIALIZABLE, 8 - 19, 8 - 26
ALTER TABLE command, 3 - 6, 3 - 9
defining integrity constraints, 5 - 18
DISABLE ALL TRIGGERS option, 13 - 31
DISABLE integrity constraint option, 5 - 23
DROP integrity constraint option, 5 - 29
ENABLE ALL TRIGGERS option, 13 - 30
ENABLE integrity constraint option, 5 - 23
INITRANS parameter, 8 - 26
ALTER TRIGGER command
DISABLE option, 13 - 30
ENABLE option, 13 - 30
altering
storage parameters, 3 - 9
tables, 3 - 9
American National Standards Institute (ANSI)
ANSI-compatible locking, 8 - 19
anonymous PL/SQL blocks
about, 10 - 2
compared to triggers, 10 - 26
ANSI SQL92
FIPS flagger, 8 - 2
application context, 12 - 22
application roles, 12 - 3
applications
calling stored procedures and packages, 10 - 52
roles, 12 - 4
security, 12 - 7
unhandled exceptions in, 10 - 46
arrays, 16 - 28
auditing
triggers and, 13 - 34
autonomous routine, 8 - 33
autonomous scope
versus autonomous transaction, 8 - 33
autonomous transactions, 8 - 33
defining, 8 - 41
AUTONOMOUS_TRANSACTION, 8 - 33

B

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, 4 - 18
blank padding data
performance considerations, 4 - 6
body
triggers, 13 - 14, 13 - 17, 13 - 18, 13 - 20
Boolean expressions, 4 - 27
bulk binds, 10 - 22
DML statements, 10 - 23
FOR loops, 10 - 25
SELECT statements, 10 - 24
usage, 10 - 23
BY REF phrase, 11 - 28

C

CACHE option
CREATE SEQUENCE command, 3 - 41
caches
sequence cache, 3 - 40
sequence numbers, 3 - 36
callback, 11 - 44
example, 11 - 44
restrictions, 11 - 46
cancelling a cursor, 8 - 10
CASCADE option
integrity constraints, 6 - 18
CATPROC.SQL script, 13 - 4, 14 - 2
CC date format, 4 - 12
century, 4 - 10
date format masks, 4 - 8
CHAR datatype, 4 - 2, 4 - 5
column length, 4 - 6
increasing column length, 3 - 9
when to use, 4 - 5
CHARSETFORM property, 11 - 24
CHARSETID property, 11 - 24
CHARTOROWID function, 4 - 25
CHECK constraint
data integrity, 5 - 22
designing, 5 - 16
NOT NULL constraint and, 5 - 16
number of, 5 - 16
restricting nulls using, 5 - 16
restrictions on, 5 - 15
triggers and, 13 - 40, 13 - 47
when to use, 5 - 15
client events, 14 - 5
clusters
allocating extents, 6 - 17
choosing data, 6 - 14, 6 - 15
creating, 6 - 15
dropped tables and, 3 - 10
dropping, 6 - 17
index creation, 6 - 16
integrity constraints and, 6 - 16
keys, 6 - 14
performance considerations, 6 - 15
privileges for creating, 6 - 16
collections, 18 - 13
nesting, 18 - 22
querying, 18 - 13
column objects
storage, 18 - 2
vs. row objects, 18 - 2
columns
accessing in triggers, 13 - 15
default values, 5 - 4
generating derived values with triggers, 13 - 49
granting privileges for selected, 12 - 17
increasing length, 3 - 9
listing in an UPDATE trigger, 13 - 6, 13 - 18
multiple FOREIGN KEY constraints, 5 - 10
number of CHECK constraints limit, 5 - 16
revoking privileges from, 12 - 19
COMMIT command, 8 - 5
comparison methods, 16 - 22
comparison operators
blank padding data, 4 - 6
comparing dates, 4 - 9
compile-time errors, 10 - 42
complex object retrieval
for Oracle Call Interface, 19 - 8
composite keys
restricting nulls in, 5 - 16
COMPRESS clause
nested tables, 18 - 18
concurrency, 8 - 23
conditional predicates
trigger bodies, 13 - 14, 13 - 17
consistency
read-only transactions, 8 - 8
constraining tables, 13 - 22
constraints, 16 - 27
composite UNIQUE keys, 5 - 6
on Oracle objects, 18 - 39
REFs, 18 - 10
restriction on stored functions, 10 - 57
SCOPE FOR constraint, 16 - 30, 16 - 34
conversion functions, 4 - 25
TO_CHAR function, 4 - 11, 4 - 12, 4 - 28
TO_DATE function, 4 - 11
TO_LABEL function, 4 - 28
Trusted Oracle Server, 4 - 28
converting data, 4 - 25
ANSI datatypes, 4 - 24
assignments, 4 - 25
expression evaluation, 4 - 27
SQL/DS and DB2 datatypes, 4 - 24
Trusted Oracle Server, 4 - 28
correlation names, 13 - 13, 13 - 15, 13 - 16, 13 - 17
NEW, 13 - 16
OLD, 13 - 16
REFERENCING option and, 13 - 17
when preceded by a colon, 13 - 16
COUNT attribute of collection types, 16 - 24
CREATE CLUSTER command, 3 - 6, 6 - 15
hash clusters, 6 - 20
HASH IS option, 6 - 21
HASHKEYS option, 6 - 21
CREATE INDEX command, 3 - 6, 6 - 5
ON CLUSTER option, 6 - 16
CREATE PACKAGE BODY command, 10 - 14
CREATE PACKAGE command, 10 - 14
CREATE ROLE command, 12 - 11
CREATE SCHEMA command, 3 - 45
privileges required, 3 - 46
CREATE SEQUENCE command
CACHE option, 3 - 36, 3 - 41
examples, 3 - 41
NOCACHE option, 3 - 41
CREATE TABLE command, 3 - 3, 3 - 4, 3 - 6
CLUSTER option, 6 - 16
defining integrity constraints, 5 - 18
INITRANS parameter in, 8 - 26
CREATE TRIGGER command, 13 - 3
REFERENCING option, 13 - 17
CREATE TYPE statement
object types, 16 - 16
varray, 16 - 17
CREATE VIEW command, 3 - 22
OR REPLACE option, 3 - 24
WITH CHECK OPTION, 3 - 22, 3 - 26
creating
clusters, 6 - 15
hash clusters, 6 - 20
indexes, 6 - 5
integrity constraints, 5 - 2
multiple objects, 3 - 45
packages, 10 - 14
sequences, 3 - 41
synonyms, 3 - 43
tables, 3 - 3, 3 - 4
triggers, 13 - 3, 13 - 20
views, 3 - 22
CURRVAL pseudo-column, 3 - 38
restrictions, 3 - 39
cursor variables, 10 - 38
declaring and opening, 10 - 38
cursors, 8 - 9
cancelling, 8 - 10
closing, 8 - 10
maximum number of, 8 - 9
pointers to, 10 - 38
private SQL areas and, 8 - 9

D

data blocks
factors affecting size of, 3 - 6
shown in ROWIDs, 4 - 20
data control in OO4O, 1 - 19
data conversion, 4 - 25
ANSI datatypes, 4 - 24
assignments, 4 - 25
expression evaluation, 4 - 27
SQL/DS and DB2 datatypes, 4 - 24
Trusted Oracle labels, 4 - 28
data dictionary
compile-time errors, 10 - 43
dropped tables and, 3 - 10
integrity constraints in, 5 - 32
procedure source code, 10 - 43
schema object views, 3 - 50
data object number
extended ROWID, 4 - 19, 4 - 20
database
administrator
application administrator vs., 12 - 3
global name in a distributed system, 3 - 46
security
applications and, 12 - 3
schemas and, 12 - 9
database event notification, 14 - 1, 15 - 5
datafiles
shown in ROWIDs, 4 - 20
datatypes, 4 - 2
ANSI/ISO, 4 - 24
CHAR, 4 - 2, 4 - 5
choosing a character datatype, 4 - 5
column lengths for character types, 4 - 6
data conversion, 4 - 25
DATE, 4 - 8, 4 - 10
DB2, 4 - 24
LONG, 4 - 15
LONG RAW, 4 - 16, 4 - 18
MLSLABEL, 4 - 23
NCHAR, 4 - 2, 4 - 5
NUMBER, 4 - 7
NVARCHAR2, 4 - 2, 4 - 5
RAW, 4 - 18
ROWID, 4 - 18
SQL/DS, 4 - 24
summary of datatypes, 4 - 2
VARCHAR, 4 - 5
VARCHAR2, 4 - 2, 4 - 5
date arithmetic, 4 - 27
DATE datatype, 4 - 8
centuries, 4 - 10
data conversion, 4 - 25
DBA_ERRORS view
debugging stored procedures, 10 - 43
DBA_ROLE_PRIVS view, 12 - 4
DBA_SOURCE view, 10 - 43
DBMS_LOCK package, 8 - 21
DBMS_SQL package
advantages of, 9 - 17
bulk SQL, 9 - 17
client-side programs, 9 - 17
DESCRIBE, 9 - 17
differences with native dynamic SQL, 9 - 12
multiple row updates and deletes, 9 - 18
RETURNING clause, 9 - 18
See Also dynamic SQL
DDL statements
package state and, 10 - 15
DEBUG_EXTPROC package, 11 - 48
debugging
stored procedures, 10 - 49
triggers, 13 - 29
default
column values, 5 - 4, 10 - 57
maximum savepoints, 8 - 6
parameters in stored functions, 10 - 60
PCTFREE option, 3 - 4
PCTUSED option, 3 - 6
role, 12 - 13
defining autonomous transactions, 8 - 41
DELETE command
column values and triggers, 13 - 16
data consistency, 8 - 11
triggers for referential integrity, 13 - 43, 13 - 44
dependencies
among PL/SQL library objects, 10 - 28
in stored triggers, 13 - 27
schema objects
trigger management, 13 - 20
the timestamp model, 10 - 29
dereferencing, 16 - 24
implicit, 16 - 24
DESC function, 6 - 8
DETERMINISTIC keyword, 10 - 63
disabling
integrity constraints, 5 - 22
triggers, 13 - 30
distributed databases
referential integrity and, 5 - 14
remote stored procedures, 10 - 54, 10 - 55
triggers and, 13 - 20
distributed queries
handling errors, 10 - 47
DML_LOCKS parameter, 8 - 11
DROP CLUSTER command, 6 - 18, 6 - 21
DROP INDEX command, 6 - 6
privileges required, 6 - 6
DROP ROLE command, 12 - 16
DROP TABLE command, 3 - 10
DROP TRIGGER command, 13 - 29
dropping
clusters, 6 - 17
hash clusters, 6 - 21
indexes, 6 - 6
integrity constraints, 5 - 29
packages, 10 - 11
procedures, 10 - 11
roles, 12 - 16
sequences, 3 - 41
synonyms, 3 - 44
tables, 3 - 10
triggers, 13 - 29
views, 3 - 27
dynamic SQL
application development languages, 9 - 24
DML statements, 9 - 3
invoker-rights, 9 - 8
invoking PL/SQL blocks, 9 - 7
optimization, 9 - 6
queries, 9 - 4
scenario, 9 - 9
See Also DBMS_SQL package
See Also native dynamic SQL
usage, 9 - 3
dynamically modified statement, 12 - 27

E

embedded SQL, 10 - 2
enabling
integrity constraints, 5 - 22
at creation, 5 - 20, 5 - 22
reporting exceptions, 5 - 25
when violations exist, 5 - 21
roles, 12 - 14
triggers, 13 - 30
errors
application errors raised by Oracle packages, 10 - 44
creating views with errors, 3 - 23
remote procedures, 10 - 47
user-defined, 10 - 44, 10 - 45
establishing, 12 - 1
event attribute functions, 14 - 2
event publication, 13 - 55, 14 - 1
advanced queueing, 13 - 54
context, 13 - 55
error handling, 13 - 55
execution model, 13 - 55
framework, 13 - 54
triggering, 13 - 54
example, purchase order, 16 - 3
exception handlers
in PL/SQL, 10 - 2
exceptions
anonymous blocks, 10 - 3
during trigger execution, 13 - 18
effects on applications, 10 - 46
remote procedures, 10 - 47
unhandled, 10 - 46
exclusive locks
LOCK TABLE command, 8 - 16
explicit locking
manual locking, 8 - 11
Export
Oracle objects, 18 - 41
extended ROWID format, 4 - 19
extents
allocating, 6 - 17
dropped tabled and, 3 - 10
external procedure, 11 - 3
DEBUG_EXTPROC package, 11 - 48
debugging, 11 - 47
maximum number of parameters, 11 - 50
restrictions, 11 - 50
specifying datatypes, 11 - 16
extproc process, 11 - 33

F

fine-grained access control, 12 - 26
FIPS flagger
interactive SQL statements and, 8 - 2
FIXED_DATE initialization parameter, 4 - 9
FOR EACH ROW clause, 13 - 12
foreign key
representing many-to-one entity relationship with, 16 - 7
FOREIGN KEY constraint
defining, 5 - 30, 5 - 31
enabling, 5 - 22, 5 - 31
NOT NULL constraint and, 5 - 9
number of rows referencing parent table, 5 - 9
one-to-many relationship, 5 - 9
one-to-one relationship, 5 - 9
UNIQUE key constraint and, 5 - 9
updating tables, 5 - 10, 5 - 11
format masks
TO_DATE function, 4 - 8
function-based indexes
returning values of type methods, 18 - 32
functions
See Also PL/SQL

G

GRANT command, 12 - 16
object privileges, 12 - 17
system privileges, 12 - 16
when in effect, 12 - 22
granting privileges and roles, 12 - 16

H

hash clusters
choosing key, 6 - 20
creating, 6 - 20
dropping, 6 - 21
root block, 6 - 20
when to use, 6 - 20
HEXTORAW function, 4 - 25
hiding PL/SQL code, 10 - 27

I

implicit dereferencing, 16 - 24
Import
Oracle objects, 18 - 41
IN OUT parameter mode, 10 - 6
IN parameter mode, 10 - 6
incomplete object types, 16 - 16
indexes
creating, 6 - 5
dropped tables and, 3 - 10
dropping, 6 - 6
function-based, 6 - 6
guidelines, 6 - 3
order of columns, 6 - 4
privileges, 6 - 5
specifying PCTFREE for, 3 - 6
SQL*Loader and, 6 - 2
temporary segments and, 6 - 2
when to create, 6 - 2
index-organized tables
storing nested tables as, 18 - 17
INDICATOR property, 11 - 23
inheritance, 18 - 33
dual subtype/super-type reference, 18 - 38
subtype contains super-type, 18 - 35
super-type contains all subtypes, 18 - 37
initialization parameters
DML_LOCKS, 8 - 11
OPEN_CURSORS, 8 - 9
REMOTE_DEPENDENCIES_MODE, 10 - 35
ROW_LOCKING, 8 - 11, 8 - 19
SERIALIZABLE, 8 - 11
INITRANS parameter, 8 - 26
INSERT command
column values and triggers, 13 - 16
read consistency, 8 - 11
INSTEAD OF triggers, 13 - 7
object views, 17 - 16, 17 - 17
on nested table view columns, 13 - 16
integrity constraints
application uses, 5 - 2
clusters and, 6 - 16
defining, 5 - 18
disabling, 5 - 20, 5 - 21, 5 - 22, 5 - 23
dropping, 5 - 29
enabling, 5 - 21
enabling at creation, 5 - 20
enabling when violations exist, 5 - 21
examples, 5 - 2
exceptions to, 5 - 25
listing definitions of, 5 - 32
naming, 5 - 20
performance considerations, 5 - 3
privileges required for creating, 5 - 19
restrictions for adding or dropping, 5 - 19
triggers vs., 13 - 2, 13 - 40
violations, 5 - 21
when to disable, 5 - 21
when to use, 5 - 2
interactive block execution, 10 - 51
invalid views, 3 - 27
invoker-rights
dynamic SQL, 9 - 8
object types, 18 - 30
ISOLATION LEVEL
changing, 8 - 26
SERIALIZABLE, 8 - 26

J

Java
in the RDBMS, 1 - 29
Oracle JDBC and Oracle objects, 19 - 19
Oracle SQLJ and Oracle objects, 19 - 19
with Oracle objects, 19 - 19
JDBCSee Oracle JDBC
join view, 3 - 28
DELETE statements, 3 - 31
key-preserved tables in, 3 - 29
mergeable, 3 - 29
modifying
rule for, 3 - 30
UPDATE statements, 3 - 30
when modifiable, 3 - 28
JPublisher, 1 - 35

K

key-preserved tables
in join views, 3 - 29
in outer joins, 3 - 34
keys
foreign keys, 5 - 30, 16 - 7
unique
composite, 5 - 6

L

labels
data conversion, 4 - 28
MLSLABEL datatype, 4 - 23
library units
remote dependencies, 10 - 28
loading
Oracle objects, 18 - 41
loadjava utility, 1 - 37
LOB datatype
support in OO4O, 1 - 17
use in triggers, 13 - 20
LOB support in OO4O, 1 - 17
locators, 16 - 33, 18 - 20
LOCK TABLE command, 8 - 12
locking
indexed foreign keys and, 5 - 11
manual (explicit), 8 - 11
row locking mode, 8 - 19
serializable mode, 8 - 19
unindexed foreign keys and, 5 - 10
locks
distributed, 8 - 11
LOCK TABLE command, 8 - 12, 8 - 13
privileges for manual acquirement, 8 - 16
user locks, 8 - 21
UTLLOCKT.SQL script, 8 - 22
LONG datatype, 4 - 15
restrictions on, 4 - 16
use in triggers, 13 - 20
LONG RAW datatype, 4 - 16, 4 - 18
restrictions on, 4 - 16
use in triggers, 13 - 20
LOWER function, 6 - 8

M

manual locking, 8 - 11
LOCK TABLE command, 8 - 12
map methods, 16 - 19, 18 - 8
MAX_ENABLED_ROLES parameter
default roles and, 12 - 13
MAXTRANS option, 3 - 6
memory
scalability, 10 - 70
methods, 18 - 27
choosing a language for, 18 - 27
comparison, 16 - 22
function-based indexes, 18 - 32
map, 16 - 19, 18 - 8
of object types, 16 - 23
order, 16 - 19, 16 - 25, 18 - 8
static, 18 - 29
migration
ROWID format, 4 - 21
MLSLABEL datatype, 4 - 23
modes
of parameters, 10 - 6
modifiable join view
definition of, 3 - 28
mutating tables, 13 - 22

N

name resolution, 3 - 46
native dynamic SQL
advantages of, 9 - 13
differences with DBMS_SQL package, 9 - 12
fetching into records, 9 - 16
performance, 9 - 15
See Also dynamic SQL
user-defined types, 9 - 16
NCHAR datatype, 4 - 2, 4 - 5
nested tables, 18 - 13, 18 - 16
COMPRESS clause, 18 - 18
creating indexes on, 18 - 19
DML operations on, 18 - 21
in an index-organized table, 18 - 17
querying, 16 - 20
returning as locators, 16 - 33, 18 - 20
storage, 16 - 32, 18 - 16
uniqueness in, 16 - 33
vs VARRAY, 16 - 17, 16 - 20
NESTED_TABLE_ID, 16 - 32, 18 - 19
NEW correlation name, 13 - 16
NEXTVAL pseudo-column, 3 - 38
restrictions, 3 - 39
NLS_DATE_FORMAT parameter, 4 - 8
NLSSORT order, and indexes, 6 - 8
NOCACHE option
CREATE SEQUENCE statement, 3 - 41
NOT NULL constraint
CHECK constraint and, 5 - 16
data integrity, 5 - 22
when to use, 5 - 3
NOWAIT option, 8 - 12
NUMBER datatype, 4 - 7
NVARCHAR2 datatype, 4 - 2, 4 - 5

O

object cache
flushing an object, 19 - 13
object columns, indexes on, 6 - 7
object identifiers, 16 - 28
primary-key based, 18 - 8
REFs, 18 - 9
storage, 18 - 8
object support in OO4O, 1 - 17
object tables, 16 - 26, 18 - 8
deleting values, 16 - 41
inserting values, 16 - 36
querying, 16 - 39
object types
column objects vs. row objects, 18 - 2
comparison methods for, 16 - 22
incomplete, 16 - 16
invoker-rights, 18 - 30
methods of, 16 - 23
object views, 17 - 2
advantages, 17 - 3
updating, 17 - 15
object-relational model, 16 - 1
comparing objects, 18 - 8
constraints, 18 - 39
design considerations, 18 - 1
embedded objects, 16 - 28
implementing with object tables, 16 - 16
inheritance, 18 - 33
limiations of relational model, 16 - 13
methods, 18 - 27
new object format, 18 - 33
partitioning, 16 - 41
programmatic environments for, 19 - 1
replication, 18 - 33
type evolution, 18 - 40
objects
collection objects, 17 - 6
in columns, 17 - 4
object references, 17 - 8
row objects and object identifiers, 17 - 6
objects, schema
granting privileges, 12 - 17
listing information, 3 - 50
name resolution, 3 - 46
renaming, 3 - 48
revoking privileges, 12 - 19
when revoking object privileges, 12 - 21
OCI
advantages, 1 - 8
object cache, 17 - 23
overview, 1 - 7
parts of, 1 - 8
OID, See object identifiers
OLD correlation name, 13 - 16
one-to-many relationship
with foreign keys, 5 - 9
one-to-one relationship
with foreign keys, 5 - 9
OO4O
features, 1 - 12
Oracle Objects for OLE, 1 - 11
OPEN_CURSORS parameter, 8 - 9
operating system
roles and, 12 - 15
OR REPLACE clause
for creating packages, 10 - 14
OraAQ object, 1 - 16
OraAQAgent object, 1 - 17
OraAQMsg object, 1 - 17
OraBFILE object, 1 - 18
OraBLOB object, 1 - 18
Oracle Call Interface, 10 - 2
applications, 10 - 4
associative access, 19 - 2
cancelling cursors, 8 - 10
closing cursors, 8 - 10
complex object retrieval (COR), 19 - 8
controlling object cache size, 19 - 5
creating a new object, 19 - 10
deleting an object, 19 - 12
for Oracle objects
building a program, 19 - 4
initializing object manipulation, 19 - 5
lock options, 19 - 7
locking an object, 19 - 12
navigational access, 19 - 3
object cache, 19 - 3
flushing an object, 19 - 13
pin options, 19 - 6
pinning and unpinning objects, 19 - 5
See OCI
updating an object, 19 - 12
with Oracle objects, 19 - 2
Oracle Data Control (ODC), 1 - 19
Oracle errors, 10 - 3
Oracle JDBC
accessing Oracle object data, 19 - 19
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 - 13
OraCollection interface, 19 - 18
OraObject interface, 19 - 17
OraRef interface, 19 - 17
overview, 1 - 11
with Oracle objects, 19 - 16
Oracle objects, See object-relational model
Oracle Precompilers
calling stored procedures and packages, 10 - 52
Oracle SQLJ
advantages over JDBC, 1 - 32
compiling custom Java classes, 19 - 24
creating custom Java classes, 19 - 24
custom Java classess, 19 - 21
CustomDatum interface, 19 - 21
definition, 1 - 31
design, 1 - 32
example, 1 - 34
in the server, 1 - 37
JPublisher, 19 - 24
Oracle object methods, 19 - 23
stored programs, 1 - 37
strongly types objects, 19 - 25
support for Oracle objects, 19 - 19
weakly types objects, 19 - 26
Oracle supplied packages, 10 - 16
OraCLOB object, 1 - 18
OraCollection interface, 19 - 18
OraDatabase object, 1 - 14
OraDynaset object, 1 - 15
OraField object, 1 - 15
OraMeta Data object, 1 - 15
OraObject interface, 19 - 17
OraParamArray object, 1 - 16
OraParameter object, 1 - 15
OraRef interface, 19 - 17
OraServer object, 1 - 14
OraSession object, 1 - 13
OraSQLStmt object, 1 - 16
order methods, 16 - 19, 16 - 25, 18 - 8
OUT parameter mode, 10 - 6
outer joins, 3 - 33
key-preserved tables in, 3 - 34
overloading
of packaged functions, 10 - 70
stored procedure names, 10 - 12
using RESTRICT_REFERENCES, 10 - 70

P

package body, 10 - 12
package specification, 10 - 12
packages
creating, 10 - 14
DBMS_OUTPUT
example of use, 10 - 3
DEBUG_EXTPROC, 11 - 48
dropping, 10 - 11
in PL/SQL, 10 - 12
naming of, 10 - 15
privileges for execution, 10 - 53
privileges required to create, 10 - 15
privileges required to create procedures in, 10 - 10
serially reusable packages, 10 - 70
session state and, 10 - 15
synonyms, 10 - 56
where documented, 10 - 16
parallel query
restrictions for Oracle objects, 18 - 41
parallel server
distributed locks, 8 - 11
sequence numbers and, 3 - 37
PARALLEL_ENABLE keyword, 10 - 63
parameter
default values, 10 - 9
with stored functions, 10 - 60
modes, 10 - 6
parse tree, 13 - 27
partitioning
Oracle objects, 16 - 41
Pascal Calling Standard, 11 - 9
pcode
when generated for triggers, 13 - 27
PCTFREE storage parameter
altering, 3 - 9
block overhead and, 3 - 7
default, 3 - 4
guidelines for setting, 3 - 4, 3 - 5, 3 - 6
indexes for, 3 - 6
non-clustered tables, 3 - 5
PCTUSED storage parameter
altering, 3 - 9
block overhead and, 3 - 7
default, 3 - 6
guidelines for setting, 3 - 6
non-clustered tables, 3 - 6
performance
clusters, 6 - 15
index column order, 6 - 4
native dynamic SQL, 9 - 15
ROW_LOCKING parameter, 8 - 19
SERIALIZABLE option, 8 - 19
PL/SQL, 10 - 2
advantages, 1 - 3
anonymous blocks, 10 - 2
calling remote stored procedures, 10 - 55
cursor variables, 10 - 38
dependencies among library units, 10 - 28
exception handlers, 10 - 2
features, 1 - 3
functions
arguments, 10 - 60
overloading, 10 - 70
parameter default values, 10 - 60
purity level, 10 - 69
RESTRICT_REFERENCES pragma, 10 - 66
using, 10 - 57
hiding source code, 10 - 27
invoking with dynamic SQL, 9 - 7
objects, 1 - 5
packages, 10 - 12
program units, 10 - 2
dropped tables and, 3 - 10
replaced views and, 3 - 25
RAISE statement, 10 - 45
sample code, 1 - 2
serially reusable packages, 10 - 70
tables, 10 - 9
of records, 10 - 9
trigger bodies, 13 - 14, 13 - 15
user-defined errors, 10 - 45
wrapper to hide code, 10 - 27
pragma, 8 - 33, 8 - 41, 16 - 22
RESTRICT_REFERENCES pragma, 10 - 66
SERIALLY_REUSABLE pragma, 10 - 70, 10 - 71
precompilers, 10 - 52
applications, 10 - 4
preface
Send Us Your Comments, xxi
PRIMARY KEY constraint
choosing a primary key, 5 - 5
disabling, 5 - 22
enabling, 5 - 22
multiple columns in, 5 - 6
UNIQUE key constraint vs., 5 - 6
private SQL areas
cursors and, 8 - 9
privileges
altering sequences, 3 - 37
altering tables, 3 - 10
cluster creation, 6 - 16
creating integrity constraints, 5 - 19
creating tables, 3 - 8
creating triggers, 13 - 25
dropping a view, 3 - 27
dropping sequences, 3 - 42
dropping tables, 3 - 11
dropping triggers, 13 - 29
enabling roles and, 12 - 13
granting, 12 - 16, 12 - 17
index creation, 6 - 5
managing, 12 - 10, 12 - 16
manually acquiring locks, 8 - 16
on selected columns, 12 - 19
recompiling triggers, 13 - 28
renaming objects, 3 - 48
replacing views, 3 - 25
revoking, 12 - 16, 12 - 18, 12 - 19
sequence creation, 3 - 37
stored procedure execution, 10 - 53
synonym creation, 3 - 43
triggers, 13 - 25
using a view, 3 - 27
using sequences, 3 - 41
view creation, 3 - 24
Pro*C/C++
applications, 1 - 20
associative access, 19 - 14
converting between Oracle and C types, 19 - 15
features, 1 - 21
navigational access, 19 - 14
new features, 1 - 22
with Oracle objects, 19 - 14
Pro*COBOL
applications, 1 - 23
features, 1 - 24
new features, 1 - 25
procedures
called by triggers, 13 - 20
external, 11 - 3
program units in PL/SQL, 10 - 2
programmatic environments
for Oracle objects, 19 - 1
Java, 19 - 19
OCI, 19 - 2
Oracle Objects for OLE, 19 - 16
Pro*C/C++, 19 - 14
property
CHARSETFORM, 11 - 24
CHARSETID, 11 - 24
INDICATOR, 11 - 23
pseudocolumns
modifying views, 13 - 8
PUBLIC user group
granting and revoking privileges to, 12 - 21
procedures and, 12 - 22
publish-subscribe
advanced queueing, 15 - 3
client notifications, 15 - 3
concepts, 15 - 4
database events, 15 - 3
examples, 15 - 6
introduction, 15 - 2
purchase order example, 16 - 3
purity level, 10 - 61

Q

queries
errors in distributed queries, 10 - 47
set memebership
optimizing, 18 - 20
unnesting, 18 - 13
VARRAY, 18 - 16

R

RAISE statement, 10 - 45
RAISE_APPLICATION_ERROR procedure, 10 - 44
remote procedures, 10 - 47
raising exceptions
triggers, 13 - 18
RAW datatype, 4 - 18
RAWTOHEX function, 4 - 25
read-only transactions, 8 - 8
REF columns, indexes on, 6 - 7
REFERENCING option, 13 - 17
referential integrity
distributed databases and, 5 - 14
one-to-many relationship, 5 - 9
one-to-one relationship, 5 - 9
privileges required to create foreign keys, 5 - 30
self-referential constraints, 13 - 44
triggers and, 13 - 41, 13 - 43, 13 - 44, 13 - 45
REFs, 18 - 9
constraints on, 18 - 10
dereferencing of, 16 - 24
implicit dereferencing of, 16 - 24
indexing, 18 - 12
object identifiers, 16 - 28
scoped, 18 - 10
storage, 18 - 10
WITH ROWID option, 18 - 11
remote dependencies, 10 - 28
signatures, 10 - 29
specifying timestamps or signatures, 10 - 35
remote exception handling, 10 - 47, 13 - 18
REMOTE_DEPENDENCIES_MODE parameter, 10 - 35
RENAME command, 3 - 48
renaming objects, 3 - 48
repeatable reads, 8 - 8, 8 - 11
resource manager events, 14 - 4
RESTRICT_REFERENCES pragma
syntax for, 10 - 66
using to control side effects, 10 - 66
restrictions
system triggers, 13 - 24
returning nested tables as, 16 - 33, 18 - 20
reusable packages, 10 - 70
REVOKE command, 12 - 18
when in effect, 12 - 22
revoking privileges and roles
on selected columns, 12 - 19
REVOKE command, 12 - 18
RNDS argument, 10 - 67
RNPS argument, 10 - 67
ROLE_SYS_PRIVS view, 12 - 4
ROLE_TAB_PRIVS view, 12 - 4
roles
ADMIN OPTION and, 12 - 17
advantages, 12 - 4
application, 12 - 3, 12 - 4, 12 - 7, 12 - 10
application security policy, 12 - 3, 12 - 7
creating, 12 - 11
default, 12 - 13
dropping, 12 - 16
enabling, 12 - 4, 12 - 14
GRANT and REVOKE commands, 12 - 15
granting, 12 - 16
managing, 12 - 10
operating system granting of, 12 - 15
privileges for creating, 12 - 12
SET ROLE command, 12 - 15
user, 12 - 4, 12 - 7, 12 - 10
user privileges and enabling, 12 - 13
when to enable, 12 - 12
WITH GRANT OPTION and, 12 - 18
ROLLBACK command, 8 - 6
rolling back transactions
to savepoints, 8 - 6
routines
autonomous, 8 - 33
external, 11 - 3
service, 11 - 35
row locking
manually locking, 8 - 17
row objects
storage, 18 - 8
row triggers
defining, 13 - 12
REFERENCING option, 13 - 17
timing, 13 - 7
UPDATE statements and, 13 - 6, 13 - 18
ROW_LOCKING parameter, 8 - 11, 8 - 19
ROWID datatype, 4 - 18
extended ROWID format, 4 - 19
migration, 4 - 21
ROWIDTOCHAR function, 4 - 25
ROWLABEL column, 4 - 23
rows
chaining across blocks, 3 - 5
format, 3 - 2
header, 3 - 2
shown in ROWIDs, 4 - 20
size, 3 - 2
violating integrity constraints, 5 - 21
ROWTYPE_MISMATCH exception, 10 - 41
RR date format, 4 - 11
RS locks
LOCK TABLE command, 8 - 13
run-time error handling, 10 - 44
RX locks
LOCK TABLE command, 8 - 13

S

S locks
LOCK TABLE command, 8 - 13
SAVEPOINT command, 8 - 6
savepoints
maximum number of, 8 - 6
rolling back to, 8 - 6
scalability
serially reusable packages, 10 - 70
schemas, 12 - 9
SCOPE FOR constraint, 16 - 30, 16 - 34
scope, autonomous, 8 - 33
security
application context, 12 - 22
fine-grained access control, 12 - 26
policy for applications, 12 - 3, 12 - 7
roles, advantages, 12 - 4
table- or view-based, 12 - 26
security policy, 12 - 1
SELECT command
read consistency, 8 - 11
SELECT ... FOR UPDATE, 8 - 17
Send Us Your Comments
boilerplate, xxi
SEQUENCE_CACHE_ENTRIES parameter, 3 - 40
sequences
accessing, 3 - 37
altering, 3 - 37
caching numbers, 3 - 36
caching sequence numbers, 3 - 40
creating, 3 - 36, 3 - 41
CURRVAL, 3 - 37, 3 - 39
dropping, 3 - 41
initialization parameters, 3 - 36
NEXTVAL, 3 - 38
parallel server, 3 - 37
privileges for creating, 3 - 37
privileges to alter, 3 - 37
privileges to drop, 3 - 42
privileges to use, 3 - 41
reducing serialization, 3 - 38
SERIALIZABLE option, 8 - 19
for ISOLATION LEVEL, 8 - 26
SERIALIZABLE parameter, 8 - 11
serializable transactions, 8 - 23
serially reusable PL/SQL packages, 10 - 70
SERIALLY_REUSABLE pragma, 10 - 71
service routine, 11 - 35
examples, 11 - 35
sessions
package state and, 10 - 15
SET ROLE command, 12 - 4, 12 - 14
when using operating system roles, 12 - 15
SET TRANSACTION command, 8 - 8
ISOLATION LEVEL clause, 8 - 26
SERIALIZABLE, 8 - 19, 8 - 26
SGA
See Also system global area
share locks (S)
LOCK TABLE command, 8 - 13
share row exclusive locks (SRX)
LOCK TABLE command, 8 - 15
side effects, 10 - 6, 10 - 61
signatures
PL/SQL library unit dependencies, 10 - 28
to manage remote dependencies, 10 - 29
SORT_AREA_SIZE parameter
index creation and, 6 - 2
SQL statements
execution, 8 - 2
in trigger bodies, 13 - 15, 13 - 20
not allowed in triggers, 13 - 20
privileges required for, 12 - 11
when constraint checking occurs, 5 - 18
SQL*Loader
indexes and, 6 - 2
SQL*Module
applications, 10 - 4
SQL*Plus
anonymous blocks, 10 - 4
compile-time errors, 10 - 42
invoking stored procedures, 10 - 50
loading a procedure, 10 - 10
SET SERVEROUTPUT ON command, 10 - 3
SHOW ERRORS command, 10 - 42
SQLJSee Oracle SQLJ
SQLStmt object, 1 - 16
SRX locks
LOCK Table command, 8 - 15
standards
ANSI, 8 - 19
state
session, of package objects, 10 - 15
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, 3 - 9
PCTUSED, 3 - 9
STORE AS clause, 16 - 32
stored functions, 10 - 5
creating, 10 - 9
stored procedures, 10 - 5
argument values, 10 - 53
creating, 10 - 9
distributed query creation, 10 - 47
exceptions, 10 - 44, 10 - 45
invoking, 10 - 50
names of, 10 - 5
overloading names of, 10 - 12
parameter
default values, 10 - 9
privileges, 10 - 53
remote, 10 - 54
remote objects and, 10 - 55
storing, 10 - 9
synonyms, 10 - 56
using privileges granted to PUBLIC, 12 - 22
synonyms
creating, 3 - 43
dropped tables and, 3 - 10
dropping, 3 - 44
privileges, 3 - 43
stored procedures and packages, 10 - 56
using, 3 - 43
SYSDATE function, 4 - 9
system events, 14 - 1
attributes, 14 - 2
client events, 14 - 5
resource manager events, 14 - 4
tracking, 13 - 52, 14 - 1
system global area
holds sequence number cache, 3 - 40
system-specific Oracle documentation, 13 - 4
PL/SQL wrapper, 10 - 27

T

table- or view-based security, 12 - 26
TABLE syntax, 18 - 13
tables
altering, 3 - 9
constraining, 13 - 22
creating, 3 - 3, 3 - 4
designing, 3 - 3
dropping, 3 - 10
guidelines, 3 - 2, 3 - 4
in PL/SQL, 10 - 9
increasing column length, 3 - 9
key-preserved, 3 - 29
location, 3 - 4
mutating, 13 - 22
privileges for creation, 3 - 8
privileges for dropping, 3 - 11
privileges to alter, 3 - 10
schema of clustered, 6 - 16
specifying PCTFREE for, 3 - 5
specifying PCTUSED for, 3 - 6
specifying tablespace, 3 - 4
truncating, 3 - 10
tables, object, See object tables
temporary segments
index creation and, 6 - 2
third generation language, 10 - 2
timestamps
PL/SQL library unit dependencies, 10 - 28
TO_CHAR function, 4 - 25
CC date format, 4 - 12
converting Trusted Oracle labels, 4 - 28
RR date format, 4 - 11
TO_DATE function, 4 - 8, 4 - 25
RR date format, 4 - 11
TO_LABEL function
converting Trusted Oracle labels, 4 - 28
TO_NUMBER function, 4 - 25
transactions
autonomous, 8 - 33
manual locking, 8 - 11
read-only, 8 - 8
serializable, 8 - 23
SET TRANSACTION command, 8 - 8
triggers
about, 10 - 26
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 - 5
column list in UPDATE, 13 - 6, 13 - 18
compiled, 13 - 27
conditional predicates, 13 - 14, 13 - 17
creating, 13 - 3, 13 - 20, 13 - 25
data access restrictions, 13 - 48
debugging, 13 - 29
designing, 13 - 2
disabling, 13 - 30
distributed query creation, 10 - 47
dropped tables and, 3 - 10
enabling, 13 - 30
error conditions and exceptions, 13 - 18
events, 13 - 5
examples, 13 - 34, 13 - 36, 13 - 38, 13 - 41, 13 - 47, 13 - 48, 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
migration issues, 13 - 28
modifying, 13 - 29
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 - 25
to drop, 13 - 29
procedures and, 13 - 20
recompiling, 13 - 28
REFERENCING option, 13 - 17
referential integrity and, 13 - 41, 13 - 43, 13 - 44, 13 - 45
remote dependencies and, 13 - 20
remote exceptions, 13 - 18
resource manager events, 14 - 4
restrictions, 13 - 13, 13 - 20
row, 13 - 12
row evaluation order, 13 - 21
scan order, 13 - 21
stored, 13 - 27
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 - 25
WHEN clause, 13 - 13
TRUNC function, 4 - 9
TRUNCATE TABLE command, 3 - 10
TRUST keyword, 10 - 68
Trusted Oracle Server
converting labels, 4 - 28
MLSLABEL datatype, 4 - 23
tuning
using LONGs, 4 - 17
type evolution, 18 - 40

U

unhandled exceptions, 10 - 46
UNIQUE key constraints
combining with NOT NULL constraint, 5 - 4
composite keys and nulls, 5 - 6
disabling, 5 - 22
enabling, 5 - 22
PRIMARY KEY constraint vs., 5 - 6
when to use, 5 - 6
unnesting queries, 18 - 13
UPDATE command
column values and triggers, 13 - 16
data consistency, 8 - 11
triggers and, 13 - 6, 13 - 18
triggers for referential integrity, 13 - 43, 13 - 44
updating tables
with parent keys, 5 - 10, 5 - 11
UPPER function, 6 - 8
USER function, 5 - 4
user locks
requesting, 8 - 21
USER_ERRORS view
debugging stored procedures, 10 - 43
USER_SOURCE view, 10 - 43
user-defined datatypes, See object-relational model
user-defined errors, 10 - 44, 10 - 45
usernames
as reported in a trigger, 13 - 25
schemas and, 12 - 9
users
dropped roles and, 12 - 16
enabling roles for, 12 - 4
PUBLIC group, 12 - 21
restricting application roles, 12 - 7
UTLEXCPT.SQL file, 5 - 25
UTLLOCKT.SQL script, 8 - 22

V

VARCHAR datatype, 4 - 5
VARCHAR2 datatype, 4 - 2, 4 - 5
column length, 4 - 6
when to use, 4 - 5
VARRAY, 18 - 13, 18 - 15
accessing, 18 - 15
querying, 18 - 16
See Also arrays
storage, 18 - 15
updating, 18 - 16
vs nested tables, 16 - 17, 16 - 20
views
containing expressions, 13 - 8
creating, 3 - 22
creating with errors, 3 - 23
dropped tables and, 3 - 10
dropping, 3 - 27
FOR UPDATE clause and, 3 - 22
inherently modifiable, 13 - 8
invalid, 3 - 27
join views, 3 - 28
modifiable, 13 - 8
ORDER BY clause and, 3 - 22
privileges, 3 - 24
pseudocolumns, 13 - 8
replacing, 3 - 24
restrictions, 3 - 26
using, 3 - 25
when to use, 3 - 22
WITH CHECK OPTION, 3 - 22
violating integrity constraints, 5 - 21

W

WHEN clause, 13 - 13
cannot contain PL/SQL expressions, 13 - 13
correlation names, 13 - 16
examples, 13 - 3, 13 - 12, 13 - 32, 13 - 41
EXCEPTION examples, 13 - 18, 13 - 41, 13 - 47, 13 - 48
WITH CONTEXT clause, 11 - 28
WITH GRANT OPTION, 12 - 18
WNDS argument, 10 - 67
WNPS argument, 10 - 67
wrapper to hide PL/SQL code, 10 - 27

X

X locks
LOCK TABLE command, 8 - 16

Y

year 2000, 4 - 10

Prev
Oracle
Copyright © 1999 Oracle Corporation.
All Rights Reserved.

Library
Product
Contents