Oracle PL SQL Programming

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 Table of Contents 2 25 A. What's on the Companion Disk? 26 A.1 Installing the Guide 26 27 A.2 Using the Guide 27 29 B. Calling Stored Procedures from PL/SQL Version 1.1 30 B.1 Using Stubs to Talk to Server-Side PL/SQL 31 33 B.2 Restrictions on Calling Stored Procedures 33 B.2.1 No Server-Side PL/SQL Datatypes 33 B.2.2 No Direct Stored Package Variable References 34 B.2.3 No Direct Remote Procedure Calls 36 B.2.4 No Default Parameter Values 36 38 C. Built-In Packages 39 C.1 Using the Built-in Packages 40 42 C.2 DBMS_ALERT 42 C.2.1 The REGISTER procedure 42 C.2.2 The REMOVE procedure 42 C.2.3 The REMOVEALL procedure 42 C.2.4 The SET_DEFAULTS procedure 42 C.2.5 The SIGNAL procedure 42 C.2.6 The WAITANY procedure 43 C.2.7 The WAITONE procedure 43 44 C.3 Oracle AQ, the Advanced Queueing Facility 44 C.3.1 DBMS_AQ (PL/SQL 8 Only) 44 C.3.2 DBMS_AQADM (PL/SQL 8 Only) 45 48 C.4 DBMS_DDL 48 C.4.1 The ALTER_COMPILE procedure 48 C.4.2 The ANALYZE_OBJECT procedure 48 49 C.5 DBMS_ JOB 49 C.5.1 The BROKEN procedure 49 C.5.2 The CHANGE procedure 49 C.5.3 The INTERVAL procedure 49 C.5.4 The ISUBMIT procedure 49 C.5.5 The NEXT_DATE procedure 50 C.5.6 The REMOVE procedure 50 C.5.7 The RUN procedure 50 C.5.8 The SUBMIT procedure 50 C.5.9 The USER_EXPORT procedure 50 C.5.10 The WHAT procedure 50 52 C.6 DBMS_LOB (PL/SQL8 Only) 52 C.6.1 The APPEND procedure 52 C.6.2 The COMPARE function 52 C.6.3 The COPY procedure 53 C.6.4 The ERASE procedure 53 C.6.5 The FILECLOSE procedure 53 C.6.6 The FILECLOSEALL procedure 53 C.6.7 The FILEEXISTS function 53 C.6.8 The FILEGETNAME procedure 53 C.6.9 The FILEISOPEN function 54 C.6.10 The FILEOPEN procedure 54 C.6.11 The GETLENGTH function 54 C.6.12 The INSTR function 54 C.6.13 The READ procedure 54 C.6.14 The SUBSTR function 55 C.6.15 The TRIM procedure 55 C.6.16 The WRITE procedure 55 57 C.7 DBMS_LOCK 57 C.7.1 The ALLOCATE_UNIQUE procedure 57 C.7.2 The CONVERT function 57 C.7.3 The RELEASE function 58 C.7.4 The REQUEST function 58 C.7.5 The SLEEP procedure 58 60 C.8 DBMS_MAIL 60 C.8.1 The SEND procedure 60 61 C.9 DBMS_OUTPUT 61 C.9.1 The DISABLE procedure 61 C.9.2 The ENABLE procedure 61 C.9.3 The GET_LINE procedure 61 C.9.4 The GET_LINES procedure 61 C.9.5 The NEW_LINE procedure 61 C.9.6 The PUT procedure 62 C.9.7 The PUT_LINE procedure 62 63 C.10 DBMS_PIPE 63 C.10.1 The CREATE_PIPE function 63 C.10.2 The NEXT_ITEM_TYPE function 63 C.10.3 The PACK_MESSAGE procedure 64 C.10.4 The PURGE procedure 64 C.10.5 The RECEIVE_MESSAGE function 64 C.10.6 The REMOVE_PIPE function 64 C.10.7 The RESET_BUFFER procedure 64 C.10.8 The SEND_MESSAGE function 65 C.10.9 The UNIQUE_SESSION_NAME function 65 C.10.10 The UNPACK_MESSAGE procedure 65 66 C.11 DBMS_ROWID (PL/SQL8 Only) 66 C.11.1 The ROWID_CREATE function 66 C.11.2 The ROWID_INFO procedure 66 C.11.3 The ROWID_TYPE function 66 C.11.4 The ROWID_OBJECT function 66 C.11.5 The ROWID_RELATIVE_FNO function 67 C.11.6 The ROWID_BLOCK_NUMBER function 67 C.11.7 The ROWID_ROW_NUMBER function 67 C.11.8 The ROWID_TO_ABSOLUTE_FNO function 67 C.11.9 The ROWID_TO_EXTENDED function 67 C.11.10 The ROWID_TO_RESTRICTED function 67 C.11.11 The ROWID_VERIFY function 67 69 C.12 DBMS_SESSION 69 C.12.1 The CLOSE_DATABASE_LINK procedure 69 C.12.2 The IS_ROLE_ENABLED function 69 C.12.3 The RESET_PACKAGE procedure 69 C.12.4 The SET_LABEL procedure 69 C.12.5 The SET_NLS_LABEL procedure 69 C.12.6 The SET_NLS procedure 69 C.12.7 The SET_ROLE procedure 70 C.12.8 The SET_SQL_TRACE procedure 70 C.12.9 The UNIQUE_SESSION_ID function 70 71 C.13 DBMS_SNAPSHOT 71 C.13.1 The DROP_SNAPSHOT procedure 71 C.13.2 The GET_LOG_AGE procedure 71 C.13.3 The PURGE_LOG procedure 71 C.13.4 The REFRESH procedure 71 C.13.5 The REFRESH_ALL procedure 72 C.13.6 The SET_UP procedure 72 C.13.7 The WRAP_UP procedure 72 73 C.14 DBMS_SQL 73 C.14.1 The BIND_ARRAY procedure 73 C.14.2 The BIND_VARIABLE procedure 73 C.14.3 The CLOSE_CURSOR procedure 74 C.14.4 The COLUMN_VALUE procedure 74 C.14.5 The DEFINE_COLUMN procedure 75 C.14.6 The EXECUTE function 75 C.14.7 The EXECUTE_AND_FETCH function 76 C.14.8 The FETCH_ROWS function 76 C.14.9 The IS_OPEN function 76 C.14.10 The LAST_ERROR_POSITION function 76 C.14.11 The LAST_ROW_COUNT function 76 C.14.12 The LAST_ROW_ID function 76 C.14.13 The LAST_SQL_FUNCTION_CODE function 76 C.14.14 The OPEN_CURSOR function 77 C.14.15 The PARSE procedure 77 C.14.16 The VARIABLE_VALUE procedure 77 78 C.15 DBMS_TRANSACTION 78 C.15.1 The ADVISE_COMMIT procedure 78 C.15.2 The ADVISE_NOTHING procedure 78 C.15.3 The ADVISE_ROLLBACK procedure 78 C.15.4 The COMMIT procedure 79 C.15.5 The COMMIT_COMMENT procedure 79 C.15.6 The COMMIT_FORCE procedure 79 C.15.7 The READ_ONLY procedure 79 C.15.8 The READ_WRITE procedure 79 C.15.9 The ROLLBACK procedure 80 C.15.10 The ROLLBACK_FORCE procedure 80 C.15.11 The ROLLBACK_SAVEPOINT procedure 80 C.15.12 The SAVEPOINT procedure 80 C.15.13 The USE_ROLLBACK_SEGMENT procedure 80 C.15.14 The BEGIN_DISCRETE_TRANSACTION procedure 80 C.15.15 The PURGE_MIXED procedure 81 C.15.16 The PURGE_LOST_DB procedure 81 C.15.17 The LOCAL_TRANSACTION_ID function 81 C.15.18 The STEP_ID function 81 83 C.16 DBMS_UTILITY 83 C.16.1 The ANALYZE_SCHEMA procedure 83 C.16.2 The COMMA_TO_TABLE procedure 83 C.16.3 The COMPILE_SCHEMA procedure 83 C.16.4 The FORMAT_CALL_STACK function 83 C.16.5 The FORMAT_ERROR_STACK function 83 C.16.6 The GET_TIME function 84 C.16.7 The IS_PARALLEL_SERVER function 84 C.16.8 The NAME_RESOLVE procedure 84 C.16.9 The NAME_TOKENIZE procedure 84 C.16.10 The PORT_STRING function 85 C.16.11 The TABLE_TO_COMMA procedure 85 86 C.17 UTL_FILE 86 C.17.1 Setting Up UTL_FILE 86 89 1. Introduction to PL/SQL 90 1.1 What Is PL/SQL? 90 92 1.2 The Concept of Programming in Oracle Applications 92 94 1.3 The Origins of PL/SQL 94 1.3.1 Improved Application Portability with PL/SQL 94 1.3.2 Improved Execution Authority and Transaction Integrity with PL/SQL 95 96 1.4 PL/SQL Versions 96 1.4.1 Working with Multiple Versions of PL/SQL 96 1.4.2 How This Book Handles Different Versions of PL/SQL 97 1.4.3 PL/SQL Version 2.0 97 1.4.4 PL/SQL Release 2.1 104 1.4.5 PL/SQL Release 2.2 106 1.4.6 PL/SQL Release 2.3 107 1.4.7 PL/SQL Version 8.0 108 1.4.8 PL/SQL Release 1.1 110 112 1.5 Advice for Oracle Programmers 112 1.5.1 Take a Creative, Even Radical Approach 112 1.5.2 Get Ready to Establish New Habits 112 1.5.3 Assume that PL/SQL Has What You Need 113 1.5.4 Share Your Ideas 114 115 1.6 A Few of My Favorite (PL/SQL) Things 115 1.6.1 Anchored declarations 115 1.6.2 Built-in functions 115 1.6.3 Built-in packages 115 1.6.4 The cursor FOR loop 116 1.6.5 Scoping with nested blocks 116 1.6.6 Module overloading 116 1.6.7 Local modules 117 1.6.8 Packages 117 119 1.7 Best Practices for PL/SQL Excellence 119 1.7.1 Write as Little Code as Possible 119 1.7.2 Synchronize Program and Data Structures 120 1.7.3 Center All Development Around Packages 121 1.7.4 Standardize Your PL/SQL Development Environment 122 1.7.5 Structured Code and Other Best Practices 122 125 2. PL/SQL Language Fundamentals 126 2.1 The PL/SQL Character Set 126 128 2.2 Identifiers 128 2.2.1 Reserved Words 129 2.2.2 Whitespace and Keywords 130 131 2.3 Literals 131 2.3.1 Embedding Single Quotes Inside a String 131 2.3.2 Numeric Literals 132 2.3.3 Boolean Literals 132 134 2.4 The Semicolon Delimiter 134 135 2.5 Comments 135 2.5.1 Single-Line Comment Syntax 135 2.5.2 Multiline Comment Syntax 135 137 2.6 The PRAGMA Keyword 137 138 2.7 Block Structure 138 2.7.1 Sections of the PL/SQL Block 138 2.7.2 Scope of a Block 139 2.7.3 Nested Blocks 139 141 3. Effective Coding Style 142 3.1 Fundamentals of Effective Layout 142 3.1.1 Revealing Logical Structure with Indentation 143 3.1.2 Using Case to Aid Readability 144 3.1.3 The UPPER-lower Style 144 3.1.4 Formatting Single Statements 145 3.1.5 Formatting Your Declarations 146 3.1.6 Formatting Multiline Statements 147 150 3.2 Formatting SQL Statements 150 153 3.3 Formatting Control Structures 153 3.3.1 Formatting IF Statements 153 3.3.2 Formatting Loops 154 3.3.3 Formatting Exception Handlers 155 157 3.4 Formatting PL/SQL Blocks 157 159 3.5 Formatting Packages 159 161 3.6 Using Comments Effectively 161 3.6.1 Comment As You Code 162 3.6.2 Explain the Why -- Not the How -- of Your Program 162 3.6.3 Make Comments Easy to Enter and Maintain 163 3.6.4 Maintain Indentation 164 3.6.5 Comment Declaration Statements 165 167 3.7 Documenting the Entire Package 167 3.7.1 Document the Package Specification 167 3.7.2 Document the Package Body 168 170 4. Variables and Program Data 171 4.1 Identifiers 171 4.1.1 Choose the Right Name 171 4.1.2 Select Readable Names 172 173 4.2 Scalar Datatypes 173 4.2.1 Numeric Datatypes 174 4.2.2 Numeric Subtypes 176 4.2.3 Character Datatypes 177 4.2.4 The Boolean Datatype 184 4.2.5 The Date-Time Datatype 184 4.2.6 NLS Character Datatypes 185 4.2.7 LOB Datatypes 186 4.2.8 Conversion Between Datatypes 190 193 4.3 NULLs in PL/SQL 193 4.3.1 NULL Values in Comparisons 194 4.3.2 Checking for NULL Values 194 4.3.3 Function Results with NULL Arguments 195 197 4.4 Variable Declarations 197 4.4.1 Constrained Declarations 197 4.4.2 Declaration Examples 197 4.4.3 Default Values 198 4.4.4 NOT NULL Clause 199 4.5.1 Benefits of Anchored Declarations 200 4.5.2 Anchoring at Compile Time 200 4.5.3 Nesting Usages of the %TYPE Attribute 201 4.5.4 Anchoring to Variables in Other PL/SQL Blocks 202 4.5.5 Anchoring to NOT NULL Datatypes 203 203 4.5 Anchored Declarations 203 205 4.6 Programmer-Defined Subtypes 205 4.6.1 Declaring Subtypes 205 4.6.2 Examples of Subtype Declarations 206 4.6.3 Emulating Constrained Subtypes 207 209 4.7 Tips for Creating and Using Variables 209 4.7.1 Establish Clear Variable Naming Conventions 209 4.7.2 Name Subtypes to Self-Document Code 211 4.7.3 Avoid Recycling Variables 212 4.7.4 Use Named Constants to Avoid Hardcoding Values 212 4.7.5 Convert Variables into Named Constants 213 4.7.6 Remove Unused Variables from Programs 214 4.7.7 Use %TYPE When a Variable Represents a Column 214 4.7.8 Use %TYPE to Standardize Nondatabase Declarations 215 4.7.9 Use Variables to Hide Complex Logic 216 220 5. Conditional and Sequential Control 221 5.1 Conditional Control Statements 221 5.1.1 The IF-THEN Combination 221 5.1.2 The IF-THEN-ELSE Combination 222 5.1.3 The IF-ELSIF Combination 223 5.1.4 Nested IF Statements 227 229 5.2 Sequential Control Statements 229 5.2.1 The GOTO Statement 229 5.2.2 The NULL Statement 232 235 6. Database Interaction and Cursors 236 6.1 Transaction Management 236 6.1.1 The COMMIT Statement 237 6.1.2 The ROLLBACK Statement 237 6.1.3 The SAVEPOINT Statement 238 6.1.4 The SET TRANSACTION Statement 238 6.1.5 The LOCK TABLE Statement 239 241 6.2 Cursors in PL/SQL 241 6.2.1 Types of Cursors 242 6.2.2 Cursor Operations 242 244 6.3 Implicit and Explicit Cursors 244 6.3.1 Implicit Cursors 244 6.3.2 Drawbacks of Implicit Cursors 244 6.3.3 Explicit Cursors 246 248 6.4 Declaring Cursors 248 6.4.1 The Cursor Name 248 6.4.2 PL/SQL Variables in a Cursor 249 6.4.3 Identifier Precedence in a Cursor 249 6.4.4 The Cursor RETURN Clause 250 253 6.5 Opening Cursors 253 255 6.6 Fetching from Cursors 255 6.6.1 Matching Column List with INTO Clause 255 6.6.2 Fetching Past the Last Row 257 258 6.7 Column Aliases in Cursors 258 260 6.8 Closing Cursors 260 6.8.1 Maximum Number of Cursors 260 6.8.2 Closing Local Cursors 261 262 6.9 Cursor Attributes 262 6.9.1 The %FOUND Attribute 263 6.9.2 The %NOTFOUND Attribute 264 6.9.3 The %ROWCOUNT Attribute 264 6.9.4 The %ISOPEN Attribute 265 6.9.5 Implicit SQL Cursor Attributes 265 6.9.6 Differences Between Implicit and Explicit Cursor Attributes 265 267 6.10 Cursor Parameters 267 6.10.1 Generalizing Cursors with Parameters 268 6.10.2 Opening Cursors with Parameters 268 6.10.3 Scope of Cursor Parameters 269 6.10.4 Cursor Parameter Modes 269 6.10.5 Default Values for Parameters 269 270 6.11 SELECT FOR UPDATE in Cursors 270 6.11.1 Releasing Locks with COMMIT 271 6.11.2 The WHERE CURRENT OF Clause 272 6.12.1 Features of Cursor Variables 274 6.12.2 Similarities to Static Cursors 274 6.12.3 Declaring REF CURSOR Types and Cursor Variables 275 6.12.4 Opening Cursor Variables 276 6.12.5 Fetching from Cursor Variables 277 6.12.6 Rules for Cursor Variables 278 6.12.7 Passing Cursor Variables as Arguments 279 6.12.8 Cursor Variable Restrictions 281 284 6.12 Cursor Variables 286 288 6.13 Working with Cursors 288 6.13.1 Validating Foreign Key Entry with Cursors 288 6.13.2 Managing a Work Queue with SELECT FOR UPDATE 290 294 7. Loops 295 7.1 Loop Basics 295 7.1.1 Examples of Different Loops 295 7.1.2 Structure of PL/SQL Loops 296 298 7.2 The Simple Loop 298 7.2.1 Terminating a Simple Loop: EXIT and EXIT WHEN 299 7.2.2 Emulating a REPEAT UNTIL Loop 300 301 7.3 The Numeric FOR Loop 301 7.3.1 Rules for Numeric FOR Loops 301 7.3.2 Examples of Numeric FOR Loops 302 7.3.3 Handling Nontrivial Increments 303 304 7.4 The Cursor FOR Loop 304 7.4.1 Example of Cursor FOR Loops 304 7.4.2 The Cursor FOR Loop Record 305 7.4.3 When to Use the Cursor FOR Loop 306 308 7.5 The WHILE Loop 308 7.5.1 The Infinite WHILE Loop 309 310 7.6 Managing Loop Execution 310 7.6.1 Loop Labels 310 7.6.2 Loop Scope 312 314 7.7 Tips for PL/SQL Loops 314 7.7.1 Naming Loop Indexes 314 7.7.2 The Proper Way to Say Goodbye 315 7.7.3 Avoiding the Phony Loop 317 7.7.4 PL/SQL Loops Versus SQL Processing 317 8.1 Why Exception Handling? 320 321 8. Exception Handlers 322 324 8.2 The Exception Section 324 326 8.3 Types of Exceptions 326 8.3.1 Named System Exceptions 326 8.3.2 Named Programmer-Defined Exceptions 328 8.3.3 Unnamed System Exceptions 329 8.3.4 Unnamed Programmer-Defined Exceptions 330 332 8.4 Determining Exception-Handling Behavior 332 8.4.1 Scope of an Exception 332 8.4.2 Propagation of an Exception 336 339 8.5 Raising an Exception 339 8.5.1 Who Raises the Exception? 339 8.5.2 Re-Raising an Exception 340 8.5.3 Exceptions Raised in a Declaration 341 8.5.4 Exceptions Raised in an Exception Handler 341 344 8.6 Handling Exceptions 344 8.6.1 Combining Multiple Exceptions in a Single Handler 344 8.6.2 Unhandled Exceptions 345 8.6.3 Using SQLCODE and SQLERRM in WHEN OTHERS Clause 345 8.6.4 Continuing Past Exceptions 346 349 8.7 Client-Server Error Communication 349 8.7.1 Using RAISE_APPLICATION_ERROR 349 8.7.2 RAISE_APPLICATION_ERROR in a database trigger 349 351 8.8 NO_DATA_FOUND: Multipurpose Exception 351 353 8.9 Exception Handler as IF Statement 353 355 8.10 RAISE Nothing but Exceptions 355 358 9. Records in PL/SQL 359 9.1 Record Basics 359 9.1.1 Different Types of Records 359 9.1.2 Accessing Record-Based Data 360 9.1.3 Benefits of Using Records 360 9.1.4 Guidelines for Using Records 361 9.1.5 Referencing a Record and its Fields 362 9.1.6 Comparing Two Records 362 364 9.2 Table-Based Records 364 9.2.1 Declaring Records with the %ROWTYPE Attribute 364 366 9.3 Cursor-Based Records 366 9.3.1 Choosing Columns for a Cursor Record 366 9.3.2 Setting the Record's Column Names 367 369 9.4 Programmer-Defined Records 369 9.4.1 Declaring Programmer-Defined Record TYPEs 369 9.4.2 Declaring the Record 370 9.4.3 Examples of Programmer-Defined Record Declarations 371 373 9.5 Assigning Values to and from Records 373 9.5.1 Direct Field Assignment 373 9.5.2 SELECT INTO from an Implicit Cursor 374 9.5.3 FETCH INTO from an Explicit Cursor 374 9.5.4 Aggregate Assignment 375 376 9.6 Record Types and Record Compatibility 376 9.6.1 Assignment Restrictions 377 9.6.2 Record Initialization 377 379 9.7 Nested Records 379 9.7.1 Example of Nested Records 379 9.7.2 Dot Notation with Nested Records 380 9.7.3 Aggregate Assignments of Nested Records 380 9.7.4 Denormalizing Program Data with Nested Records 381 10.1 PL/SQL Tables and Other Collections 384 10.1.1 PL/SQL Tables 385 10.1.2 Nested Tables and VARRAYs 386 386 10. PL/SQL Tables 386 387 10.2 Characteristics of PL/SQL Tables 387 389 10.3 PL/SQL Tables and DML Statements 389 390 10.4 Declaring a PL/SQL Table 390 10.4.1 Defining the Table TYPE 390 10.4.2 Declaring the PL/SQL Table 391 392 10.5 Referencing and Modifying PL/SQL Table Rows 392 10.5.1 Automatic Conversion of Row Number Expressions 392 10.5.2 Referencing an Undefined Row 392 10.5.3 Nonsequential Use of PL/SQL Table 393 10.5.4 Passing PL/SQL Tables as Parameters 394 396 10.6 Filling the Rows of a PL/SQL Table 396 10.6.1 Direct Assignment 396 10.6.2 Iterative Assignment 396 10.6.3 Aggregate Assignment 397 398 10.7 Clearing the PL/SQL Table 398 400 10.8 PL/SQL Table Enhancements in PL/SQL Release 2.3 400 10.8.1 PL/SQL Tables of Records 401 10.8.2 PL/SQL Table Built-ins 403 407 10.9 Working with PL/SQL Tables 407 10.9.1 Transferring Database Information to PL/SQL Tables 407 10.9.2 Data-Smart Row Numbers in PL/SQL Tables 408 10.9.3 Displaying a PL/SQL Table 410 10.9.4 Building Traditional Arrays with PL/SQL Tables 415 10.9.5 Optimizing Foreign Key Lookups with PL/SQL Tables 421 428 11. Character Functions 429 11.1 Character Function Descriptions 430 11.1.1 The ASCII function 430 11.1.2 The CHR function 430 11.1.3 The CONCAT function 431 11.1.4 The INITCAP function 432 11.1.5 The INSTR function 433 11.1.6 The LENGTH function 436 11.1.7 The LOWER function 436 11.1.8 The LPAD function 437 11.1.9 The LTRIM function 438 11.1.10 The REPLACE function 439 11.1.11 The RPAD function 442 11.1.12 The RTRIM function 443 11.1.13 The SOUNDEX function 444 11.1.14 The SUBSTR function 444 11.1.15 The TRANSLATE function 448 11.1.16 The UPPER function 449 450 11.2 Character Function Examples 450 11.2.1 Parsing a Name 450 11.2.2 Implementing Word Wrap for Long Text 455 11.2.3 Filling Text to Fit a Line 458 11.2.4 Counting Substring Occurrences in Strings 460 11.2.5 Verifying String Formats with TRANSLATE 462 465 12. Date Functions 466 12.1 Date Function Descriptions 467 12.1.1 The ADD_MONTHS function 467 12.1.2 The LAST_DAY function 468 12.1.3 The MONTHS_BETWEEN function 469 12.1.4 The NEW_TIME function 470 12.1.5 The NEXT_DAY function 471 12.1.6 The ROUND function 472 12.1.7 The SYSDATE function 474 12.1.8 The TRUNC function 474 477 12.2 Date Function Examples 477 12.2.1 Customizing the Behavior of ADD_MONTHS 477 12.2.2 Using NEW_TIME in Client-Server Environments 478 483 13. Numeric, LOB, and Miscellaneous Functions 484 13.1 Numeric Function Descriptions 485 13.1.1 The ABS function 485 13.1.2 The ACOS function 486 13.1.3 The ASIN function 486 13.1.4 The ATAN function 486 13.1.5 The ATAN2 function 486 13.1.6 The CEIL function 487 13.1.7 The COS function 488 13.1.8 The COSH function 488 13.1.9 The EXP function 488 13.1.10 The FLOOR function 488 13.1.11 The LN function 489 13.1.12 The LOG function 489 13.1.13 The MOD function 489 13.1.14 The POWER function 489 13.1.15 The ROUND function 490 13.1.16 The SIGN function 490 13.1.17 The SIN function 490 13.1.18 The SINH function 491 13.1.19 The SQRT function 491 13.1.20 The TAN function 491 13.1.21 The TANH function 491 13.1.22 The TRUNC function 491 13.1.23 Rounding and Truncation with PL/SQL 492 493 13.2 LOB Function Descriptions 493 13.2.1 The BFILENAME function 493 13.2.2 The EMPTY_BLOB function 495 13.2.3 The EMPTY_CLOB function 495 496 13.3 Miscellaneous Function Descriptions 496 13.3.1 The DUMP function 496 13.3.2 The GREATEST function 497 13.3.3 The LEAST function 497 13.3.4 The NVL function 497 13.3.5 The SQLCODE function 499 13.3.6 The SQLERRM function 499 13.3.7 The UID function 500 13.3.8 The USER function 500 13.3.9 The USERENV function 501 13.3.10 The VSIZE function 502 503 14. Conversion Functions 504 14.1 Conversion Formats 504 14.1.1 Date Format Models 505 14.1.2 Number Format Models 507 510 14.2 Conversion Function Descriptions 510 14.2.1 The CHARTOROWID function 510 14.2.2 The CONVERT function 510 14.2.3 The HEXTORAW function 510 14.2.4 The RAWTOHEX function 511 14.2.5 The ROWIDTOCHAR function 511 14.2.6 The TO_CHAR function (date conversion) 511 14.2.7 The TO_CHAR function (number conversion) 512 14.2.8 The TO_DATE function 512 14.2.9 The TO_NUMBER function 514 515 14.3 Conversion Function Examples 515 14.3.1 FM: Suppressing Blanks and Zeros 515 14.3.2 FX: Matching Formats Exactly 516 14.3.3 RR: Changing Millenia 517 14.3.4 Using TO_CHAR to Create a Date Range 518 14.3.5 Building a Date Manager 522 15.2.1 Sequence of Section Construction 532 15.2.2 PL/SQL Block Structure Examples 533 15.3.1 The Structure of an Anonymous Block 533 15.3.2 Examples of Anonymous Blocks 536 15.3.3 Anonymous Blocks in the Oracle Tools 536 15.3.4 Nested Blocks 537 15.3.5 Scope and Visibility 537 15.3.6 Block Labels 539 15.4.1 Calling a Procedure 539 15.4.2 Procedure Header 539 15.4.3 Procedure Body 540 15.4.4 The END Label 540 541 15. Procedures and Functions 545 15.1 Modular Code 549 551 15.2 Review of PL/SQL Block Structure 551 552 15.3 The Anonymous PL/SQL Block 552 553 15.4 Procedures 553 554 15.5 Functions 554 15.5.1 Structure of a Function 554 15.5.2 The RETURN Datatype 555 15.5.3 The END Label 556 15.5.4 Calling a Function 556 15.5.5 Function Header 557 15.5.6 Function Body 558 15.5.7 A Tiny Function 558 15.5.8 The RETURN Statement 558 561 15.6 Parameters 561 15.6.1 Defining the Parameters 561 15.6.2 Parameter Modes 562 15.6.3 Actual and Formal Parameters 565 15.6.4 Matching Actual and Formal Parameters in PL/SQL 566 15.6.5 Default Values 568 569 15.7 Local Modules 569 15.7.1 Benefits of Local Modularization 569 15.7.2 Reducing Code Volume 570 15.7.3 Improving Readability 570 15.7.4 Bottom-Up Reading 572 15.7.5 Scope of Local Modules 572 15.7.6 Spruce Up Your Code with Local Modules! 572 573 15.8 Module Overloading 573 15.8.1 Overloading in PL/SQL Built-Ins 573 15.8.2 Benefits of Overloading 574 15.8.3 Where to Overload Modules 574 15.8.4 Restrictions on Overloading 575 578 15.9 Forward Declarations 578 580 15.10 Go Forth and Modularize! 580 581 16. Packages 582 16.1 The Benefits of Packages 583 16.1.1 Enforced Information Hiding 583 16.1.2 Object-Oriented Design 583 16.1.3 Top-Down Design 583 16.1.4 Object Persistence 583 16.1.5 Performance Improvement 584 585 16.2 Overview of Package Structure 585 16.2.1 The Specification 585 16.2.2 The Body 586 16.2.3 Package Syntax 586 16.2.4 Public and Private Package Elements 587 16.2.5 How to Reference Package Elements 588 16.2.6 Quick Tour of a Package 589 593 16.3 The Package Specification 593 16.3.1 Packages Without Bodies 594 16.3.2 Declaring Package Cursors 597 599 16.4 The Package Body 599 16.4.1 Declare in Specification or Body 599 16.4.2 Synchronize Body with Package 600 602 16.5 Package Data 602 16.5.1 Architecture of Package-Based Data 602 16.5.2 Global Within a Single Oracle Session 603 16.5.3 Global Public Data 603 16.5.4 Global Private Data 604 16.5.5 Providing an Interface to Global Data 605 607 16.6 Package Initialization 607 16.6.1 Drawbacks of Package Initialization 607 16.6.2 Use Initialization Section for Complex Logic 607 16.6.3 Side Effects 608 16.6.4 Load Session Data in Initialization Section 608 610 17. Calling PL/SQL Functions in SQL 611 17.1 Looking at the Problem 611 614 17.2 Syntax for Calling Stored Functions in SQL 614 616 17.3 Requirements for Stored Functions in SQL 616 618 17.4 Restrictions on PL/SQL Functions in SQL 618 620 17.5 Calling Packaged Functions in SQL 620 17.5.1 The RESTRICT_REFERENCES Pragma 620 17.5.2 Asserting Purity Level with Package Initialization Section 622 624 17.6 Column/Function Name Precedence 624 625 17.7 Realities: Calling PL/SQL Functions in SQL 625 17.7.1 Manual Application of Pragmas 625 17.7.2 Read Consistency Model Complications 626 628 17.8 Examples of Embedded PL/SQL 628 17.8.1 Encapsulating Calculations 628 17.8.2 Combining Scalar and Aggregate Values 629 17.8.3 Replacing Correlated Subqueries 631 17.8.4 Replacing DECODEs with IF Statements 633 17.8.5 GROUP BY Partial Column Values 635 17.8.6 Sequential Processing Against a Column's Value 636 17.8.7 Recursive Processing in a SQL Statement 637 640 18. Object Types 641 18.1 Introduction to Oracle8 Objects 642 18.1.1 Terminology 642 18.1.2 Some Simple Examples 643 18.1.3 Comparison: Oracle8 Objects and Earlier Features 644 18.1.4 Characteristics of Objects 645 18.1.5 Object Programming Themes 647 651 18.2 Oracle Objects Example 651 18.2.1 Defining the Object Type Specification 651 18.2.2 Defining the Object Type Body 651 18.2.3 Adding Complex Data Structures 655 658 18.3 Syntax for Creating Object Types 658 18.3.1 About Object Types 658 18.3.2 CREATE TYPE and DROP TYPE: Creating and Dropping Types 658 18.3.3 CREATE TYPE BODY: Creating a Body 660 18.3.4 Dot Notation 660 18.3.5 SELF: The Implied Parameter 663 18.3.6 Comparing Objects 664 18.3.7 Privileges 667 669 18.4 Manipulating Objects in PL/SQL and SQL 669 18.4.1 The Need to Initialize 669 18.4.2 OID, VALUE, REF, and DEREF 671 679 18.5 Modifying Persistent Objects 679 18.5.1 Approach 1: Permit Full Use of Conventional SQL 680 18.5.2 Approach 2: Define Methods and Permit Limited Use of Conventional SQL 681 18.5.3 Approach 3: Do Everything via Methods 682 18.5.4 Approach 4: Use an Object and a PL/SQL Container Package 688 18.5.5 Implications for Developer/2000 691 692 18.6 Object Housekeeping 692 18.6.1 Data Dictionary 692 18.6.2 SQL*Plus "Describe" Command 693 18.6.3 Schema Evolution 693 696 18.7 Making the Objects Option Work 696 698 19. Nested Tables and VARRAYs 699 19.1 Types of Collections 699 704 19.2 Creating the New Collections 704 19.2.1 Collections "In the Database" 704 19.2.2 Collections in PL/SQL 706 711 19.3 Syntax for Declaring Collection Datatypes 711 713 19.4 Using Collections 713 19.4.1 Initializing Collection Variables 713 19.4.2 Assigning Values to Elements: Index (Subscript) Considerations 717 19.4.3 Adding and Removing Elements 717 19.4.4 Comparing Collections 719 721 19.5 Collection Pseudo-Functions 721 19.5.1 The THE Pseudo-function 721 19.5.2 The CAST Pseudo-function 723 19.5.3 The MULTISET Pseudo-function 724 19.5.4 The TABLE Pseudo-function 726 728 19.6 Collection Built-Ins 728 19.6.1 COUNT 729 19.6.2 DELETE [ ( i [ , j ] ) ] 729 19.6.3 EXISTS(i) 730 19.6.4 EXTEND [ (n [,i] ) ] 730 19.6.5 FIRST, LAST 731 19.6.6 LIMIT 731 19.6.7 PRIOR(i), NEXT(i) 732 19.6.8 TRIM [ (n ) ] 732 734 19.7 Example: PL/SQL-to-Server Integration 734 737 19.8 Collections Housekeeping 737 19.8.1 Privileges 737 19.8.2 Data Dictionary 737 19.8.3 Call by Reference or Call by Value 738 739 19.9 Which Collection Type Should I Use? 739 20.1 Example: Using Object Views 740 741 20. Object Views 742 747 20.2 INSTEAD OF Triggers 747 20.2.1 INSTEAD OF Triggers: To Use or Not to Use? 748 751 20.3 Syntax for Object Views 751 20.3.1 CREATE VIEW: Creating an Object View 751 20.3.2 DROP: Dropping Views and Triggers 752 20.3.3 MAKE_REF: Returning a Virtual REF 752 754 20.4 Differences Between Object Views and Object Tables 754 20.4.1 OID Uniqueness 754 20.4.2 Using REFs with Object Views 756 20.4.3 Storage of Virtual REFs 761 20.4.4 REFs to Nonunique OIDs 761 762 20.5 Not All Views with Objects Are Object Views 762 763 20.6 Schema Evolution 763 765 20.7 Object Views Housekeeping 765 20.7.1 Data Dictionary 765 20.7.2 Privileges 766 20.7.3 Forcing Compilation 766 767 20.8 Postscript: Using the BFILE Datatype 767 770 21. External Procedures 771 21.1 Introduction to External Procedures 772 21.1.1 Example: Determining Free Disk Space on Windows NT 772 21.1.2 Architecture 774 21.1.3 Advantages 774 21.1.4 Limitations 775 777 21.2 Steps in Creating an External Procedure 777 21.2.1 Step 1: Set Up the Listener 777 21.2.2 Step 2: Identify or Create the Shared Library 779 21.2.3 Step 3: Issue CREATE LIBRARY Statement 779 21.2.4 Step 4: Create the PL/SQL Body 780 21.2.5 Using the rand External Procedure 781 783 21.3 Syntax for External Procedures 783 21.3.1 CREATE LIBRARY: Creating the External Procedure Library 783 21.3.2 EXTERNAL: Creating the PL/SQL Body 784 21.3.3 DROP: Dropping Libraries 785 786 21.4 Mapping Parameters 786 21.4.1 Datatype Conversion 786 21.4.2 More Syntax: The PARAMETERS Clause 788 21.4.3 Properties 789 21.4.4 Correct Declaration of Properties 791 793 21.5 OCI Service Routines 793 794 21.6 External Procedure Housekeeping 794 21.6.1 Data Dictionary 794 21.6.2 Rules and Warnings About External Procedures 794 797 21.7 Examples 797 21.7.1 Example: Retrieving the Time Zone 797 21.7.2 Example: Sending Email 800 805 22. Code Design Tips 806 22.1 Select Meaningful Module and Parameter Names 806 22.1.1 Make Sure the Module Name Explains the Module 806 22.1.2 Develop Consistent Naming Conventions for Your Formal Parameters 808 22.1.3 Name Packages and Their Elements to Reflect the Packaged Structure 809 811 22.2 Build the Most Functional Functions 811 22.2.1 Avoid Side Effects in Functions 811 22.2.2 Use a Single RETURN Statement for Successful Termination 814 22.2.3 Avoid Exception Handlers for Normal Program Exits 817 22.2.4 Use Assertion Modules to Validate Parameters and Assumptions 818 822 22.3 Take Full Advantage of Local Modularization 822 825 22.4 Be Wary of Modules Without Any Parameters 825 827 22.5 Create Independent Modules 827 22.5.1 Stretch the Possibilities of the Module 828 22.5.2 Keep the Focus of Your Module 828 22.5.3 Use Parameters Liberally 828 22.5.4 Avoid Global Variables and Data Structures 829 831 22.6 Construct Abstract Data Types (ADTs) 831 22.6.1 Build an ADT in Phases 831 22.6.2 Some ADT Guidelines 832 22.6.3 Progress Box as ADT 832 22.6.4 Price Paid for Code Dispersion 834 838 22.7 Tips for Parameter Design 838 22.7.1 Document All Parameters and Their Functions 838 22.7.2 Use Self-Identifying Parameters (Avoid Boolean Values) 839 22.7.3 Assign Values to All OUT and IN OUT Parameters 840 22.7.4 Ensure Case Consistency of Parameters 842 22.7.5 Default Values and Remote Procedure Calls 844 846 23. Managing Code in the Database 847 23.1 Executing Stored Code 848 23.1.1 Executing Procedures 848 23.1.2 Executing Functions 848 23.1.3 Memory-Based Architecture of PL/SQL Code 849 23.1.4 Key Concepts for Program Execution 850 853 23.2 Transaction Integrity and Execute Authority 853 23.2.1 Execute Authority on