Advanced Oracle PL/SQL

Steven Feuerstein has been having fun with Oracle software since 1987, but nothing prepared him for the Joy of PL/SQL. When not in ecstasies over his latest package creation, Steven is codirector of the Oracle Practice for SSC, a systems management consulting firm based in Chicago. He is also a senior technology officer for RevealNet and coauthor of the Reveal for PL/SQL knowledge base. Finally, Steven is the author of RevealNet's PL/Vision, the first third-party software library for PL/SQL. Steven shares his Rogers Park, Chicago, Georgian with his wife, Veva, his youngest son, Eli, two cats (Sister Itsacat and Moshe Jacobawitz), Mercury the Congo Red African Gray parrot, and the occasional carpenter ant (which Steven would be happy to name, but in ways thoroughly unprintable). He is a member of the Board of Directors of the Crossroads Fund, which provides grants to organizations in Chicago working for social change. Steven is the author of Oracle PL/SQL Programming (O'Reilly & Associates, 1995).

Foreword Preface I: Working With Packages 1: PL/SQL Packages What Is a PL/SQL Package? What Are the Types and Layers of Packages? What Are the Benefits of Packages? Using Packages Types of Packages Building Packages 2: Best Practices for Packages Starting With Packages Using Effective Coding Style for Packages Selecting Package Names Organizing Package Source Code Constructing the Optimal Interface to Your Package Building Flexibility Into Your Packages Building Windows Into Your Packages Overloading for Smart Packages Modularizing for Maintainable Packages Hiding Package Data Simultaneous Construction of Multiple Packages 3: The PL/SQL Development Spiral The Basic Problem Adding Value Supplying Backward Compatibility Improving the User Interface Rough Waters Ahead Building a Structured Function Handling Program Assumptions Broadening the Scope Considering Implementation Options Choosing the Best Performer Don't Forget Backward Compatibility Obliterating the Literals Glancing Backward, Looking Upward II: PL/Vision Overview 4: Getting Started with PL/Vision What Is PL/Vision? PL/Vision Package Bundles Installation Instructions Using Online Help Summary of Files on Disk 5: PL/Vision Package Specifications Common Package Elements p: a DBMS_OUTPUT Substitute PLV: Top-Level Constants and Functions PLVcase: PL/SQL Code Conversion PLVcat: PL/SQL Code Cataloguing PLVchr: Operations on Single Characters PLVcmt: Commit Processing PLVddd: DDL Syntax Dump PLVdyn: Dynamic SQL Operations PLVexc: Exception Handling PLVfile: Operating System I/O Manager PLVfk: Foreign Key Interface PLVgen: PL/SQL Code Generator PLVhlp: Online Help Architechture PLVio: Input/Output Processing PLVlex: Lexical Analysis PLVlog: Logging Facility PLVlst: List Mananger PLVmsg: Message Handling PLVobj: Object Interface PLVprs: String Parsing PLVprsps: PL/SQL Source Code Parsing PLVrb: Rollback Processing PLVstk: Stack Manager PLVtab: Table Interface PLVtkn: Token Table Interface PLVtmr: Program Performance Analyzer PLVtrc: Trace Facility PLVvu: Code and Error Viewing III: Building Block Packages 6: PLV: Top-Level Constants and Functions Null Substitution Value Setting the PL/Vision Date Mask Assertion Routines PLV Utilities The Predefined Datatypes The Predefined Constants 7: p: A Powerful Substitute for DBMS_OUTPUT Using the l Procedure The Line Separator The Output Prefix Controlling Output from p 8: PLVtab: Easy Access to PL/SQL Tables Using PLVtab-Based PL/SQL Table Types Displaying PLVtab Tables Showing Header Toggle Showing Row Number Toggle Setting the Display Prefix Emptying Tables with PLVtab Implementing PLVtab.display 9: PLVmsg: Single-Sourcing PL/SQL Message Text PLVmsg Data Structures Storing Message Text Retrieving Message Text The Restriction Toggle Integrating PLVmsg with Error Handling Implementing load_ from_dbms 10: PLVprs, PLVtkn, and PLVprsps: Parsing Strings PLVprs: Useful String Parsing Extensions PLVtkn: Managing PL/SQL Tokens PLVprsps: Parsing PL/SQL Strings 11: PLVobj: A Packaged Interface to ALL_OBJECTS Why PLVobj? ALL_OBJECTS View Setting the Current Object Accessing ALL_OBJECTS Binding Objects to a Dynamic Cursor Populating a PL/SQL Table with Object Names A Programmatic Cursor FOR Loop Tracing PLVobj Activity 12: PLVio: Reading and Writing PL/SQL Source Code Why PLVio? Code Repositories Supported by PLVio Managing the Source Repository The Source WHERE Clause Managing the Target Repository Reading From the Source Writing to the Target Saving and Restoring Settings Cleaning Up Source and Target 13: PLVfile: Reading and Writing Operating System Files A Review of UTL_FILE Specifying the File in PLVfile Creating and Checking Existence of Files Opening and Closing Files Reading From a File Writing to a File Copying File Contents Displaying File Contents Handling File Errors with PLVfile Tracing PLVfile Activity IV: Developer Utility Packages 14: PLVtmr: Analyzing Program Performance Toggling the Timer Capturing the Start Time Retrieving and Displaying the Elapsed Time Using PLVtmr in Scripts 15: PLVvu: Viewing Source Code and Compile Errors Compiling PL/SQL Code in SQL*Plus Displaying Compile Errors Displaying Source Code Implementing PLVvu 16: PLVgen: Generating PL/SQL Programs Options for Best Practices Code Generated by PLVgen Modifying PLVgen Behavior Implementing PLVgen 17: PLVhlp: Online Help for PL/SQL Programs Who Needs Online Help? Current Sources of Information What Is "Online Help" for Stored Code? Using PLVhlp Implementing PLVhlp 18: PL/SQL Code 469 PLVcase: Converting the Case of PL/SQL Programs PLVcat: Cataloguing PL/SQL Source Code V: Plug-and-Play Packages 19: PLVdyn and PLVfk: Dynamic SQL and PL/SQL About Plug-and-Play Declarative Programming in PL/SQL The Dynamic Packages of PL/Vision PLVdyn: A Code Layer over DBMS_SQL DML Operations PLVfk: Generic Foreign Key Lookups 20: PLVcmt and PLVrb: Commit and Rollback Processing PLVcmt: Enhancing Commit Processing PLVrb: Performing Rollbacks 21: PLVlog and PLVtrc: Logging and Tracing PLVlog: Logging Activity in PL/SQL Programs PLVtrc: Tracing Execution of PL/SQL Programs 22: PLVexc: Exception Handling The Challenge of Exception Handling Application-Specific Exception Packages Implementing PLVexc VI: Testing Your Knowledge Appendix: PL/SQL Exercises Exercises Solutions Index