Oracle PL/SQL Best Practices
Examples

[ Back to: Oracle PL/SQL Best Practices ]

The following is a list of the files containing code examples for Oracle PL/SQL Best Practices. They are arranged by best practice number within each chapter. Please note that you cannot, as specified in the book, type in a best practice such as "STYL-09" and go right to the code. Instead, you will find all of the files contained in the examples.zip file. You'll also find a text file, filelist.txt, that contains the following reference list. So download the file, find the code you want on this page, then grab that file from the archive.

Chapter 1

• 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

• 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

• 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 4

• plsqlloops.pro: Script to compare the performance of several alternatives to scanning a collection.

• myCollection.pkg: Implementation of a utility package that displays the cont ents of a collection and compares the contents of two collections.

CTL-09

• insql.sql: A script to compare the performance of functions in SQL versus PL/SQL.

Chapter 5

• 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

• 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

• 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

• 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

• 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.

[ Back to: Oracle PL/SQL Best Practices ]