Oracle PL/SQL Built-ins Pocket Reference

Michael J. Ross

This book review was published by SDOUG, .

Oracle PLSQL Built-ins Pocket Reference

Like most major relational database systems, Oracle allows the developer to encapsulate database logic in procedures and functions (sometimes called "programs" or "named code blocks"), which themselves can be grouped into packages. In turn, Oracle has a great many procedures and functions built into the system, and usable directly from within PL/SQL programs written by the programmer. These built-in programs offer several advantages over creating the same code from scratch, including the extensive verification that has been done on that code (in the form of testing by Oracle engineers, and the usage of Oracle programmers out in industry), the ability of separate projects to standardize upon functionality with a published and stable API, and the avoidance of re-coding functionality that has already been created and tested by others. Yet this last advantage will likely be lost if the developer is unable to quickly locate the built-in program before losing patience and resorting to writing that code themselves — and thus reinventing the wheel, or a (likely inferior) variation thereof.

What the PL/SQL developer needs is a handy quick reference that lists all of the most commonly used built-in packages and their programs. The best candidate to fill that role is a slim volume published by O'Reilly, entitled Oracle PL/SQL Built-ins Pocket Reference, and authored by Steven Feuerstein, John Beresniewicz, and Chip Dawes. This book, published on 20 October 1998 under the ISBN 978-1565924567, covers Oracle7 and Oracle8. The contents are organized into four major sections: built-in packages, built-in functions, RESTRICT REFERENCES pragmas, and non-program elements. The section on the built-in packages is preceded by the book's conventions and a brief introduction, in which the authors note that they have indicated in the text where a particular package, "program" (presumably procedure), or function is supported only by a particular version of Oracle. This is a welcome practice, as many authors of technical volumes frequently neglect to make clear which version of the product supports each element of it that they are describing.

The bulk of this non-bulky book comprises the section listing the 24 most commonly needed PL/SQL built-in packages (in the opinion of the authors, of course). Unfortunately, this is the only place in the book where it is made clear that not all of the available PL/SQL built-ins are covered by this book. It would have been helpful had the authors mentioned that at the beginning of each section, if applicable, and, more importantly, if they had provided advice as to where the developer could look to find all of the built-ins not discussed by the authors. The chosen packages are:

For each built-in package, the authors provide a brief summary of the package, and all of the publicly visible procedures and functions within that package. For each one of those programs, the declaration is listed, followed by a brief summary as to the purpose of that particular program. No examples are given, undoubtedly as a result of space limitations imposed upon any pocket reference covering a rich topic such as this.

The second-largest section covers the stand-alone PL/SQL built-in functions, which have been implemented in the STANDARD built-in package, and thus can be called without making reference to the package name (unlike the programs in the built-in packages discussed above, except DBMS_STANDARD). These built-in functions can be organized into six groups:

The third section of the book discusses the RESTRICT REFERENCES pragmas, which are used to inform the PL/SQL compiler as to a package program's "purity level", i.e., the degree to which it is free from side effects. Oracle makes available four purity levels: write or read no database or package state. These apply to the programs within nine PL/SQL packages:

For each one of these packages, a table shows which of the four purity levels apply to each one of the programs within the package.

This pocket reference concludes by listing the non-program elements (constants, exceptions, record types, index-by tables, etc.) of 15 built-in PL/SQL packages:

This information clearly should have been located in the book's first section, so that the program and non-program elements of each package could be found in the same location. There is no apparent reason as to why this artificial split into separate sections could be beneficial to the reader. The authors certainly do not provide any justification for doing so.

Aside from the aforesaid and unhelpful division of program and non-program information, this title is to be recommended to any Oracle PL/SQL developer, including the developer who is not using — or has no plans for using — any of the built-in packages made available to them. In fact, this reference volume, combined with the companion book Oracle Built-in Packages, is even more strongly recommended to such a developer — to encourage them to utilize existing functionality and to avoid re-creating it from scratch. The software projects of the world are littered with far too many cases of source code implementing the same or similar capabilities that have already been developed and fully tested by other programmers years earlier — and in some cases decades earlier. There is simply no excuse for such inefficient use of development time and effort. For the PL/SQL programmer, the built-in packages are there to be used to one's advantage.

Copyright © 2003 Michael J. Ross. All rights reserved.
bad bots block