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, 2728
- 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, 28182
- 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 developers 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 triggers 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 triggers associated table, 244
- 7.14 Declaring a triggers 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, 7072
- 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