Oracle PL SQL Programming

Table of contents :
Table of Contents……Page 2
BookmarkTitle:……Page 25
A.1 Installing the Guide……Page 26
A.2 Using the Guide……Page 27
BookmarkTitle:……Page 29
B. Calling Stored Procedures from PL/SQL Version 1.1……Page 30
B.1 Using Stubs to Talk to Server-Side PL/SQL……Page 31
B.2.1 No Server-Side PL/SQL Datatypes……Page 33
B.2.2 No Direct Stored Package Variable References……Page 34
B.2.4 No Default Parameter Values……Page 36
BookmarkTitle:……Page 38
C. Built-In Packages……Page 39
C.1 Using the Built-in Packages……Page 40
C.2.5 The SIGNAL procedure……Page 42
C.2.7 The WAITONE procedure……Page 43
C.3.1 DBMS_AQ (PL/SQL 8 Only)……Page 44
C.3.2 DBMS_AQADM (PL/SQL 8 Only)……Page 45
C.4.2 The ANALYZE_OBJECT procedure……Page 48
C.5.4 The ISUBMIT procedure……Page 49
C.5.10 The WHAT procedure……Page 50
C.6.2 The COMPARE function……Page 52
C.6.8 The FILEGETNAME procedure……Page 53
C.6.13 The READ procedure……Page 54
C.6.16 The WRITE procedure……Page 55
C.7.2 The CONVERT function……Page 57
C.7.5 The SLEEP procedure……Page 58
C.8.1 The SEND procedure……Page 60
C.9.5 The NEW_LINE procedure……Page 61
C.9.7 The PUT_LINE procedure……Page 62
C.10.2 The NEXT_ITEM_TYPE function……Page 63
C.10.7 The RESET_BUFFER procedure……Page 64
C.10.10 The UNPACK_MESSAGE procedure……Page 65
C.11.4 The ROWID_OBJECT function……Page 66
C.11.11 The ROWID_VERIFY function……Page 67
C.12.6 The SET_NLS procedure……Page 69
C.12.9 The UNIQUE_SESSION_ID function……Page 70
C.13.4 The REFRESH procedure……Page 71
C.13.7 The WRAP_UP procedure……Page 72
C.14.2 The BIND_VARIABLE procedure……Page 73
C.14.4 The COLUMN_VALUE procedure……Page 74
C.14.6 The EXECUTE function……Page 75
C.14.13 The LAST_SQL_FUNCTION_CODE function……Page 76
C.14.16 The VARIABLE_VALUE procedure……Page 77
C.15.3 The ADVISE_ROLLBACK procedure……Page 78
C.15.8 The READ_WRITE procedure……Page 79
C.15.14 The BEGIN_DISCRETE_TRANSACTION procedure……Page 80
C.15.18 The STEP_ID function……Page 81
C.16.5 The FORMAT_ERROR_STACK function……Page 83
C.16.9 The NAME_TOKENIZE procedure……Page 84
C.16.11 The TABLE_TO_COMMA procedure……Page 85
C.17.1 Setting Up UTL_FILE……Page 86
BookmarkTitle:……Page 89
1.1 What Is PL/SQL?……Page 90
1.2 The Concept of Programming in Oracle Applications……Page 92
1.3.1 Improved Application Portability with PL/SQL……Page 94
1.3.2 Improved Execution Authority and Transaction Integrity with PL/SQL……Page 95
1.4.1 Working with Multiple Versions of PL/SQL……Page 96
1.4.3 PL/SQL Version 2.0……Page 97
1.4.4 PL/SQL Release 2.1……Page 104
1.4.5 PL/SQL Release 2.2……Page 106
1.4.6 PL/SQL Release 2.3……Page 107
1.4.7 PL/SQL Version 8.0……Page 108
1.4.8 PL/SQL Release 1.1……Page 110
1.5.2 Get Ready to Establish New Habits……Page 112
1.5.3 Assume that PL/SQL Has What You Need……Page 113
1.5.4 Share Your Ideas……Page 114
1.6.3 Built-in packages……Page 115
1.6.6 Module overloading……Page 116
1.6.8 Packages……Page 117
1.7.1 Write as Little Code as Possible……Page 119
1.7.2 Synchronize Program and Data Structures……Page 120
1.7.3 Center All Development Around Packages……Page 121
1.7.5 Structured Code and Other Best Practices……Page 122
BookmarkTitle:……Page 125
2.1 The PL/SQL Character Set……Page 126
2.2 Identifiers……Page 128
2.2.1 Reserved Words……Page 129
2.2.2 Whitespace and Keywords……Page 130
2.3.1 Embedding Single Quotes Inside a String……Page 131
2.3.3 Boolean Literals……Page 132
2.4 The Semicolon Delimiter……Page 134
2.5.2 Multiline Comment Syntax……Page 135
2.6 The PRAGMA Keyword……Page 137
2.7.1 Sections of the PL/SQL Block……Page 138
2.7.3 Nested Blocks……Page 139
BookmarkTitle:……Page 141
3.1 Fundamentals of Effective Layout……Page 142
3.1.1 Revealing Logical Structure with Indentation……Page 143
3.1.3 The UPPER-lower Style……Page 144
3.1.4 Formatting Single Statements……Page 145
3.1.5 Formatting Your Declarations……Page 146
3.1.6 Formatting Multiline Statements……Page 147
3.2 Formatting SQL Statements……Page 150
3.3.1 Formatting IF Statements……Page 153
3.3.2 Formatting Loops……Page 154
3.3.3 Formatting Exception Handlers……Page 155
3.4 Formatting PL/SQL Blocks……Page 157
3.5 Formatting Packages……Page 159
3.6 Using Comments Effectively……Page 161
3.6.2 Explain the Why — Not the How — of Your Program……Page 162
3.6.3 Make Comments Easy to Enter and Maintain……Page 163
3.6.4 Maintain Indentation……Page 164
3.6.5 Comment Declaration Statements……Page 165
3.7.1 Document the Package Specification……Page 167
3.7.2 Document the Package Body……Page 168
BookmarkTitle:……Page 170
4.1.1 Choose the Right Name……Page 171
4.1.2 Select Readable Names……Page 172
4.2 Scalar Datatypes……Page 173
4.2.1 Numeric Datatypes……Page 174
4.2.2 Numeric Subtypes……Page 176
4.2.3 Character Datatypes……Page 177
4.2.5 The Date-Time Datatype……Page 184
4.2.6 NLS Character Datatypes……Page 185
4.2.7 LOB Datatypes……Page 186
4.2.8 Conversion Between Datatypes……Page 190
4.3 NULLs in PL/SQL……Page 193
4.3.2 Checking for NULL Values……Page 194
4.3.3 Function Results with NULL Arguments……Page 195
4.4.2 Declaration Examples……Page 197
4.4.3 Default Values……Page 198
4.4.4 NOT NULL Clause……Page 199
4.5.2 Anchoring at Compile Time……Page 200
4.5.3 Nesting Usages of the %TYPE Attribute……Page 201
4.5.4 Anchoring to Variables in Other PL/SQL Blocks……Page 202
4.5 Anchored Declarations……Page 203
4.6.1 Declaring Subtypes……Page 205
4.6.2 Examples of Subtype Declarations……Page 206
4.6.3 Emulating Constrained Subtypes……Page 207
4.7.1 Establish Clear Variable Naming Conventions……Page 209
4.7.2 Name Subtypes to Self-Document Code……Page 211
4.7.4 Use Named Constants to Avoid Hardcoding Values……Page 212
4.7.5 Convert Variables into Named Constants……Page 213
4.7.7 Use %TYPE When a Variable Represents a Column……Page 214
4.7.8 Use %TYPE to Standardize Nondatabase Declarations……Page 215
4.7.9 Use Variables to Hide Complex Logic……Page 216
BookmarkTitle:……Page 220
5.1.1 The IF-THEN Combination……Page 221
5.1.2 The IF-THEN-ELSE Combination……Page 222
5.1.3 The IF-ELSIF Combination……Page 223
5.1.4 Nested IF Statements……Page 227
5.2.1 The GOTO Statement……Page 229
5.2.2 The NULL Statement……Page 232
BookmarkTitle:……Page 235
6.1 Transaction Management……Page 236
6.1.2 The ROLLBACK Statement……Page 237
6.1.4 The SET TRANSACTION Statement……Page 238
6.1.5 The LOCK TABLE Statement……Page 239
6.2 Cursors in PL/SQL……Page 241
6.2.2 Cursor Operations……Page 242
6.3.2 Drawbacks of Implicit Cursors……Page 244
6.3.3 Explicit Cursors……Page 246
6.4.1 The Cursor Name……Page 248
6.4.3 Identifier Precedence in a Cursor……Page 249
6.4.4 The Cursor RETURN Clause……Page 250
6.5 Opening Cursors……Page 253
6.6.1 Matching Column List with INTO Clause……Page 255
6.6.2 Fetching Past the Last Row……Page 257
6.7 Column Aliases in Cursors……Page 258
6.8.1 Maximum Number of Cursors……Page 260
6.8.2 Closing Local Cursors……Page 261
6.9 Cursor Attributes……Page 262
6.9.1 The %FOUND Attribute……Page 263
6.9.3 The %ROWCOUNT Attribute……Page 264
6.9.6 Differences Between Implicit and Explicit Cursor Attributes……Page 265
6.10 Cursor Parameters……Page 267
6.10.2 Opening Cursors with Parameters……Page 268
6.10.5 Default Values for Parameters……Page 269
6.11 SELECT FOR UPDATE in Cursors……Page 270
6.11.1 Releasing Locks with COMMIT……Page 271
6.11.2 The WHERE CURRENT OF Clause……Page 272
6.12.2 Similarities to Static Cursors……Page 274
6.12.3 Declaring REF CURSOR Types and Cursor Variables……Page 275
6.12.4 Opening Cursor Variables……Page 276
6.12.5 Fetching from Cursor Variables……Page 277
6.12.6 Rules for Cursor Variables……Page 278
6.12.7 Passing Cursor Variables as Arguments……Page 279
6.12.8 Cursor Variable Restrictions……Page 281
BookmarkTitle:……Page 284
6.12 Cursor Variables……Page 286
6.13.1 Validating Foreign Key Entry with Cursors……Page 288
6.13.2 Managing a Work Queue with SELECT FOR UPDATE……Page 290
BookmarkTitle:……Page 294
7.1.1 Examples of Different Loops……Page 295
7.1.2 Structure of PL/SQL Loops……Page 296
7.2 The Simple Loop……Page 298
7.2.1 Terminating a Simple Loop: EXIT and EXIT WHEN……Page 299
7.2.2 Emulating a REPEAT UNTIL Loop……Page 300
7.3.1 Rules for Numeric FOR Loops……Page 301
7.3.2 Examples of Numeric FOR Loops……Page 302
7.3.3 Handling Nontrivial Increments……Page 303
7.4.1 Example of Cursor FOR Loops……Page 304
7.4.2 The Cursor FOR Loop Record……Page 305
7.4.3 When to Use the Cursor FOR Loop……Page 306
7.5 The WHILE Loop……Page 308
7.5.1 The Infinite WHILE Loop……Page 309
7.6.1 Loop Labels……Page 310
7.6.2 Loop Scope……Page 312
7.7.1 Naming Loop Indexes……Page 314
7.7.2 The Proper Way to Say Goodbye……Page 315
7.7.4 PL/SQL Loops Versus SQL Processing……Page 317
8.1 Why Exception Handling?……Page 320
BookmarkTitle:……Page 321
8. Exception Handlers……Page 322
8.2 The Exception Section……Page 324
8.3.1 Named System Exceptions……Page 326
8.3.2 Named Programmer-Defined Exceptions……Page 328
8.3.3 Unnamed System Exceptions……Page 329
8.3.4 Unnamed Programmer-Defined Exceptions……Page 330
8.4.1 Scope of an Exception……Page 332
8.4.2 Propagation of an Exception……Page 336
8.5.1 Who Raises the Exception?……Page 339
8.5.2 Re-Raising an Exception……Page 340
8.5.4 Exceptions Raised in an Exception Handler……Page 341
8.6.1 Combining Multiple Exceptions in a Single Handler……Page 344
8.6.3 Using SQLCODE and SQLERRM in WHEN OTHERS Clause……Page 345
8.6.4 Continuing Past Exceptions……Page 346
8.7.2 RAISE_APPLICATION_ERROR in a database trigger……Page 349
8.8 NO_DATA_FOUND: Multipurpose Exception……Page 351
8.9 Exception Handler as IF Statement……Page 353
8.10 RAISE Nothing but Exceptions……Page 355
BookmarkTitle:……Page 358
9.1.1 Different Types of Records……Page 359
9.1.3 Benefits of Using Records……Page 360
9.1.4 Guidelines for Using Records……Page 361
9.1.6 Comparing Two Records……Page 362
9.2.1 Declaring Records with the %ROWTYPE Attribute……Page 364
9.3.1 Choosing Columns for a Cursor Record……Page 366
9.3.2 Setting the Record’s Column Names……Page 367
9.4.1 Declaring Programmer-Defined Record TYPEs……Page 369
9.4.2 Declaring the Record……Page 370
9.4.3 Examples of Programmer-Defined Record Declarations……Page 371
9.5.1 Direct Field Assignment……Page 373
9.5.3 FETCH INTO from an Explicit Cursor……Page 374
9.5.4 Aggregate Assignment……Page 375
9.6 Record Types and Record Compatibility……Page 376
9.6.2 Record Initialization……Page 377
9.7.1 Example of Nested Records……Page 379
9.7.3 Aggregate Assignments of Nested Records……Page 380
9.7.4 Denormalizing Program Data with Nested Records……Page 381
10.1 PL/SQL Tables and Other Collections……Page 384
10.1.1 PL/SQL Tables……Page 385
10. PL/SQL Tables……Page 386
10.2 Characteristics of PL/SQL Tables……Page 387
10.3 PL/SQL Tables and DML Statements……Page 389
10.4.1 Defining the Table TYPE……Page 390
10.4.2 Declaring the PL/SQL Table……Page 391
10.5.2 Referencing an Undefined Row……Page 392
10.5.3 Nonsequential Use of PL/SQL Table……Page 393
10.5.4 Passing PL/SQL Tables as Parameters……Page 394
10.6.2 Iterative Assignment……Page 396
10.6.3 Aggregate Assignment……Page 397
10.7 Clearing the PL/SQL Table……Page 398
10.8 PL/SQL Table Enhancements in PL/SQL Release 2.3……Page 400
10.8.1 PL/SQL Tables of Records……Page 401
10.8.2 PL/SQL Table Built-ins……Page 403
10.9.1 Transferring Database Information to PL/SQL Tables……Page 407
10.9.2 Data-Smart Row Numbers in PL/SQL Tables……Page 408
10.9.3 Displaying a PL/SQL Table……Page 410
10.9.4 Building Traditional Arrays with PL/SQL Tables……Page 415
10.9.5 Optimizing Foreign Key Lookups with PL/SQL Tables……Page 421
BookmarkTitle:……Page 428
11. Character Functions……Page 429
11.1.2 The CHR function……Page 430
11.1.3 The CONCAT function……Page 431
11.1.4 The INITCAP function……Page 432
11.1.5 The INSTR function……Page 433
11.1.7 The LOWER function……Page 436
11.1.8 The LPAD function……Page 437
11.1.9 The LTRIM function……Page 438
11.1.10 The REPLACE function……Page 439
11.1.11 The RPAD function……Page 442
11.1.12 The RTRIM function……Page 443
11.1.14 The SUBSTR function……Page 444
11.1.15 The TRANSLATE function……Page 448
11.1.16 The UPPER function……Page 449
11.2.1 Parsing a Name……Page 450
11.2.2 Implementing Word Wrap for Long Text……Page 455
11.2.3 Filling Text to Fit a Line……Page 458
11.2.4 Counting Substring Occurrences in Strings……Page 460
11.2.5 Verifying String Formats with TRANSLATE……Page 462
BookmarkTitle:……Page 465
12. Date Functions……Page 466
12.1.1 The ADD_MONTHS function……Page 467
12.1.2 The LAST_DAY function……Page 468
12.1.3 The MONTHS_BETWEEN function……Page 469
12.1.4 The NEW_TIME function……Page 470
12.1.5 The NEXT_DAY function……Page 471
12.1.6 The ROUND function……Page 472
12.1.8 The TRUNC function……Page 474
12.2.1 Customizing the Behavior of ADD_MONTHS……Page 477
12.2.2 Using NEW_TIME in Client-Server Environments……Page 478
BookmarkTitle:……Page 483
13. Numeric, LOB, and Miscellaneous Functions……Page 484
13.1.1 The ABS function……Page 485
13.1.5 The ATAN2 function……Page 486
13.1.6 The CEIL function……Page 487
13.1.10 The FLOOR function……Page 488
13.1.14 The POWER function……Page 489
13.1.17 The SIN function……Page 490
13.1.22 The TRUNC function……Page 491
13.1.23 Rounding and Truncation with PL/SQL……Page 492
13.2.1 The BFILENAME function……Page 493
13.2.3 The EMPTY_CLOB function……Page 495
13.3.1 The DUMP function……Page 496
13.3.4 The NVL function……Page 497
13.3.6 The SQLERRM function……Page 499
13.3.8 The USER function……Page 500
13.3.9 The USERENV function……Page 501
13.3.10 The VSIZE function……Page 502
BookmarkTitle:……Page 503
14.1 Conversion Formats……Page 504
14.1.1 Date Format Models……Page 505
14.1.2 Number Format Models……Page 507
14.2.3 The HEXTORAW function……Page 510
14.2.6 The TO_CHAR function (date conversion)……Page 511
14.2.8 The TO_DATE function……Page 512
14.2.9 The TO_NUMBER function……Page 514
14.3.1 FM: Suppressing Blanks and Zeros……Page 515
14.3.2 FX: Matching Formats Exactly……Page 516
14.3.3 RR: Changing Millenia……Page 517
14.3.4 Using TO_CHAR to Create a Date Range……Page 518
14.3.5 Building a Date Manager……Page 522
15.2.1 Sequence of Section Construction……Page 532
15.3.1 The Structure of an Anonymous Block……Page 533
15.3.3 Anonymous Blocks in the Oracle Tools……Page 536
15.3.5 Scope and Visibility……Page 537
15.4.2 Procedure Header……Page 539
15.4.4 The END Label……Page 540
BookmarkTitle:……Page 541
15. Procedures and Functions……Page 545
15.1 Modular Code……Page 549
15.2 Review of PL/SQL Block Structure……Page 551
15.3 The Anonymous PL/SQL Block……Page 552
15.4 Procedures……Page 553
15.5.1 Structure of a Function……Page 554
15.5.2 The RETURN Datatype……Page 555
15.5.4 Calling a Function……Page 556
15.5.5 Function Header……Page 557
15.5.8 The RETURN Statement……Page 558
15.6.1 Defining the Parameters……Page 561
15.6.2 Parameter Modes……Page 562
15.6.3 Actual and Formal Parameters……Page 565
15.6.4 Matching Actual and Formal Parameters in PL/SQL……Page 566
15.6.5 Default Values……Page 568
15.7.1 Benefits of Local Modularization……Page 569
15.7.3 Improving Readability……Page 570
15.7.6 Spruce Up Your Code with Local Modules!……Page 572
15.8.1 Overloading in PL/SQL Built-Ins……Page 573
15.8.3 Where to Overload Modules……Page 574
15.8.4 Restrictions on Overloading……Page 575
15.9 Forward Declarations……Page 578
15.10 Go Forth and Modularize!……Page 580
BookmarkTitle:……Page 581
16. Packages……Page 582
16.1.4 Object Persistence……Page 583
16.1.5 Performance Improvement……Page 584
16.2.1 The Specification……Page 585
16.2.3 Package Syntax……Page 586
16.2.4 Public and Private Package Elements……Page 587
16.2.5 How to Reference Package Elements……Page 588
16.2.6 Quick Tour of a Package……Page 589
16.3 The Package Specification……Page 593
16.3.1 Packages Without Bodies……Page 594
16.3.2 Declaring Package Cursors……Page 597
16.4.1 Declare in Specification or Body……Page 599
16.4.2 Synchronize Body with Package……Page 600
16.5.1 Architecture of Package-Based Data……Page 602
16.5.3 Global Public Data……Page 603
16.5.4 Global Private Data……Page 604
16.5.5 Providing an Interface to Global Data……Page 605
16.6.2 Use Initialization Section for Complex Logic……Page 607
16.6.4 Load Session Data in Initialization Section……Page 608
BookmarkTitle:……Page 610
17.1 Looking at the Problem……Page 611
17.2 Syntax for Calling Stored Functions in SQL……Page 614
17.3 Requirements for Stored Functions in SQL……Page 616
17.4 Restrictions on PL/SQL Functions in SQL……Page 618
17.5.1 The RESTRICT_REFERENCES Pragma……Page 620
17.5.2 Asserting Purity Level with Package Initialization Section……Page 622
17.6 Column/Function Name Precedence……Page 624
17.7.1 Manual Application of Pragmas……Page 625
17.7.2 Read Consistency Model Complications……Page 626
17.8.1 Encapsulating Calculations……Page 628
17.8.2 Combining Scalar and Aggregate Values……Page 629
17.8.3 Replacing Correlated Subqueries……Page 631
17.8.4 Replacing DECODEs with IF Statements……Page 633
17.8.5 GROUP BY Partial Column Values……Page 635
17.8.6 Sequential Processing Against a Column’s Value……Page 636
17.8.7 Recursive Processing in a SQL Statement……Page 637
BookmarkTitle:……Page 640
18. Object Types……Page 641
18.1.1 Terminology……Page 642
18.1.2 Some Simple Examples……Page 643
18.1.3 Comparison: Oracle8 Objects and Earlier Features……Page 644
18.1.4 Characteristics of Objects……Page 645
18.1.5 Object Programming Themes……Page 647
18.2.2 Defining the Object Type Body……Page 651
18.2.3 Adding Complex Data Structures……Page 655
18.3.2 CREATE TYPE and DROP TYPE: Creating and Dropping Types……Page 658
18.3.4 Dot Notation……Page 660
18.3.5 SELF: The Implied Parameter……Page 663
18.3.6 Comparing Objects……Page 664
18.3.7 Privileges……Page 667
18.4.1 The Need to Initialize……Page 669
18.4.2 OID, VALUE, REF, and DEREF……Page 671
18.5 Modifying Persistent Objects……Page 679
18.5.1 Approach 1: Permit Full Use of Conventional SQL……Page 680
18.5.2 Approach 2: Define Methods and Permit Limited Use of Conventional SQL……Page 681
18.5.3 Approach 3: Do Everything via Methods……Page 682
18.5.4 Approach 4: Use an Object and a PL/SQL Container Package……Page 688
18.5.5 Implications for Developer/2000……Page 691
18.6.1 Data Dictionary……Page 692
18.6.3 Schema Evolution……Page 693
18.7 Making the Objects Option Work……Page 696
BookmarkTitle:……Page 698
19.1 Types of Collections……Page 699
19.2.1 Collections “In the Database”……Page 704
19.2.2 Collections in PL/SQL……Page 706
19.3 Syntax for Declaring Collection Datatypes……Page 711
19.4.1 Initializing Collection Variables……Page 713
19.4.3 Adding and Removing Elements……Page 717
19.4.4 Comparing Collections……Page 719
19.5.1 The THE Pseudo-function……Page 721
19.5.2 The CAST Pseudo-function……Page 723
19.5.3 The MULTISET Pseudo-function……Page 724
19.5.4 The TABLE Pseudo-function……Page 726
19.6 Collection Built-Ins……Page 728
19.6.2 DELETE [ ( i [ , j ] ) ]……Page 729
19.6.4 EXTEND [ (n [,i] ) ]……Page 730
19.6.6 LIMIT……Page 731
19.6.8 TRIM [ (n ) ]……Page 732
19.7 Example: PL/SQL-to-Server Integration……Page 734
19.8.2 Data Dictionary……Page 737
19.8.3 Call by Reference or Call by Value……Page 738
19.9 Which Collection Type Should I Use?……Page 739
20.1 Example: Using Object Views……Page 740
BookmarkTitle:……Page 741
20. Object Views……Page 742
20.2 INSTEAD OF Triggers……Page 747
20.2.1 INSTEAD OF Triggers: To Use or Not to Use?……Page 748
20.3.1 CREATE VIEW: Creating an Object View……Page 751
20.3.3 MAKE_REF: Returning a Virtual REF……Page 752
20.4.1 OID Uniqueness……Page 754
20.4.2 Using REFs with Object Views……Page 756
20.4.4 REFs to Nonunique OIDs……Page 761
20.5 Not All Views with Objects Are Object Views……Page 762
20.6 Schema Evolution……Page 763
20.7.1 Data Dictionary……Page 765
20.7.3 Forcing Compilation……Page 766
20.8 Postscript: Using the BFILE Datatype……Page 767
BookmarkTitle:……Page 770
21. External Procedures……Page 771
21.1.1 Example: Determining Free Disk Space on Windows NT……Page 772
21.1.3 Advantages……Page 774
21.1.4 Limitations……Page 775
21.2.1 Step 1: Set Up the Listener……Page 777
21.2.3 Step 3: Issue CREATE LIBRARY Statement……Page 779
21.2.4 Step 4: Create the PL/SQL Body……Page 780
21.2.5 Using the rand External Procedure……Page 781
21.3.1 CREATE LIBRARY: Creating the External Procedure Library……Page 783
21.3.2 EXTERNAL: Creating the PL/SQL Body……Page 784
21.3.3 DROP: Dropping Libraries……Page 785
21.4.1 Datatype Conversion……Page 786
21.4.2 More Syntax: The PARAMETERS Clause……Page 788
21.4.3 Properties……Page 789
21.4.4 Correct Declaration of Properties……Page 791
21.5 OCI Service Routines……Page 793
21.6.2 Rules and Warnings About External Procedures……Page 794
21.7.1 Example: Retrieving the Time Zone……Page 797
21.7.2 Example: Sending Email……Page 800
BookmarkTitle:……Page 805
22.1.1 Make Sure the Module Name Explains the Module……Page 806
22.1.2 Develop Consistent Naming Conventions for Your Formal Parameters……Page 808
22.1.3 Name Packages and Their Elements to Reflect the Packaged Structure……Page 809
22.2.1 Avoid Side Effects in Functions……Page 811
22.2.2 Use a Single RETURN Statement for Successful Termination……Page 814
22.2.3 Avoid Exception Handlers for Normal Program Exits……Page 817
22.2.4 Use Assertion Modules to Validate Parameters and Assumptions……Page 818
22.3 Take Full Advantage of Local Modularization……Page 822
22.4 Be Wary of Modules Without Any Parameters……Page 825
22.5 Create Independent Modules……Page 827
22.5.3 Use Parameters Liberally……Page 828
22.5.4 Avoid Global Variables and Data Structures……Page 829
22.6.1 Build an ADT in Phases……Page 831
22.6.3 Progress Box as ADT……Page 832
22.6.4 Price Paid for Code Dispersion……Page 834
22.7.1 Document All Parameters and Their Functions……Page 838
22.7.2 Use Self-Identifying Parameters (Avoid Boolean Values)……Page 839
22.7.3 Assign Values to All OUT and IN OUT Parameters……Page 840
22.7.4 Ensure Case Consistency of Parameters……Page 842
22.7.5 Default Values and Remote Procedure Calls……Page 844
BookmarkTitle:……Page 846
23. Managing Code in the Database……Page 847
23.1.2 Executing Functions……Page 848
23.1.3 Memory-Based Architecture of PL/SQL Code……Page 849
23.1.4 Key Concepts for Program Execution……Page 850
23.2.1 Execute Authority on Stored Objects……Page 853
23.2.2 Creating Synonyms for Stored Objects……Page 855
23.3.1 Interdependencies of Stored Objects……Page 856
23.4 Remote Procedure Calls……Page 859
23.5.1 Creating Stored Objects……Page 860
23.5.3 Changing Stored Objects……Page 861
23.5.4 Viewing Compilation Errors in SQL*Plus……Page 863
23.6.1 Displaying Object Dependencies……Page 864
23.6.2 Displaying Information About Stored Objects……Page 865
23.6.3 Analyzing the Size of PL/SQL Code……Page 867
23.6.5 Cross-Referencing Source Code……Page 868
23.6.6 Finding the Code for a Line Number……Page 869
23.6.7 Changing Source Code in the Database……Page 870
23.7.1 How to Encrypt Code……Page 872
23.7.3 Impact of Encrypting Code……Page 873
BookmarkTitle:……Page 876
24.1 The Wrong Way to Debug……Page 877
24.1.2 Irrational Debugging……Page 878
24.2.1 Gather Data……Page 880
24.2.2 Remain Logical at All Times……Page 881
24.2.3 Analyze Instead of Trying……Page 882
24.2.5 Change and Test One Area of Code at a Time……Page 883
24.2.6 Document and Back Up Your Efforts……Page 884
24.2.7 Test All Assumptions……Page 885
24.2.8 Leverage Existing Utilities — Or Build Your Own……Page 886
24.2.9 Build Debugging Messages into Your Packages……Page 887
BookmarkTitle:……Page 890
25.1 Analyzing Program Performance……Page 891
25.1.1 Use the DBMS_UTILITY.GET_TIME Function……Page 892
25.2.2 Pin Critical Code into the SGA……Page 895
25.2.3 Tune ACCESS$ Table to Reduce First Execution Time of Code……Page 897
25.2.5 Reducing Memory Usage of Package Variables……Page 898
25.3.1 Use Package Data to Minimize SQL Access……Page 900
25.3.2 Call PL/SQL Functions in SQL to Reduce I/O……Page 901
25.3.3 Avoid Client-Side SQL……Page 904
25.3.5 Avoid Procedural Code When Possible……Page 905
25.3.6 Use PL/SQL to Improve Performance of IO-Intensive SQL……Page 906
25.3.7 Keep Database Triggers Small……Page 907
25.4.1 There Are No Sacred Cows……Page 910
25.4.2 Zen and the Art of PL/SQL Tuning……Page 913
25.4.3 Rely on Local Variables to Improve Performance……Page 919
25.4.4 Use Package Data to Avoid Passing “Bulky” Parameter Values……Page 922
25.4.5 Use PLS_INTEGER for All Integer Operations……Page 924
25.4.7 Avoid Type Conversions When Possible……Page 925
25.4.8 Use Index-By Tables of Records and Objects……Page 926
25.5 Overview of PL/SQL8 Enhancements……Page 927
BookmarkTitle:……Page 929
26.1.1 Enabling Program Units for Tracing……Page 930
26.1.2 Turning On the Trace……Page 931
26.1.3 A Sample Tracing Session……Page 932
26.2.1 Features of a Real-Time Support Mechanism……Page 934
26.2.2 Starting and Stopping a Support Session……Page 935
26.2.3 Filtering Trace Information……Page 936
26.3 Free Format Filtering……Page 938
26.4.1 From Idea to Implementation……Page 940
26.5 Quick-and-Dirty Tracing……Page 942
Part V: New PL/SQL8 Features……Page 946
BookmarkTitle:……Page 947
BookmarkTitle:……Page 948
Part I: Programming in PL/SQL……Page 949
BookmarkTitle:……Page 950
Part II: PL/SQL Language Elements……Page 951
BookmarkTitle:……Page 952
Part III: Built-In Functions……Page 953
BookmarkTitle:……Page 954
Part IV: Modular Code……Page 955
BookmarkTitle:……Page 956
Part V: New PL/SQL8 Features……Page 957
BookmarkTitle:……Page 958
Part VI: Making PL/SQL Programs Work……Page 959
BookmarkTitle:……Page 960
Part VII: Appendixes……Page 961
BookmarkTitle:……Page 962
Dedication……Page 963
BookmarkTitle:……Page 964
Foreword……Page 965
BookmarkTitle:……Page 967
Preface……Page 968
Objectives of This Book……Page 970
About the Contents……Page 971
Audience……Page 974
Conventions Used in This Book……Page 976
Which Platform or Version?……Page 978
About the Disk……Page 979
Comments and Questions……Page 980
First Edition (Steven)……Page 981
Second Edition (Steven)……Page 982
Second Edition (Bill)……Page 983