Useful PL/SQL Techniques

Oracle PL/SQL: Expert Techniques for Developers and Database Administrators, by Lakshman Bulusu, Course Technology, 2008, ISBN-13:978-1-58450-554-9, ISBN-10:1-58450-554-0, 307 pp.

In the introduction, author Lakshman Bulusu says his book will include “how-to” and "when-to” techniques from the real world of Oracle PL/SQL application development through Oracle 11g. My first thought was to wonder how he could do all this in 307 pages—given the much greater length of a similar, slightly older PL/SQL book on my shelf. But he did it. In fact, I’m looking forward to using this book on my next PL/SQL assignment.

Bulusu focuses on solutions to practical, real-world problems from his own experience. This narrows the topics to information that developers and database administrators are most likely to need in their daily work. The introductory information gives developers a good base to work from, as long as you already have a working knowledge of SQL and PL/SQL, and the book is organized to progress nicely through more complex information and code segments.

As a bonus, the book includes a CD with the source code for the chapter examples. The author claims that the source code is bug-free and that all but the “code snippets” can be compiled and executed. In addition, book owners have access to the book’s website (http://edu.cengage.co.uk/instructors/product.aspx?isbn=1584505540) to access updates and other information regarding the CD contents.

My review is organized in three sections that correspond to the book's organization.

Introductory Concepts

The author first distinguishes PL/SQL from SQL. The latter is a nonprocedural, fourth-generation language (4GL), while PL/SQL is a procedural, third-generation language (3GL) that’s important to Oracle server-side programming. When you need to develop Oracle applications on the database and/or application tiers, PL/SQL will be the language of choice.

Bulusu gives pointers on when to use or not use PL/SQL, then briefly introduces the categories of PL/SQL techniques he presents in the book’s second section. In addition to describing the parts of a PL/SQL program, the architecture of its processing, the debugging methods, memory usage, and metadata, the introductory chapters also outline new features/enhancements found in Oracle 10g and Oracle 11g. These outlines were particularly interesting to me because 10g is the latest version I’ve used. I feel ready to give Oracle 11g a try after reading about the “cool and much-wanted new features.”

PL/SQL Technique Categories

Six categories of PL/SQL techniques are covered thoroughly in the book’s second section: data structure management, error management, data management, application management, and transaction management.

Data structures discussed and illustrated in code examples include types such as records, objects, associative arrays, nested tables, and VARRAYS (variable-size arrays). In addition to examples and explanations, Bulusu describes things to be aware of as you make your choices among the various types. For example, the number of database hits (which adds overhead), using nested tables with a specific search value to perform data I/O (which is inefficient), and the need to remember the upper limit when populating VARRAYS.

The chapter on error management highlights the importance of having your PL/SQL code handle compile-time and runtime errors. This requires trapping the error and providing error information to the calling environment. Information that results from trapping the error must be complete enough for the developer to identify and correct any problem found in the source code. The code examples show complete error data display/output. They should be useful to most PL/SQL developers, and they definitely gave me ideas I plan to use at the next opportunity.

The data management techniques focus on manipulating and retrieving data from the database. They include methods for processing arrays using either static or dynamic SQL. Bulusu explains various data retrieval and processing techniques with examples of ensuring that error information is captured as well. He describes ways to improve performance and includes “Tips” related to the topic, including what you can do, what you cannot do, or what you should be aware of when utilizing a particular technique.

Oracle PL/SQL application structures refer to functions, procedures, triggers, and packages. Each of these has a “best use.” As a developer of PL/SQL packages, I found Bulusu’s discussion and examples of packages and their use for “globalizing data and code, localizing data and privatizing code” most informative.

The transactions that PL/SQL code processes must be managed to guard database integrity. At the most basic level, a good transaction should be saved to the database while an erroneous one should not. Part of this process must include efficient error handling, and Bulusu provides examples to illustrate error logging as part of the package operation. He also covers techniques for auditing the queries to a database. The enactment of Sarbanes-Oxley has required much more attention to auditing database activity.

PL/SQL in Your World

The term “application development frameworks” had not been well defined for me until reading this book. Bulusu sums it up nicely in one sentence: “Application development frameworks provide for a standard template for implementing a particular task.” The tasks he discusses here are performance tuning, auditing, error logging, and the extract, transform, load tasks collectively referred to as ETL. Each explanation is clear and accompanied by code examples and tips.

Bulusu also describes using PL/SQL in a non-Oracle scenario, which typically involves Java, C, C++, and C#. He keeps the topics on target by limiting the discussions of methods to what has been found to work best for each scenario.

The final chapters cover tuning and debugging, coding standards, and miscellaneous information such as pipelined functions and autogenerating code.

This book will definitely be in my PL/SQL tool set! There are other books that include many of the topics covered here, plus more. However, Lakshman Bulusu provides application insights from his own experience that are found nowhere else, especially in his descriptions of what doesn’t work. The explanations are to-the-point, and they’re followed by working code as well as tips and guidance on best practices.

I recommend using this book/CD as a supplement to more extensive documentation on Oracle and PL/SQL. It will be useful to students and a handy reference for experienced practitioners.

Caroline Pepa is a software engineer with Tybrin Corp. Contact her at caroline.pepa@tybrin.com.