Advanced Pl Sql Programming (Nutshell Handbook)

Demonstrates how to construct and properly build PL/SQL packages, providing a full-use shareware version of PL/Vision and a library of dozens of packages written by the author on the companion disk. Original. (Advanced).

Dedication; Foreword; Preface; Objectives of This Book; Structure of This Book; Conventions Used in This Book; About the Disk; About PL/Vision; Comments and Questions; Acknowledgments; Working With Packages; Chapter 1: PL/SQL Packages; 1.1 What Is a PL/SQL Package?; 1.2 What Are the Types and Layers of Packages?; 1.3 What Are the Benefits of Packages?; 1.4 Using Packages; 1.5 Types of Packages; 1.6 Building Packages; Chapter 2: Best Practices for Packages; 2.1 Starting With Packages; 2.2 Using Effective Coding Style for Packages; 2.3 Selecting Package Names; 2.4 Organizing Package Source Code; 2.5 Constructing the Optimal Interface to Your Package; 2.6 Building Flexibility Into Your Packages; 2.7 Building Windows Into Your Packages; 2.8 Overloading for Smart Packages; 2.9 Modularizing for Maintainable Packages; 2.10 Hiding Package Data; 2.11 Simultaneous Construction of Multiple Packages; Chapter 3: The PL/SQL Development Spiral; 3.1 The Basic Problem; 3.2 Adding Value; 3.3 Supplying Backward Compatibility; 3.4 Improving the User Interface; 3.5 Rough Waters Ahead; 3.6 Building a Structured Function; 3.7 Handling Program Assumptions; 3.8 Broadening the Scope; 3.9 Considering Implementation Options; 3.10 Choosing the Best Performer; 3.11 Don’t Forget Backward Compatibility; 3.12 Obliterating the Literals; 3.13 Glancing Backward, Looking Upward; PL/Vision Overview; Chapter 4: Getting Started with PL/Vision; 4.1 What Is PL/Vision?; 4.2 packagesin PL/VisionPL/Vision Package Bundles; 4.3 installingPL/Vision LitePL/VisioninstallingInstallation Instructionss; 4.4 installingPL/Vision help packagespackageshelpdocumentationInstalling Online Help for PL/Vision; 4.5 documentationonline helpUsing Online Help; 4.6 SQL*Plusscripts included on diskfilescreated during installationcodecreated during installationSummary of Files on Disk; Chapter 5: PL/Vision Package Specifications; 5.1 Common Package Elements; 5.2 p packagep: a DBMS_OUTPUT Substitute; 5.3 PLV: Top-Level Constants and Functions; 5.4 PLVcase: PL/SQL Code Conversion; 5.5 PLVcat: PL/SQL Code Cataloguing; 5.6 PLVchr: Operations on Single Characters; 5.7 PLVcmt: Commit Processing; 5.8 PLVddd: DDL Syntax Dump; 5.9 PLVdyn: Dynamic SQL Operations; 5.10 PLVexc: Exception Handling; 5.11 PLVfile: Operating System I/O Manager; 5.12 PLVfk: Foreign Key Interface; 5.13 PLVgen: PL/SQL Code Generator; 5.14 PLVhlp: Online Help Architechture; 5.15 PLVio: Input/Output Processing; 5.16 PLVlex: Lexical Analysis; 5.17 PLVlog: Logging Facility; 5.18 listsPLVlst package PLVlst: List Manager; 5.19 PLVmsg: Message Handling; 5.20 PLVobj: Object Interface; 5.21 PLVprs: String Parsing; 5.22 PLVprsps: PL/SQL Source Code Parsing; 5.23 PLVrb: Rollback Processing; 5.24 PLVstk: Stack Manager; 5.25 PLVtab: Table Interface; 5.26 PLVtkn: Token Table Interface; 5.27 PLVtmr: Program Performance Analyzer; 5.28 PLVtrc: Trace Facility; 5.29 PLVvu: Code and Error Viewing; Building Block Packages; Chapter 6: PLV: Top-Level Constants and Functions; 6.1 NULLsubstitution valuesubstitution value, NULLNull Substitution Value; 6.2 Setting the PL/Vision Date Mask; 6.3 Assertion Routines; 6.4 PLV Utilities; 6.5 typesdatatypestypesThe Predefined Datatypes; 6.6 The Predefined Constants; Chapter 7: p: A Powerful Substitute for DBMS_OUTPUT; 7.1 p packagell procedureUsing the l Procedure; 7.2 delimiterline separatorline separatorwhitespaceline separatorcharactersline separatorblank lineswhitespaceThe Line Separator; 7.3 stringsoutput prefixI/Ooutput prefixoutput prefixThe Output Prefix; 7.4 p packageoutput fromoutput from p packageI/Op package outputControlling Output from p; Chapter 8: PLVtab: Easy Access to PL/SQL Tables; 8.1 typestableUsing PLVtab-Based PL/SQL Table Types; 8.2 display functionDisplaying PLVtab Tables; 8.3 togglestable formatheaders, tableShowing Header Toggle; 8.4 Showing Row Number Toggle; 8.5 prefixtable row valuedisplay prefixtablesrow value prefixSetting the Display Prefix; 8.6 empty tablesdeletingtable rowsEmptying Tables with PLVtab; 8.7 Implementing PLVtab.display; Chapter 9: PLVmsg: Single-Sourcing PL/SQL Message Text; 9.1 PLVmsg Data Structures; 9.2 savingmessage textStoring Message Text; 9.3 Retrieving Message Text; 9.4 The Restriction Toggle; 9.5 Integrating PLVmsg with Error Handling; 9.6 Implementing load_ from_dbms; Chapter 10: PLVprs, PLVtkn, and PLVprsps: Parsing Strings; 10.1 PLVprs: Useful String Parsing Extensions; 10.2 PLVtkn: Managing PL/SQL Tokens; 10.3 PLVprsps: Parsing PL/SQL Strings; Chapter 11: PLVobj: A Packaged Interface to ALL_OBJECTS; 11.1 Why PLVobj?; 11.2 ALL_OBJECTS View; 11.3 Setting the Current Object; 11.4 Accessing ALL_OBJECTS; 11.5 Binding Objects to a Dynamic Cursor; 11.6 Populating a PL/SQL Table with Object Names; 11.7 A Programmatic Cursor FOR Loop; 11.8 Tracing PLVobj Activity; Chapter 12: PLVio: Reading and Writing PL/SQL Source Code; 12.1 Why PLVio?; 12.2 Code Repositories Supported by PLVio; 12.3 Managing the Source Repository; 12.4 The Source WHERE Clause; 12.5 Managing the Target Repository; 12.6 Reading From the Source; 12.7 Writing to the Target; 12.8 Saving and Restoring Settings; 12.9 Cleaning Up Source and Target; Chapter 13: PLVfile: Reading and Writing Operating System Files; 13.1 A Review of UTL_FILE; 13.2 Specifying the File in PLVfile; 13.3 Creating and Checking Existence of Files; 13.4 Opening and Closing Files; 13.5 Reading From a File; 13.6 Writing to a File; 13.7 Copying File Contents; 13.8 Displaying File Contents; 13.9 Handling File Errors with PLVfile; 13.10 Tracing PLVfile Activity; Developer Utility Packages; Chapter 14: PLVtmr: Analyzing Program Performance; 14.1 Toggling the Timer; 14.2 Capturing the Start Time; 14.3 Retrieving and Displaying the Elapsed Time; 14.4 Using PLVtmr in Scripts; Chapter 15: PLVvu: Viewing Source Code and Compile Errors; 15.1 Compiling PL/SQL Code in SQL*Plus; 15.2 Displaying Compile Errors; 15.3 Displaying Source Code; 15.4 Implementing PLVvu; Chapter 16: PLVgen: Generating PL/SQL Programs; 16.1 Best PracticesOptions for Best Practices; 16.2 Code Generated by PLVgen; 16.3 Modifying PLVgen Behavior; 16.4 Implementing PLVgen; Chapter 17: PLVhlp: Online Help for PL/SQL Programs; 17.1 Who Needs Online Help?; 17.2 Current Sources of Information; 17.3 What Is “Online Help” for Stored Code?; 17.4 Using PLVhlp; 17.5 Implementing PLVhlp; Chapter 18: PLVcase and PLVcat: Converting and Analyzing PL/SQL Code; 18.1 PLVcase: Converting the Case of PL/SQL Programs; 18.2 PLVcat: Cataloguing PL/SQL Source Code; Plug-and-Play Packages; Chapter 19: PLVdyn and PLVfk: Dynamic SQL and PL/SQL; 19.1 About Plug-and-Play; 19.2 Declarative Programming in PL/SQL; 19.3 The Dynamic Packages of PL/Vision; 19.4 PLVdyn: A Code Layer over DBMS_SQL; 19.5 DML Operations; 19.6 PLVfk: Generic Foreign Key Lookups; Chapter 20: PLVcmt and PLVrb: Commit and Rollback Processing; 20.1 PLVcmt: Enhancing Commit Processing; 20.2 PLVrb: Performing Rollbacks; Chapter 21: PLVlog and PLVtrc: Logging and Tracing; 21.1 PLVlog: Logging Activity in PL/SQL Programs; 21.2 PLVtrc: Tracing Execution of PL/SQL Programs; Chapter 22: PLVexc: Exception Handling; 22.1 The Challenge of Exception Handling; 22.2 Application-Specific Exception Packages; 22.3 Implementing PLVexc; Testing Your Knowledge; Appendix: PL/SQL Exercises; Exercises; Solutions; Colophon;|

• Dedication
• Foreword
• Preface
• Working With Packages
  • Chapter 1: PL/SQL Packages
  • Chapter 2: Best Practices for Packages
  • Chapter 3: The PL/SQL Development Spiral
• PL/Vision Overview
  • Chapter 4: Getting Started with PL/Vision
  • Chapter 5: PL/Vision Package Specifications
• Building Block Packages
  • Chapter 6: PLV: Top-Level Constants and Functions
  • Chapter 7: p: A Powerful Substitute for DBMS_OUTPUT
  • Chapter 8: PLVtab: Easy Access to PL/SQL Tables
  • Chapter 9: PLVmsg: Single-Sourcing PL/SQL Message Text
  • Chapter 10: PLVprs, PLVtkn, and PLVprsps: Parsing Strings
  • Chapter 11: PLVobj: A Packaged Interface to ALL_OBJECTS
  • Chapter 12: PLVio: Reading and Writing PL/SQL Source Code
  • Chapter 13: PLVfile: Reading and Writing Operating System Files
• Developer Utility Packages
  • Chapter 14: PLVtmr: Analyzing Program Performance
  • Chapter 15: PLVvu: Viewing Source Code and Compile Errors
  • Chapter 16: PLVgen: Generating PL/SQL Programs
  • Chapter 17: PLVhlp: Online Help for PL/SQL Programs
  • Chapter 18: PLVcase and PLVcat: Converting and Analyzing PL/SQL Code
• Plug-and-Play Packages
  • Chapter 19: PLVdyn and PLVfk: Dynamic SQL and PL/SQL
  • Chapter 20: PLVcmt and PLVrb: Commit and Rollback Processing
  • Chapter 21: PLVlog and PLVtrc: Logging and Tracing
  • Chapter 22: PLVexc: Exception Handling
• Testing Your Knowledge
  • Appendix: PL/SQL Exercises
• Colophon