1. Working With Packages

   1. Chapter 1 PL/SQL Packages

      1. What Is a PL/SQL Package?
      2. What Are the Types and Layers of Packages?
      3. What Are the Benefits of Packages?
      4. Using Packages
      5. Types of Packages
      6. Building Packages

   2. Chapter 2 Best Practices for Packages

      1. Starting With Packages
      2. Using Effective Coding Style for Packages
      3. Selecting Package Names
      4. Organizing Package Source Code
      5. Constructing the Optimal Interface to Your Package
      6. Building Flexibility Into Your Packages
      7. Building Windows Into Your Packages
      8. Overloading for Smart Packages
      9. Modularizing for Maintainable Packages
      10. Hiding Package Data
      11. Simultaneous Construction of Multiple Packages

   3. Chapter 3 The PL/SQL Development Spiral

      1. The Basic Problem
      2. Adding Value
      3. Supplying Backward Compatibility
      4. Improving the User Interface
      5. Rough Waters Ahead
      6. Building a Structured Function
      7. Handling Program Assumptions
      8. Broadening the Scope
      9. Considering Implementation Options
      10. Choosing the Best Performer
      11. Don’t Forget Backward Compatibility
      12. Obliterating the Literals
      13. Glancing Backward, Looking Upward

2. PL/Vision Overview

   1. Chapter 4 Getting Started with PL/Vision

      1. What Is PL/Vision?
      2. packagesin PL/VisionPL/Vision Package Bundles
      3. installingPL/Vision LitePL/VisioninstallingInstallation Instructionss
      4. installingPL/Vision help packagespackageshelpdocumentationInstalling Online Help for PL/Vision
      5. documentationonline helpUsing Online Help
      6. SQL*Plusscripts included on diskfilescreated during installationcodecreated during installationSummary of Files on Disk

   2. Chapter 5 PL/Vision Package Specifications

      1. Common Package Elements
      2. p packagep: a DBMS_OUTPUT Substitute
      3. PLV: Top-Level Constants and Functions
      4. PLVcase: PL/SQL Code Conversion
      5. PLVcat: PL/SQL Code Cataloguing
      6. PLVchr: Operations on Single Characters
      7. PLVcmt: Commit Processing
      8. PLVddd: DDL Syntax Dump
      9. PLVdyn: Dynamic SQL Operations
      10. PLVexc: Exception Handling
      11. PLVfile: Operating System I/O Manager
      12. PLVfk: Foreign Key Interface
      13. PLVgen: PL/SQL Code Generator
      14. PLVhlp: Online Help Architechture
      15. PLVio: Input/Output Processing
      16. PLVlex: Lexical Analysis
      17. PLVlog: Logging Facility
      18. listsPLVlst package PLVlst: List Manager
      19. PLVmsg: Message Handling
      20. PLVobj: Object Interface
      21. PLVprs: String Parsing
      22. PLVprsps: PL/SQL Source Code Parsing
      23. PLVrb: Rollback Processing
      24. PLVstk: Stack Manager
      25. PLVtab: Table Interface
      26. PLVtkn: Token Table Interface
      27. PLVtmr: Program Performance Analyzer
      28. PLVtrc: Trace Facility
      29. PLVvu: Code and Error Viewing

3. Building Block Packages

   1. Chapter 6 PLV: Top-Level Constants and Functions

      1. NULLsubstitution valuesubstitution value, NULLNull Substitution Value
      2. Setting the PL/Vision Date Mask
      3. Assertion Routines
      4. PLV Utilities
      5. typesdatatypestypesThe Predefined Datatypes
      6. The Predefined Constants

   2. Chapter 7 p: A Powerful Substitute for DBMS_OUTPUT

      1. p packagell procedureUsing the l Procedure
      2. delimiterline separatorline separatorwhitespaceline separatorcharactersline separatorblank lineswhitespaceThe Line Separator
      3. stringsoutput prefixI/Ooutput prefixoutput prefixThe Output Prefix
      4. p packageoutput fromoutput from p packageI/Op package outputControlling Output from p

   3. Chapter 8 PLVtab: Easy Access to PL/SQL Tables

      1. typestableUsing PLVtab-Based PL/SQL Table Types
      2. display functionDisplaying PLVtab Tables
      3. togglestable formatheaders, tableShowing Header Toggle
      4. Showing Row Number Toggle
      5. prefixtable row valuedisplay prefixtablesrow value prefixSetting the Display Prefix
      6. empty tablesdeletingtable rowsEmptying Tables with PLVtab
      7. Implementing PLVtab.display

   4. Chapter 9 PLVmsg: Single-Sourcing PL/SQL Message Text

      1. PLVmsg Data Structures
      2. savingmessage textStoring Message Text
      3. Retrieving Message Text
      4. The Restriction Toggle
      5. Integrating PLVmsg with Error Handling
      6. Implementing load_ from_dbms

   5. Chapter 10 PLVprs, PLVtkn, and PLVprsps: Parsing Strings

      1. PLVprs: Useful String Parsing Extensions
      2. PLVtkn: Managing PL/SQL Tokens
      3. PLVprsps: Parsing PL/SQL Strings

   6. Chapter 11 PLVobj: A Packaged Interface to ALL_OBJECTS

      1. Why PLVobj?
      2. ALL_OBJECTS View
      3. Setting the Current Object
      4. Accessing ALL_OBJECTS
      5. Binding Objects to a Dynamic Cursor
      6. Populating a PL/SQL Table with Object Names
      7. A Programmatic Cursor FOR Loop
      8. Tracing PLVobj Activity

   7. Chapter 12 PLVio: Reading and Writing PL/SQL Source Code

      1. Why PLVio?
      2. Code Repositories Supported by PLVio
      3. Managing the Source Repository
      4. The Source WHERE Clause
      5. Managing the Target Repository
      6. Reading From the Source
      7. Writing to the Target
      8. Saving and Restoring Settings
      9. Cleaning Up Source and Target

   8. Chapter 13 PLVfile: Reading and Writing Operating System Files

      1. A Review of UTL_FILE
      2. Specifying the File in PLVfile
      3. Creating and Checking Existence of Files
      4. Opening and Closing Files
      5. Reading From a File
      6. Writing to a File
      7. Copying File Contents
      8. Displaying File Contents
      9. Handling File Errors with PLVfile
      10. Tracing PLVfile Activity

4. Developer Utility Packages

   1. Chapter 14 PLVtmr: Analyzing Program Performance

      1. Toggling the Timer
      2. Capturing the Start Time
      3. Retrieving and Displaying the Elapsed Time
      4. Using PLVtmr in Scripts

   2. Chapter 15 PLVvu: Viewing Source Code and Compile Errors

      1. Compiling PL/SQL Code in SQL*Plus
      2. Displaying Compile Errors
      3. Displaying Source Code
      4. Implementing PLVvu

   3. Chapter 16 PLVgen: Generating PL/SQL Programs

      1. Best PracticesOptions for Best Practices
      2. Code Generated by PLVgen
      3. Modifying PLVgen Behavior
      4. Implementing PLVgen

   4. Chapter 17 PLVhlp: Online Help for PL/SQL Programs

      1. Who Needs Online Help?
      2. Current Sources of Information
      3. What Is “Online Help” for Stored Code?
      4. Using PLVhlp
      5. Implementing PLVhlp

   5. Chapter 18 PLVcase and PLVcat: Converting and Analyzing PL/SQL Code

      1. PLVcase: Converting the Case of PL/SQL Programs
      2. PLVcat: Cataloguing PL/SQL Source Code

5. Plug-and-Play Packages

   1. Chapter 19 PLVdyn and PLVfk: Dynamic SQL and PL/SQL

      1. About Plug-and-Play
      2. Declarative Programming in PL/SQL
      3. The Dynamic Packages of PL/Vision
      4. PLVdyn: A Code Layer over DBMS_SQL
      5. DML Operations
      6. PLVfk: Generic Foreign Key Lookups

   2. Chapter 20 PLVcmt and PLVrb: Commit and Rollback Processing

      1. PLVcmt: Enhancing Commit Processing
      2. PLVrb: Performing Rollbacks

   3. Chapter 21 PLVlog and PLVtrc: Logging and Tracing

      1. PLVlog: Logging Activity in PL/SQL Programs
      2. PLVtrc: Tracing Execution of PL/SQL Programs

   4. Chapter 22 PLVexc: Exception Handling

      1. The Challenge of Exception Handling
      2. Application-Specific Exception Packages
      3. Implementing PLVexc

6. Testing Your Knowledge

   1. Appendix Appendix: PL/SQL Exercises

      1. Exercises
      2. Solutions

1. Colophon

Our look is the result of reader comments, our own experimentation, and feedback from distribution channels. Distinctive covers complement our distinctive approach to technical topics, breathing personality and life into potentially dry subjects. A moth is featured on the cover of Advanced Oracle PL /SQL: Programming with Packages. Moths, along with butterflies, make up the insect order Lepidoptera, the second largest order of insects. Their wings are largely or entirely covered in tiny, overlapping scales. Moths, unlike butterflies, are mostly nocturnal, yet they tend to fly toward light. An old superstition holds that white moths fluttering around lights in the evening are actually the souls of the recently dead.

The long coil seen on the head of the moth on this cover is the proboscis, a hollow tube through which it sucks up food. The proboscis is actually formed of two rounded tubes joined together by a third tube. When the moth is feeding blood flows through the two outer tubes, forcing the proboscis to straighten. When feeding is done the blood stops flowing to these tubes, allowing the proboscis to coil up and out of the way. All moths and butterflies have such a feeding "tube." Most moths feed exclusively on nectar, preferring flowers that are white or green and that can be seen at night. Their feeding activities make moths and butterflies extremely efficient pollenizers. Some moths, however, have developed proboscises that are strong enough to penetrate fruits. UNIX and its attendant programs can be unruly beasts. Nutshell Handbooks help you tame them.

Edie Freedman designed this cover and the entire UNIX bestiary that appears on the Nutshell Handbooks. The beasts themselves are adapted from 19th-century engravings from the Dover Pictorial Archive. The cover layout was produced with Quark XPress 3.3 using the ITC Garamond font.

The inside layout was designed by Nancy Priest and implemented in FrameMaker 5.0 by Mike Sierra. The text and heading fonts are ITC Garamond Light and Gara mond Book. The illustrations that appear in the book were created in Macromedia Freehand 5.0 by Chris Reilley. This colophon was written by Clairemarie Fisher O'Leary.