book-index

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

PRAGMA s, 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