Advanced Oracle PL/SQL Programming with Packages

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;