monitoring locks, 449
rows, 445
tables, 443-444
exclusive mode, 444
row exclusive mode,
444
row share mode, 444
share lock mode, 444
share row exclusive
mode, 444
LOG function, 158
logarithms, returning, 158
logic errors, 378-379
nonterminating loops, 380
order of operations, 379
logical operators, 76-78
AND, 77
nulls, 89
syntax, 77
NOT, 76-77
nulls, 89
syntax, 76
OR, 77-78
expressions using, 78
nulls, 89
syntax, 77
login_denied exception, 208
logs, maintaining with trig-
gers, 304-306
LONG datatype, 46
syntax, 46

nesting, 148
order of execution, 125
REPEAT...UNTIL,
144-146
example, 145
simulating, 145
reversing, 122-123
listing, 122
selecting, 146
spacing, 148
style tips, 148-149
FOR loop confusing
style, 148
FOR loop suggested
style, 148
terminating, 121, 138
WHILE, 134-135,
137-138, 146
examples, 134-135,
137-138
EXIT statement, 139
EXIT WHEN state-
ment, 140
in functions, 137-138
stepping through, 136
syntax, 134
LOWER function, 157
LPAD function, 157
LTRIM function, 157
LUB function, 160

publishing, calling Java
from PL/SQL, 571-573
SELF parameter, 361-362
listing, 362
specifying, 362
678 locking
30 SQL Index 11/30/99 1:27 PM Page 678
SSThresh
calling from PL/SQL,
567-568
calling from
SQL*Plus, 566-567
publishing, 565-566
MIN function, 160
minus sign (-)
negation operator, 65, 67
subtraction operator, 65
MLSLABEL datatype
defining output variables,
462
VARIABLE_VALUE pro-
cedure, 469
MOD function, 158
MODE statement, 103
IN, 103
IN OUT, 103
OUT, 103
monitoring locks, 449
MONTHS_BETWEEN
function, 159, 182

nesting
blocks, 54
listing, 54-55
FOR loops, 121
listing, 122
functions, 55-56
IF statements, 115-117
listing, 116
syntax, 115
vs IF...ELSIF state-
ment, 119
loops, 148
order of execution, 125
objects, 350
dot notation, 356-357
visibility, 358
procedures, 55-56
networks, performance
(SQL limitations), 8
NEW_LINE procedure, 497
NEW_TIME function, 159,
183-184
syntax, 183
NEXTVAL pseudocolumns,
241
NEXT_DATE procedure,
521
NEXT_DAY function, 159,
179-181
syntax, 179

nulls
AND operator, 89
checking for, 90
IF statement, 90
in expressions, 86-89, 91
listing, 88
specifying alternate
values, 91
three-valued logic, 87
treating as specific val-
ues, 91
nulls 679
30 SQL Index 11/30/99 1:27 PM Page 679
NOT operator, 89
OR operator, 89
NUMBER datatype, 38
defining output variables,
461
syntax, 39-40
VARIABLE_VALUE
procedure, 468
NUMBER datatype:
subtypes, 40-41
number functions, 158
ABS, 158
ACOS, 158
ASIN, 158
ATAN, 158
ATAN2, 158
CEIL, 158

cosines, returning, 158
determining negative/
positive/zero, 158
dividing (returning
remainders), 158
exponentiation, 158
factorials, 150
FACTORIAL function,
150
hyperbolic cosines, return-
ing, 158
hyperbolic sines, return-
ing, 158
hyperbolic tangents,
returning, 158
logarithms, 158
raising to powers, 158
rounding, 158
sines, returning, 158
square roots, 158
tangents, returning, 158
truncating, 158
NVL function, 91, 160
O
object identifiers, 349, 358
Object Navigator
(Procedure Builder), 24
object tables, 337, 349,
351-356
creating, 350

nesting, 350
dot notation, 356-357
visibility, 358
Oracle limitations, 367
overloading functions, 363
package (private versus
public), 205
referencing, 358-359, 361
DREF operator, 359,
361
object identifiers, 358
REF operator, 359, 361
retrieving
from columns, 347-349
from object tables,
352-353
storing
as columns, 345-347
object tables, 349,
351-356
updating
from columns, 347-349
from object tables,
352-356
680 nulls
30 SQL Index 11/30/99 1:27 PM Page 680
OOP (object-oriented pro-
gramming), 335-336
advantages, 336
attributes, 336

binary, 65
comparison, 68-70, 72-74
BETWEEN, 68, 72
dates, 82-83
equality (=), 68
IN, 68, 72-73
inequality (!), 68
inequality (=), 68
IS NULL, 68, 74, 90
less than (<), 68
less than/equal to (<=),
68
LIKE, 68, 70-71
strings, 79-81
DREF, 359, 361
listing, 360
logical, 76-78
AND, 77
NOT, 76-77
OR, 77-78
order of execution (logic
errors), 379
precedence, 84-85
parentheses, 85
REF, 359, 361
listing, 359
relational (sample expres-
sions), 69
string, 78-79
concatenation (||),

procedure, 17
defining, 461-463
CHAR datatype, 462
DATE datatype, 462
MLSLABEL datatype,
462
NUMBER datatype,
461
RAW datatype, 462
ROWID datatype, 462
VARCHAR2 datatype,
462
overloaded functions, 178
overloading, 363
advantages, 199-200
body, 199, 201-202
creating, 202-203
cursors, 256-257
return clause, 257
DBMS LOCK, 446-448
ALLOCATE_UNIQUE
function, 446
CONVERT function,
447-448
RELEASE function,
448-449
REQUEST function,
446-447
SLEEP function, 449
DBMS OUTPUT, 25, 484

463-467
DEBUG, 385-387
creating components,
386-387
DEBUG.ERASE pro-
cedure, 388
DEBUG.OUT proce-
dure, 387
declaring, 240-241
dependency, 206
guidelines, 200
invoking, 203
objects, private versus
public, 205
packages, 199, 201-206
procedures, 197
recompiling, 204-205
specification, 199-200
variable declarations,
240
state, 205
subprograms, 199
calling, 203-204
TEXT IO, 25
UTL_FILE, 491, 496-497
clearing buffer, 500
P
PACK_MESSAGE proce-
dure, 545-547
PACK_MESSAGE_RAW

NLS (jobs), 515
Passing to functions, 111
procedures, 195
parameter definitions,
195
SELF, 361-362
listing, 362
specifying, 362
triggers, 298-299,
312-313, 318-319
condition, 299, 313,
318
correlation names,
299-300, 318
declarations, 299, 313,
318
instead-of, 318
pl/sql code, 299, 313,
318
table name, 299, 318
verb list, 298, 318
parentheses ( ), operator
precedence, 85
PARSE procedure, 454
parsing statements, 454-455
queries, 461
payload, AQ (Advanced
Queuing), 587
payload types, defining
(message queuing),

555
retrieving message from
buffer, 548-549
rowids
decoding, 554
sending, 554
sending data to, 547
size, 545
versus alerts, 555-557
writers, 543
PL/SQL, 10
block structure, 13
compiling and execut-
ing blocks, 15-17
syntax, 14-15
blocks, alternatives to
retyping, 18-20
calling from Java (SQLJ),
578-579
calling procedures and
functions, 580, 582
executing blocks,
578-579
calling Java from, 569
datatype compatibility,
574-575
Employee class,
569-570
methods, publishing,
571-573

predefined exceptions
invalid_cursor, 232
no_data_found, 232
too_many_rows, 232
value_error, 232
when_others, 232
predefined Oracle errors,
207-209
cursor_already_open, 208
dup_val_on_index, 208
invalid_cursor, 208
invalid_number, 208
login_denied, 208
not_logged_on, 208
no_data_found, 208
others, 208
program_error, 208
storage_error, 208
timeout_on_resource, 208
too_many_rows, 208
transaction_backed_out,
208
value_error, 208
zero_divide, 208
private objects (packages),
205
private pipes, 544
creating, 546, 550-551
privileges, database (exer-
cise completion require-