Oracle PL/SQL Best Practices By Steven Feuerstein List of Files Containing Code Examples for the Book (arranged by best practice number within chapter) Chapter 1 DEV-04 reftabs.sql : Query identifying direct references to tables and views. valstd.pkg : Simple prototype package offering an interface to identify the presence of unwanted text in source code. DEV-05 genlookup.pro: Generates a lookup procedure that returns a row in a table. msginfo.pkg: Generates a package with definitions for all application-specific exceptions. genmods.pkg: Generates standard formatted functions. Chapter 2 STYL-02 standards.doc: An unfinished draft of some naming and coding standards for PL/SQL developers; be sure to review and edit this document before using in your organization. standards.zip: An HTML-driven comprehensive guide to a set of naming conventions for PL/ SQL code (courtesy of Matthew MacFarland). STYL-03 stdhdr.pkg: A prototype "standard header" package that generates a standard header (with an XML-style format) and offers programs to query such headers from stored code. Chapter 3 DAT-11 bool.pkg: A package to convert between Booleans and strings, since Oracle doesn't offer any built-in utilities to do this. DAT-13 colltype.pks: A package specification of standard collection TYPE definitions. DAT-15 overdue.pkg: The overdue package. p_and_l.pkg and watch.pkg: Demonstration of "watching" a variable. Chapter 3 CTL-08 plsqlloops.pro: Script to compare the performance of several alternatives to scanning a collection. myCollection.pkg: Implementation of a utility package that displays the contents of a collection and compares the contents of two collections. Chapter 4 CTL-09 insql.sql: A script to compare the performance of functions in SQL versus PL/SQL. Chapter 5 EXC-01 assert.pro: A simple assertion procedure assert.pkg: An assertion package that offers assertions for different conditions EXC-04 err.pkg: A simple, but functional prototype of a generic error-handling package. EXC-06 excquiz6.sql and excquiz6a.sql: Demonstrations of how you can transform a single, overused exception such as NO_DATA_FOUND into multiple, distinct exceptions. EXC-08 err.pkg: A simple, but functional prototype of a generic error-handling package. EXC-09 msginfo.pkg: Infrastructure package and associated table to manage error numbers and text, and to generate a package with named exceptions. EXC-10 callstack.sql: Contains the error package and a demonstration package containing a dump procedure. EXC-12 sqlerr.pks: Package of predefined exceptions that commonly occur when working with SQL, and especially dynamic SQL, inside PL/SQL. EXC-14 sqlerr.pks: Package of predefined exceptions that commonly occur when working with SQL, and especially dynamic SQL, inside PL/SQL. Chapter 6 SQL-03 log.pkg and log.tst: A simple logging package that uses autonomous transactions, and a companion script you can use to test the functionality. SQL-05 nextseq.sf: A function that uses dynamic SQL to offer a single function that retrieves the nth NEXTVAL from any sequence you specify. SQL-08 atleastone.sql: A SQL*Plus script comparing different approaches to answering the question "Is there at least one employee in department 20?" SQL-10 explimpl.pkg and explimpl.sql: Scripts that compare the performance of cursor FOR loops to other fetching methods for a single row. SQL-11 forupdate.sql: Contains the code for the example in this section. SQL-13 returning.tst: A script comparing the performance of INSERT-SELECT to INSERT-RETURNING. SQL-15 te_employee.pks and te_employee.pkb: Examples of the specification and body of a table encapsulation package. SQL-18 bulktiming.sql: A script to compare performance of row-by-row DML and FORALL-based DML. SQL-19 openprse.pkg: A package that allocates new DBMS_SQL cursors only when necessary, and displays SQLERRM and the SQL string if a parse error occurs. SQL-20 updnval2.pro: Implementation of the updnumval program using concatenation so that you can compare the complexity of the implementations. effdsql.tst: A script that allows you to compare performance of repetitive parsing using concatenation with a single parse that relies on binding instead. SQL-21 genlenpkg.pro: A program that generates the column length package for the specified table (VARCHAR2 columns only). Here's an example of the output from the genlenpkg procedure: SQL-22 runddl.pro and runddl81.pro: Generic DDL engine in both DBMS_SQL and NDS. Chapter 7 MOD-02 template.fun and template.pro: Function and procedure template files. genmods.pkg: A simple prototype of a function generator. MOD-04 namednot.sql: A file that demonstrates the different ways you can use named and positional notation to invoke a procedure. MOD-06 pkgvar.pkg and pkgvar.tst: A package and test script to both demonstrate the globalization technique and test its performance impact. nocopy.tst, nocopy2.tst, and nocopy3.tst: Examples of scripts that examine the impact of the NOCOPY statement. MOD-07 genmods.pkg: A simple prototype of a function generator. MOD-09 isvalidisbn.fun: This file contains the two implementations described in the example MOD-10 genmods.pkg: The genmods.use_new and genmods.use_old procedures within this package generate procedure calls that "explode" the pseudo-records into individual arguments (one per column) that can be passed to stored programs. Here's an example session: MOD-11 multiple_triggers.sql: Contains a detailed working version of the example. trigger_conflict.sql: A simple query against the USR_TRIGGERS data dictionary view that helps you identify potentially conflicting triggers. MOD-12 instead_of_nothing.sql: Contains a complete example of handling the situation versus not handling the situation. MOD-14 always_use_ora.sql: Contains the preceding example. Chapter 8 PKG-01 te_employee.pks and te_employee.pkb: Table encapsulation packages feature "high cohesion" (grouping together of related programs). Such packages offer a set of procedures and functions that allow a developer to manipulate the underlying data structure (table or view) without writing any explicit SQL. xfile.pkg: The xfile class (built on top of the JFile Java class) offers "one stop shopping" for all file-related processing in a PL/SQL environment. PKG-02 tmr.pkg: The simplest version of the timer package PLVtmr.pkg: A more complete implementation tmr81.ot: An object-based timer PKG-04 watch.pkg: This package, used to trace program execution, offers the ability to switch output between the screen and database pipes. PKG-05 overdue.pkg: The overdue package watch.pkg: A watch package used to perform tracing PKG-06 init.pkg and init.tst: An example package and script to compare the performance of caching a record of data. emplu.pkg and emplu.tst: An example package and script to compare the performance of caching multiple rows of data. PKG-07 log.pkg and log.tst: The log package and test script that demonstrate the techniques you need for this best practice. PKG-10 te_book.pkg: The table encapsulation package for the book table (well, just the INSERT functionality of such a package). PKG-12 template.pks and template.pkb: Template files, one for the package specification and one for the body. Chapter 9 BIP-01 pl.sp and bpl.sp: Standalone procedure implementations; these are used through-out the book in place of DBMS_OUTPUT.PUT_LINE. watch.pkg: A generalized trace package with the ability to send output to a screen or database pipe. BIP-02 JFile.java and xfile.pkg: The Java-enhanced file I/O package for PL/SQL, along with the required Java class. BIP-03 utlfile.tst: A simple script to test the ability to read and write files. BIP-04 utlflexc.sql: A template of code containing a local error-handling procedure and an exception section for use with UTL_FILE. BIP-05 getnext.sp: The get_next_line procedure replaces UTL_FILE.GET_LINE. BIP-06 fdir.pkg and fdir.tst: A package that allows you to define directories in a table based on development phase and application, and then open files without hardcoding the directory location. There is also an accompanying test script. filepath.pkg: An encapsulation of UTL_FILE.FOPEN that adds support for a user-specified path (it can only be used to open files in Read mode). valstd.pkg: A general (and simple) standards validation package that searches ALL_ SOURCE for the specified string and reports on those programs that contain the string. BIP-07 pe_book.pkg: The full implementation of the pipe encapsulation package for the book table. BIP-08 pe_book.pkg: The full implementation of the pipe encapsulation package for the book table. BIP-09 pe_book.pkg: The full implementation of the pipe encapsulation package for the book table. BIP-10 myjob.pkg: A prototype package that demonstrates how to give a name to a job and then manage that job by name. BIP-11 myjob.pkg: A prototype package that demonstrates how to give a name to a job and then manage that job by name.