Table of Contents


Index

4GL, 41

—A—

Ada, 58
Alerts
and pipes, 296
ALTER USER privilege, 311
and changing passwords, 310
Arrays
vs. cursors, 52
Associated table, 242, 244

—B—

Base table, 250. See also Associated table.
BEGIN statement, 51
Blank lines
in SQL*Plus, 262
Blocks
and sub-blocks, 50
definition, 50
Break
set of actions, 92
break command
types of events, 92
Broken job, 295
definition, 291

—C—

C, 55, 58, 70
Cartesian products. See Joins.
Cascading delete
definition, 236
COBOL, 41
Code, standardizing
PL/SQL, 27
SQL, 27
Columns. See also Tables.
definition, 37
Comments
single-line vs. multi-line, 145
COMMIT statement, 42
Compile error
definition, 257
Compute command
types of operations, 97
Constraints
definition, 28
types, 29
Continue statement
lack of in PL/SQL, 186
Conversions
explicit, 44, 53
implicit, 44
reasons for using explicit, 44
Cursors
definition, 51
explicit, 52
implicit, 53
problems using, 331
vs. arrays, 52

—D—

Data definition language. See DDL.
Data dictionary, 240
Data manipulation language. See DML.
Database
basics, 27–28
Database triggers, 20, 65
basic structure, 242
common uses, 241
and DBMS_Alert, 281
definition, 223
row-level, 19
statement-level, 18
types, 19
typical uses, 18
use for row-level, 241
Datatypes
composite, 55
most common, 44
scalar, 55
DBMS_Alert package, 281
procedures, 282
vs. DMBS_Pipe package, 284
DBMS_DDL package, 286
procedures, 286
DBMS_Describe package
prodedure, 288
DBMS_Job package, 290
procedures, 291
scheduling a job, 294
DBMS_Output package, 265, 295
DBMS_Pipe package, 295
procedures, 296
vs. DMBS_Alert package, 284
DBMS_SQL package, 302
and changing passwords, 310
procedures, 304
DBMS_Utility package, 312
especially useful function, 312
DDL, 41, 42
DECLARE statement, 51
Delete, cascading
definition, 236
DELETE statement, 42
Dependencies
definition, 121, 163
DML, 42, 48, 49, 60, 67
Documentation
three basic aspects of the procedure, 141
trigger vs. function, 247
trigger vs. package, 247
trigger vs. procedure, 247

—E—

Elements
referencing, 56
END statement, 51
Error
mutating table, 233
Error messages, 137
and packages, 211
assigned numbers, 138
Error, compile
definition, 257
Errors, 269. See also Exceptions.
in Oracle, 58
useful functions in PL/SQL, 275
Errors, runtime
better method of debugging, 269
definition, 264
locating, 268
most effective method of isolating problems, 269
Exception handler
importance of using carefully, 272
order of steps, 275
Exceptions
and the RAISE statement, 61
handling, 58
importance of creating user-defined, 138
origin of term, 51
reasons for occurring, 137
user-defined, 61
EXCEPTION statement, 51
Exit and quit commands
values that can be returned, 103
EXIT statement, 64
Extra fetch
avoiding, 330

—F—

Fetch, extra
avoiding, 330
Fortran, 41
Forward declaration, 119
Fourth-generation programming language. See 4GL.
Full-table scan
and use of indexes, 327
avoiding, 327
definition, 326
Function declaration
portions, 170
Function documentation
vs. trigger documentation, 247
Functions
and procedures, 14
calling, 167
creating, 160, 183
definition, 157
documenting, 179
dropping, 161
most common uses, 14
packaged vs. standalone, 204
poor programming style for defining parameters, 165
purity levels, 61
referencing from stored objects, 163
structure of stored, 170
tests, 190

—H—

Header, 141

—I—

Identifier names
and documenting code, 145
Implementation
processing pipe-based, 296
processing using signals, 281–82
Indexes
ways to create, 33
INSERT statement, 42
Instructions
PRAGMAs, 58

—J—

Joins
Cartesian products, 44
definition, 44
outer, 45
simple, 45

—L—

Languages. See also 4GL, C, COBOL, Fortran, Pascal, PL/SQL, SQL.
Ada, 51
data definition, 41
data manipulation, 41
Legacy system, 301
Line numbers
incorrect, 263
Lines, blank
in SQL*Plus, 262
Listings
1.1 A generic cold backup script for an Oracle database, 4
1.2 Logic for a hot backup of an Oracle database, 5
1.3 A sample script to create a new user in an Oracle database, 7
1.4 The HTMLCODE.SQL script, 7
1.5 A script to recompile stored objects that are marked as invalid, 9
1.6 Generated code to recompile invalid PL/SQL objects, 10
1.7 A simple script that allows unit testing for a function, 10
1.8 A simple script to update area codes inside phone numbers, 12
1.9 A typical stored procedure, 14
1.10 A typical stored function, 15
1.11 Use of the Calculate_GPA function in a SQL statement, 16
1.12 A typical package spec, 16
1.13 A typical package body, 17
1.14 A typical database trigger, 20
1.15 An UPDATE trigger using a WHEN clause, 21
2.1 A sample table creation script using constraints, 28
2.2 A revised table creation script using constraints, 30
2.3 Finding the indexes for a table, 34
2.4 Finding the existing roles in your database, 35
2.5 A simple DDL statement, 41
2.6 A simple DML statement, 42
2.7 A query that causes a Cartesian product, 44
2.8 A query using a simple join, 45
2.9 A query using an outer join, 46
2.10 A sample PL/SQL block, 50
2.11 A sample PL/SQL block with a sub-block, 50
2.12 The declaration of an explicit cursor, 52
2.13 A CURSOR FOR loop, 52
2.14 A PL/SQL record declaration, 54
2.15 A PL/SQL table declaration, 55
2.16 A user-defined exception, 61
2.17 Using an EXIT statement with multiple loops, 64
2.18 A typical stored procedure, 67
2.19 A typical stored function, 67
2.20 A sample package spec, 69
2.21 Using a stored procedure to simulate a C continue statement, 71
3.1 A generic cold backup script for an Oracle database, 78
3.2 The DROP_ALL.SQL script, 79
3.3 A script that grants privileges to roles, 80
3.4 A script to create an application developer’s account, 81
3.5 An SQL report on code stored in the data dictionary, 82
3.6 A unit test for the Calculate_GPA() procedure, 84
3.7 A documented header for a script, 89
4.1 Embedded SQL within a stored procedure, 113
4.2 Creating a stored procedure, 116
4.3 Declaring a local procedure within a procedure, 118
4.4 Using a forward declaration for a local procedure, 119
4.5 The structure of the ALL_DEPENDENCIES view, 121
4.6 Defining parameters for a stored procedure, 122
4.7 Checking the values of para- meters, 122
4.8 Defining a parameter using %TYPE, 123
4.9 Defining a parameter using %ROWTYPE, 123
4.10 Default values for parameters, 124
4.11 An anonymous PL/SQL block that calls a procedure, 125
4.12 Calling a stored procedure from another stored procedure, 126
4.13 Calling a procedure using named notation, 127
4.14 Calling a stored procedure using positional notation, 128
4.15 Mixing named and positional notation, 128
4.16 The procedure declarations portion of a procedure, 130
4.17 The variable declarations portion of a procedure, 131
4.18 The executable declarations portion of a procedure, 133
4.19 The body of a procedure, 134
4.20 The exception handler of a procedure, 136
4.21 Using the OTHERS exception handler, 138
4.22 Using SQLCODE() and SQLERRM() in an OTHERS exception handler, 139
4.23 Using the RAISE statement in your code, 139
4.24 Using the Raise_Application_Error() procedure, 140
4.25 The Calculate_GPA() procedure with a header, 141
4.26 Pseudocode for the Calculate_GPA() procedure, 143
4.27 Pseudocode for the Annual_Review() procedure, 147
4.28 The code for the Annual_Review() procedure, 148
4.29 Part of the unit testing scripts for the Annual_Review() procedure, 153
5.1 A PL/SQL function that utilizes a DML statement, 158
5.2 A procedure calling the Raise_Salary() function, 158
5.3 A testing script for the Raise_Salary() function, 159
5.4 Creating a function, 160
5.5 Declaring a local function within a procedure, 161
5.6 The structure of the ALL_DEPENDENCIES view, 163
5.7 Use of the RETURN statement in a function, 165
5.8 A return value of a user-defined datatype, 165
5.9 Using %TYPE definitions for parameters and return values, 167
5.10 Using %ROWTYPE definitions for parameters and return values, 167
5.11 Calling a function within a DML statement, 168
5.12 The Raise_Salary() function called in Listing 5.11, 168
5.13 An anonymous PL/SQL block that calls a function, 169
5.14 A stored function calling another stored function, 169
5.15 The function declaration, 171
5.16 The variable declaration section of a function, 172
5.17 The executable declarations of a function, 174
5.18 The body of a function, 176
5.19 The exception handling portion of a function, 178
5.20 The Parse_String() function with a header, 180
5.21 Pseudocode for the Parse_String() function, 181
5.22 Pseudocode for the Assign_Instructor() function, 185
5.23 The code for the new Assign_Instructor() function, 188
5.24 A test script for the Assign_Instructor() function, 192
6.1 The definition of global constructs in a package spec, 200
6.2 Referencing an object within a package, 201
6.3 Defining a procedure within a package spec, 202
6.4 Defining a function within a package spec, 204
6.5 Defining the purity level of a packaged function, 206
6.6 A package spec containing an overloaded function, 207
6.7 Creating a procedure inside a package body, 209
6.8 Creating a function inside a package body, 210
6.9 Initializing packaged variables, 211
6.10 Logic for the function Next_Word(), 214
6.11 Revised pseudocode for the Next_Word() function, 215
6.12 Logic for the Build_Error() procedure, 215
6.13 Logic for the Next_String() function, 216
6.14 The package spec for the System_Errors package, 216
7.1 Using a DML statement inside a database trigger, 223
7.2 Using a WHEN clause, 226
7.3 Using boolean functions in a database trigger, 230
7.4 A sample CREATE TRIGGER command, 231
7.5 A trigger that causes a mutating table error, 234
7.6 A trigger that can read from its associated table, 235
7.7 Referencing a foreign key column in another table, 236
7.8 Referencing a trigger’s associated table using an after statement trigger, 237
7.9 Implementing a key value lookup scheme to avoid mutating table errors, 238
7.10 The structure of the ALL_TRIGGERS view, 240
7.11 A trigger declaration, 242
7.12 A triggering event, 243
7.13 Defining a trigger’s associated table, 244
7.14 Declaring a trigger’s level, 245
7.15 Using the WHEN clause, 246
7.16 A trigger body, 246
7.17 A sample header for a trigger, 248
7.18 Pseudocode for the ENROLLED_CLASSES_ARIU trigger, 250
7.19 Code for the ENROLLED_CLASSES_ARIU trigger, 251
8.1 A sample stored procedure with compile errors, 258
8.2 The revised Calculate_Student_Grades() procedure, 260
8.3 Pulling error information from the ALL_ERRORS view, 262
8.4 The Calculate_Student_Grades() procedure, 263
8.5 An excerpt of debugging code from the Build_SUID_Matrix package, 265
8.6 Code using a tracepoint variable, 269
8.7 Misusing the OTHERS exception handler, 272
8.8 Using the OTHERS exception handler to log an error, 273
8.9 Calling the SQLCODE() function in an exception handler, 276
9.1 Using a trigger to send a signal, 285
9.2 Using a trigger to send a message over a pipe, 301
9.3 The Change_Password() function, 310
9.4 A procedure that uses the UTL_File package, 316
10.1 Using the EXPLAIN PLAN SQL statement, 320
10.2 Getting an EXPLAIN PLAN from the PLAN_TABLE table, 321
10.3 A SELECT statement inside the body of a PL/SQL block, 330
10.4 Implementing SELECT statement functionality by using a cursor, 330
10.5 Using IF-THEN logic to flag errors, 331
10.6 Using exception handlers to improve performance, 333
Local functions
and accessibility, 163
Local procedures
declaring within PL/SQL code, 118
Locks, 47
Loops
CURSOR FOR, 52
FOR, 63
WHILE, 64
LOOP statement, 63

—M—

Many-to-many relationships, 31, 32
Mutating table error, 233
and foreign key, 236
cascading delete, 237

—N—

Notation
named, 127
named vs. positional, 129
positional, 128
NULL statement, 63

—O—

Objects
private, 16, 17
One-to-many relationships, 31, 32
One-to-one relationships, 31
Operators, 47
Optimizer
rule-based vs. cost-based, 329
Optimizer, rule-based
primary conditions, 329
Oracle error ORA-00942, 276
Oracle exceptions
and confusing the debugging process, 140
Outer joins, 45
Overloaded object
definition, 207

—P—

Package body
contents, 208
Package documentation
vs. trigger documentation, 247
Package specification. See Package specs.
Package specs, 16, 17
how to define, 202
in PL/SQL, 69
primary purpose, 212
types of definitions, 199
Packages
contents, 16
defining purity levels, 205
definition, 199
definition of body, 16
definition of specification, 16
in PL/SQL, 69
testing, 220
Packages, special
DBMS_Alert, 301
DBMS_DDL, 286
DBMS_Describe, 288
DBMS_Job, 290
DBMS_Output, 265, 295
DBMS_Pipe, 295
DBMS_SQL, 302
DBMS_Utility, 312
Parameters
constraining, 122
definition, 122
for functions, 164
references to, 105
types, 67
types for stored procedures, 123
Pascal, 55
Passwords
changing, 310
p-code, 159, 228
Performance problems
most common causes, 319
pipename parameter
character length, 297
Pipes
and alerts, 296
private, 297
public, 297
unique names, 301
Pipes, unnecessary
importance of emptying, 298
PL/SQL
deficiencies, 70–72
select features, 49
vs. SQL for client/server development, 74
PRAGMA. See also Instructions.
definition, 205
Private pipe
and security, 297
Private synonyms
definition, 37
Privileges
definition, 35
system level, 35
table level, 35
Pro*C program, 286, 302
Problems, performance
most common causes, 319
Procedural Logic/Structured Query Language. See PL/SQL.
Procedure Builder, 232, 265
Procedure declaration
portions, 129
Procedure documentation
vs. trigger documentation, 247
Procedures
and functions, 14
most useful place to document, 141
named method, 68
package vs. standalone, 203
positional method, 68
reasons for writing, 13
Prologue. See Header.
Public pipe, 297
Public synonyms
definition, 37

—R—

RAISE statement
and exceptions, 61
Records, 53
Referential integrity
definition, 31
types, 31
Relationships
many-to-many, 31, 32
one-to-many, 31, 32
one-to-one, 31
Roles, 6
definition, 35
ROLLBACK statement, 42
Rows. See also Tables.
definition, 38
Rule-based optimizer
primary conditions, 329
tuning tips, 329
Runtime errors
better method of debugging, 269
definition, 264
locating, 268
most effective way of isolating problems, 269

—S—

Scan, full table
definition, 326
avoiding, 327
and use of indexes, 327
Schemas
definition, 36
Script development
dynamic code generation, 7
Scripting
creating unit testing pieces of code, 10
Scripts
HTMLCODE.SQL, 7
importance of storing in version control, 90
important aspects, 90
tasks performed, 78
Security features. See Privileges, Roles.
SELECT statement
performance problem, 330
Sequences
definition, 36
SGA, 28, 39, 40, 41, 73
Show command
and arguments, 99
Signals
and DBMS_Alert, 281
overwriting previous signals, 284
Simple joins, 45
Snapshots
benefits of using, 37
definition, 36
Special packages
DBMS_Alert, 301
DBMS_DDL, 286
DBMS_Describe, 288
DBMS_Job, 290
DBMS_Output, 265, 295
DBMS_Pipe, 295
DBMS_SQL, 302
DBMS_Utility, 312
SQL
vs. PL/SQL for client/server development, 74
SQL scripts
and generating other SQL scripts, 79
SQLCODE(), 275, 276
SQLERRM(), 275, 277
Standardizing code
PL/SQL, 27
SQL, 27
Statements
BEGIN, 51
COMMIT, 42
DECLARE, 51
DELETE, 42
END, 51
EXCEPTION, 51
EXIT, 64
INSERT, 42
LOOP, 63
NULL, 62
RAISE, 61
ROLLBACK, 42
SELECT, 42
UPDATE, 42
Stored function
vs. stored procedure, 67
Stored objects
and DML statements, 159
Stored PL/SQL objects
common traits, 13
Stored procedures
components, 129
definition, 113
reasons for providing performance improvements over code, 114
vs. stored function, 67
Structured Query Language. See SQL.
Subqueries
definition, 49
Substitution variable
definition, 105
Synonyms
definition of private, 37
definition of public, 37
System automation
backups, 4
creating new users, 6
scripting, 4
testing code, 12
System Global Area. See SGA.

—T—

Tables
aliases, 46
associated, 242, 244
base, 250
definition, 37
definition of columns, 37
definition of rows, 37
in PL/SQL, 55
Tasks
conditions indicating automation, 88
conditions indicating scripting, 88
deciding whether to perform with a script, 87
Tests
examples of positive and negative, 152
for functions, 190
TKPROF, 322
Trace file
creating, 322
Tracepoint variable
advantages of using, 271
and debugging runtime errors, 269
Trigger
pseudocode, 250
requirements, 249
Trigger body, 242, 246
Trigger declaration, 242
Trigger documentation
vs. function documentation, 247
vs. package documentation, 247
vs. procedure documentation, 247
Trigger header
questions to answer, 247
Trigger level, 242, 245
Triggering event, 242, 243
Triggers
and loading data more quickly, 233
disabling more than one, 233
single vs. several, 226
testing UPDATE functionality, 253
vs. other stored PL/SQL objects, 228
Triggers, database
and DBMS_Alert, 281
basic structure, 242
common uses, 241
defining to fire, 225
definition, 223
restrictions, 224
row-level, 229
tasks for testing, 252
ttitle command
actions, 102
Tuning tips
when using rule-based optimizer, 329

—U—

Unit test
advantages, 87
Unit testing script
advantages over typical ad hoc testing, 82
tasks of well-written, 84
UPDATE statement, 42
UTL_File package, 107
procedures, 312
steps to do file I/O, 312
using to access a file, 316

—V—

Variables
declaring dynamically, 57
initializing, 57
Views
definition, 38
performance implications, 38

—W—

WHEN clause, 242, 245
WHERE clause tips, 328


Table of Contents