Expert PL/SQL Practices – Review

I’ve spent the past few train trips to work reading Expert PL/SQL Practices so I thought I’d post a short review of my impressions.

Generally speaking I’m somewhat wary of books that have chapters by different authors. The change of writing style and lack of continuity across the chapters prevents me from “getting into” the book. However, I’m pleased to say that this book is an exception and I found it to be an engrossing read.

Expert PL/SQL Practices covers quite a range of topics. Furthermore, it does not limit itself to covering the programming features of PL/SQL and delves into general development practices within the context of PL/SQL. In this regard it is quite different to other PL/SQL books.

Regarding pure PL/SQL features, there are chapters on set-based processing through SQL, dynamic SQL, bulk operations using PL/SQL, cursors and calling PL/SQL from within SQL. In what’s become the standard for Oracle database books, all chapters go to lengths to explain not only the features themselves but demonstrate why they should be used. Autotrace and TKProf snippets abound to show precisely the advantages of the features.

Complementing the PL/SQL features chapters are chapters on things like unit testing, code analysis, contract-oriented programming, performance profiling, coding standards and more. These are topics that may be found in general programming books but do not often make it into Oracle books. These chapters cover things that Oracle developers need to know beyond the simple application of the PL/SQL language features.

Exactly as the title conveys, Expert PL/SQL Practices is aimed at PL/SQL developer looking to write better code. It’s assumed you are already familiar with the language so it isn’t suitable for novices starting out.

I fully acknowledge that perhaps some of my enthusiasm for this book is based on my approval of the matter presented. I regularly found myself nodding in agreement and only rarely shaking my head in disagreement. My experiences are that too many PL/SQL programmers are unaware of good programming practices so I would rate this book as “highly recommended”.


As mentioned in my previous post, my preferred strategy for setting the module and action settings is to store the current values, set the new values and, once my process finishes its tasks, restore the previous values.

A small package with a simple interface to perform these tasks is required. So, the list of functional requirements are:

  • Set the module value, saving the existing value
  • Set the action value, saving the existing value
  • Restore the module value, overwriting the current value with the previous value
  • Restore the action value, overwriting the current value with the previous value
  • Retrieve the current module value
  • Retrieve the current action value

Additionally, I’ll also specify:

  • For familiarity reasons, the interface should resemble the interface that DBMS_APPLICATION_INFO provides

Thinking about the requirements it would appear to me that the solution is crying out for a traditional stack; push the current setting onto the stack, change the setting and pop off the previous value when restoring it.

The package pkg_app_info implements this. The exposed routines match the requirements listed above; set, get and restore for both module and action values. In a manner similar to DBMS_APPLICATION_INFO, the set module routines can also be used to set the action. There is one additional routine and that’s a function that returns the level to which settings are stored, i.e. how many are pushed onto the stack. This routine was very useful for testing purposes…

Behind the scenes in the package body things are quite simple too. Rather than implement two stacks, one for module values and one for action values, both are combined into a single record and a stack of this record type is implemented. So, whenever the module or action values are changed then both current values are pushed onto the stack. Restoring either action or module is a matter of restoring both values.

Since we do not know how many items we may need to push onto the stack, the stack is implemented as a nested table. Handling the nested table is the only area of complexity in the code. When an item is popped off the stack we have two options of how to handle it in our nested table; use the DELETE method or the TRIM method. The TRIM method removes the entry and also the placeholder in the nested table, whilst the DELETE method leaves the placeholder. The package uses the DELETE method and keeps track of the maximum number of items have been created in the table. The nested table only needs extending when the maximum stack depth needs increasing, not for every push operation.

Private package routines for push, pop and peek exist, which implement the traditional stack operations. Both the push and pop routines contain a call to another private routine that makes the call to update the Oracle module and action values.

Going back to the last code example in the previous post, let’s rewrite it using the new package. Other than calling the new package when setting the action we also need to restore the previous value on exit of the routines, via calls to pkg_app_info.restore_action. I’ve also inserted additional display lines between the calls to the various ETL routines to display what the module and action values are in the main routine.

The revised code looks like:


-- clear any existing settings for module and action
EXEC dbms_application_info.set_module (NULL,NULL)


   -- a routine that obtains the module and action and outputs them
   -- using dbms_output, along with a message
   PROCEDURE display (p_msg IN VARCHAR2)
      dbms_output.put_line (NVL(pkg_app_info.get_module,'<>') || ':'   || 
	                        NVL(pkg_app_info.get_action,'<>') || ' - ' || 
   END display;

   -- simple stubs that do nothing except set the action   
   PROCEDURE read_data
      pkg_app_info.set_action ('read_data');
      display ('Inside read_data');
   END read_data;   

   PROCEDURE clean_data
      pkg_app_info.set_action ('clean_data');
      display ('Inside clean_data');
   END clean_data;   

   PROCEDURE transform_data
      pkg_app_info.set_action ('transform_data');
      display ('Inside transform_data, before clean_data');
      display ('Inside transform_data, after clean_data');
   END transform_data;   

   PROCEDURE load_data
      pkg_app_info.set_action ('load_data');
      display ('Inside load_data');
   END load_data;   

   display ('Start');

   pkg_app_info.set_module (p_module_name => 'ETL Process'
                           ,p_action_name => NULL);


   display ('End');


The output we get is:

<>:<> - Start
ETL Process:read_data - Inside read_data
ETL Process:transform_data - Inside transform_data, before clean_data
ETL Process:clean_data - Inside clean_data
ETL Process:transform_data - Inside transform_data, after clean_data
ETL Process:load_data - Inside load_data
<>:<> - End

PL/SQL procedure successfully completed.

If we focus on the transform_data entries in the above listing we can see that the action was set to “transform_data” then set to “clean_data” on entry into that routine before being reset back to “transform_data” once the clean_data routine finished. In other words, the action setting always reflects the specific routine that was executing… exactly as we wanted.

Of course, where you would want to put the “set” and “restore” routines in your code is a matter of choice. So long as you keep in mind what the settings are intended for and have a clear strategy then you should be okay.

Download scripts shown in this post.