Oracle PL/SQL Best Practices

About the Code

The best way to learn how to write good code is by analyzing and following examples. Almost every best practice offered in this book includes a code example, both in the text and in downloadable form from the Oracle PL/SQL Best Practices site, available through the O’Reilly & Associates site at:

http://www.oreilly.com/catalog/orbestprac

To locate the code for your best practice, simply enter the best practice identifier, such as BIP-10, in the search field. You will then be directed to the associated code. You can also browse the site by topic area. You can even offer your own insights about PL/SQL best practices, so I encourage you to visit and contribute.

As a rule, I will follow my own best practices in these examples (unless the point of the code is to demonstrate a “bad practice”!). So, for example, you will rarely see me using DBMS_OUTPUT.PUT_LINE, even though this “show me” capability is needed in many programs. As I mention in [BIP-01: Avoid using the DBMS_OUTPUT.PUT_LINE procedure directly.], you should avoid calling this procedure directly; instead, build or use an encapsulation over DBMS_OUTPUT.PUT_LINE. So rather than seeing code like this:

DBMS_OUTPUT.PUT_LINE (l_date_published);

you will instead encounter a call to the “pl” or “put line” procedure:

pl (l_date_published);

I also make many references to PL/Vision packages. PL/Vision is a code library, consisting of more than 60 packages that offer 1,000-plus procedures and functions to perform a myriad of useful tasks in PL/SQL applications. I have deposited much of what I have learned in the last five years about PL/SQL into PL/Vision, so I naturally return to it for examples. Any package mentioned in this book whose name starts with “PLV” is a PL/Vision package.

A completely free, “lite” version of PL/Vision is available from the PL/SQL Pipeline Archives at:

http://www.quest-pipelines.com/pipelines/dba/PLVision/plvision.htm

Select the “RevealNet Active PL/SQL Knowledge Base” from the list. (You might also like to download and try out the other code you’ll find there.) A commercial version of PL/Vision (with more packages and functionality than the lite version) is currently available inside the RevealNet Active PL/SQL Knowledge Base (http://www.revealnet.com).

Whenever possible, the code I provide for the book can be used to generate best-practice-based code and as prebuilt, generalized components in your applications, code that you can use without having to make any modifications.

The code examples offer programs that you can use to both generate and directly implement those best practices. In some cases, the programs are rather simple “prototypes”; they work as advertised, but you will probably want to make some changes before you put them into production applications.

And you should most certainly test every single program you use from Oracle PL /SQL Best Practices ! I have run some tests, and my wonderful technical reviewers have also exercised the code. In the end, however, if the code goes into your application, you are responsible for making sure that it meets your needs.