Reprinted with Permission by Quest Software Sept. 2006

Design Patterns In PL/SQL—Pre-Inventing the Wheel
by Lucas Jellema

Design Patterns for the Millions

Some of those Java upstarts may try to dazzle you with their frameworks, object orientation and most of all: their precious Design Patterns. Although sometimes these patterns are pretentious and hollow, more wrapping than gift, there is definitely a lot to be learned from some of these design patterns. And not just for Java programming: many patterns are applicable to the world of PL/SQL as well.

This article will show how we can improve the design of our PL/SQL applications by making use of some of the popular Java/J2EE design patterns. We will look at the Template Pattern and the Observer Pattern, as well as aspect-oriented programming (AOP) and see how they all apply to PL/SQL programming.

Note: All source code (and that is quite a bit) can be downloaded at: http://technology.amis.nl/blog.

The Template Pattern

Consider the following situation: you have received a reusable component from another department in your organization. It is a package that implements functionality that you really need. Unfortunately, although 90% of the package has a perfect fit with your requirements, the last 10% should be slightly different. What can you do?

PL/SQL packages cannot be extended and partially overridden, like Java Objects. So is there really an alternative to taking the source and adapt it to your own needs, thereby losing the link with the original component and the ability to smoothly adopt improvements in the base component?

Well, that is where the Template Pattern comes in, one of the Design Patterns made popular in the OO world, especially the world of Java. The Template Pattern is based on a Design Principle that states: “Packages should be open to extension, not to modification”, also see http://www.exciton.cs.rice.edu/JavaResources/DesignPatterns/TemplatePattern.htm.

The objective of the Template Pattern is to allow customization of the behavior of (reusable) components, without the risk of invalidating the overall algorithm. The general approach for implementing the Template Pattern: The core algorithm is hidden, cannot be changed. This algorithm calls out to helper procedures (hooks). These helper procedures are usually initially empty; they can be modified/implemented for customization purposes. In Java, the helper procedures are often abstract in the core class and need to be implemented in the subclasses, providing proper behavior.

In PL/SQL terms, we would implement the Template Pattern using two packages, one containing the core pattern and a companion package with the hooks:

Figure 1: PL/SQL implementation of the Template Pattern

The Core Package should not be touched. Users of the reusable component will invoke the core package, but should not look at its body. The core package will make calls to the helper procedures or hooks in the companion package. These procedures may be implemented/modified.

Example: the reusable component is the package price_calculations. This package provides us with a series of very complex order price calculation services. The component allows a certain extent of customization through its companion package. In this package, we can plugin our own discount calculation based on the business logic in our department with regard to the requested delivery date and the special arrangements we may have for calculating customer discounts. If we have such specific business logic, we can link it into the core price_calculations module by implementing the hook procedures apply_customer_discount and apply_delivery_date_surcharge in the companion package.

Figure 2: Price Calculations package implements the Template , the companion provides the hooks

Force Implementation of the Hook-Procedures

The Template Pattern can come in two flavors: one where implementing the hook-procedures is optional: you can influence the way the algorithm is executed, but you do not have to. And another one where implementing the hook-procedures is mandatory. You have to provide an implementation, otherwise the component cannot execute.
In Java, we would implement this using abstract methods. In the Core Class, we would have the hook methods and define them as abstract. This tells the Java compiler that in order to run the core algorithm, we need to extend the Core Class and provide implementations for each of the abstract hook methods. Non-abstract hook methods can be overridden, but do not have to be.

PL/SQL does not have the concept of abstract methods. So what can we do to enforce that the hook-methods in the companion package are implemented? One thing the developers of the reusable component can do, is deliver the component with a companion package that has invalid implementations of the hook-procedures.

Invalid in this case does not necessarily mean that they cannot be compiled – which is a somewhat crude approach. More subtle is to have the default implementation of the hook procedure specify a check parameter, an in out parameter that needs to be assigned a new value by the hook procedure. The core algorithm will invoke the hook procedure with a certain value for the check parameter and checks after the call is complete whether or not the hook has assigned a new value to the parameter. If not, the conclusion will be that hook procedure has not been implemented, as it should have been, and an exception is raised.

Template Pattern in Oracle Designer

In hindsight, I have seen an implementation of the Template Pattern in PL/SQL as far back as 1998, in the Oracle Designer product. The JR_VERSION package is one of the core packages, handling most of the version related operations that were introduced in Oracle Designer 6i, like Check Out, Check In, Merge and Branch. It is very much not supported to change this package.

However, the developers of Oracle Designer realized that organizations using the Oracle Designer 6i and Oracle SCM infrastructure might have a need for a more advanced approach to deriving version labels for checked in objects. They catered for this by providing a simple package jr_version_label. It contains functions like next and first with default implementations. And: users are free to change the implementation of the version label derivation implementation in this companion package. Also see figure 3.

Figure 3: Example of the Template Pattern from Oracle Designer

AOP – Aspect Oriented Programming

One of the hottest topics in Java programming right now is probably Aspect Oriented Programming. Made popular by frameworks such as AspectJ and Spring, AOP refers to a new way of approaching programming. AOP has to do with so called ‘concerns’: objectives or areas of interest. Many application components have a core, functional or business oriented concern. However, they are also subject to system-level, often more technical concerns, such as security, transaction management, performance, logging and auditing.

Traditionally, a developer would program a piece of code to address a core business concern. Then he would copy and paste statements for logging, debugging, security enforcement from an earlier developed unit. Those system-level concerns that more or less return in many program units are called ‘cross cutting concerns’.

AOP strives to developing the implementations of the cross cutting concerns – those are called aspects - more or less independently from the core concerns. Only after development is done are the cross-cutting concerns applied to the program units that implement the core concerns. The process of applying concerns to a module is called weaving.
The benefits we hope to reap from AOP are code reuse, more focused development, less complex code and therefore faster and less buggy development, very loose coupling between aspects and therefore more flexible maintenance (changing the implementation of one concern does not necessarily impact other concerns). See (http://www.javaworld.com/javaworld/jw-01-2002/jw-0118-aspect.html) for a more detailed introduction to AOP.
The weaving process – the merger of the various aspects - in Java can be done at three levels: compile time, class load time and runtime. For PL/SQL, there is no clear concept of class load time and redefinition at runtime if at all possible would probably be a performance nightmare. So the only viable option is to implement the application of concerns at compile time. Which is slightly less flexible – when any of the aspects change, we need to recompile the woven program units – than dynamic AOP, but certainly superior from a performance point of view.

Our hook into the PL/SQL compilation process is the AFTER CREATE system event trigger that Oracle allows us to define – first introduced in the 9i release. When the database is just done compiling a package, it can invoke the trigger code, allowing us to react to the compilation process and the source involved. When the AFTER CREATE trigger fires, a program unit has been compiled. We can get at the source, weave our cross cutting concern implementations in, and compile again – without activating the AOP processor again. Then, for the sake of our programmer, we need an easy way to remove the woven-in aspects to allow maintenance on the core code.

We cannot use an EXECUTE IMMEDIATE statement in the AFTER CREATE trigger, that is an illegal operation that would raise an ORA-30511 error. Instead, we submit a job from that trigger; the job will call the AOP_PROCESSOR package that will recompile the package after ‘advising’ i.e. weaving in the aspects that apply.

CREATE OR REPLACE TRIGGER aop_processor_trg
AFTER CREATE
ON SCHEMA
declare
  PRAGMA AUTONOMOUS_TRANSACTION;
  l_job number;
BEGIN
  if ora_dict_obj_type = ‘PACKAGE BODY’
     and
     not aop_processor.during_advise -- to prevent recursive AOP processing
  then
    dbms_job.submit
      ( JOB  => l_job
      , WHAT => ‘begin    
                   aop_processor.advise_package
                   ( p_object_name   => ‘'’||ora_dict_obj_name ||”’
                   , p_object_type   => ‘'’||ora_dict_obj_type ||”’
                   , p_object_owner  => ‘'’||ora_dict_obj_owner||”’
                   );
                 end;’
      );
      commit;
  end if;
END aop_processor_trg;

Note that this will mean a delay – typically a few seconds - between the time the developer is notified that the package is compiled and the moment at which the AOP processor has done its work. It may be convenient to only enable the AOP processor when the core concern is completed by the developer – for example when the package is checked in.

Figure 4: Aspect Oriented Programming (AOP) in PL/SQL: the After Create Trigger is fired after compilation of a PL/SQL package and invokes a job that applies aspects to the package

Examples of concerns we can weave into PL/SQL applications:

In this simplified example, the developer can stipulate through a simple AOP instruction -@AOP(advice=LOG) – that a specific procedure or function should be advised (have an aspect applied) with the LOG Aspect.

-- @AOP(advice=LOG)
function verify_salary_raise( p_job in varchar2, p_old_salary in number, p_new_salary in number)
return boolean
is
  begin
    ...

After compilation – and a small delay in which the Job will run – the advised function will have been modified to look like:

-- @AOP(advice=LOG)
function verify_salary_raise( p_job in varchar2, p_old_salary in number, p_new_salary in number)
return boolean
is
  begin
    -- AOP-ADVICE:LOG  ; Added by AOP_PROCESSOR on 02-03-2006 20:21:58
    dbms_output.put_line(’Call to HRM_SALARY_RULES.verify_salary_raise on ‘
                       ||to_char(systimestamp, ‘DD-MM-YYYY HH24:MI:SS’);
    -- END OF AOP-ADVICE
    ...

This is really a very crude way of implementing AOP – the LOG advice is hard-coded into the AOP Processor for starters - but it gives you an impression of what AOP could be like for PL/SQL coding.

Aspect Management

The implementation of Aspects is probably best done in packages, one Aspect per Package. Part of the package will be template code that is woven into packages that are advised with the Aspect, another part may be code that is invoked by the Advice.

When one of the Aspects changes – the implementation of a system concern – we need to make sure that the new implementation is woven into all advised packages. This probably best achieved by having the AOP Processor traverse all AOP enabled packages, find the ones that contain the changed Aspect and invoke the advise_package procedure for these packages. This will redo the weaving of aspects followed by a recompilation of all packages involved.

Figure 5: What if an Aspect changes? The AOP Processor revisits all packages

For example: we have somewhat extended our Log-Aspect. Instead of just writing a single line to dbms_output, we will now invoke a central logging package, passing the values of the input parameters. The AOP_Processor package is modified with the enhanced Aspect. Now we can reapply the Aspect through this call:

begin    
  aop_processor.reapply_aspect
  ( p_aspect   => 'LOG'
  );
end;

All procedures and functions in all our packages that we have made pointcuts for the LOG advise by tagging them with @AOP(advice=LOG) are now advised with code like:

  -- @AOP(advice=LOG)
  function verify_salary_raise( p_job in varchar2, p_old_salary in number, p_new_salary in number)
  return boolean
  is
  begin
    -- AOP-ADVICE:LOG  ; Added by AOP_PROCESSOR on 02-03-2006 22:29:18
    declare
      l_parameters CENTRAL_LOGGER.parameter_table;
    begin
      l_parameters('p_job'):= p_job;
      l_parameters('p_old_salary'):= p_old_salary;
      l_parameters('p_new_salary'):= p_new_salary;
      CENTRAL_LOGGER.log_program_unit_execution
      ( p_program_unit => 'HRM_SALARY_RULES.verify_salary_raise'
      , p_parameters => l_parameters
      );
    end;
    -- END OF AOP-ADVICE

Theoretically, you could even specify PointCuts – locations in the code where an aspect should be applied – external to the code. Without changing the source code of an application, we can instruct the PL/SQL compiler to insert aspects, thereby seriously interfering with the run-time behavior of such code. Needless to say, this will not work for wrapped packages, for which we cannot access the source code. However, for open products like Oracle Designer we can pretty easily weave aspects into the core repository packages.

You can download all source code for this PL/SQL implementation of AOP from http://technology.amis.nl/blog/?p=1083.

The Observer Pattern

Inside components in your application, whether you built them or someone else did, there may be many things going on. Attempts to update data, calculations, communications with external resources. You may have an interest in specific events inside such a component. You would like to be told about such events when they occur.

The Observer Patterns describes a one-to-many dependency between a subject object and any number of observer objects so that when the subject object changes state, all its observer objects are notified and updated automatically. The Observer pattern is also known as Dependents and Publish-Subscribe. Also see: http://sern.ucalgary.ca/courses/SENG/609.04/W98/lamsh/observerLib.html

The most explicit example of an implementation of the Observer Pattern in the Oracle Database is through the Database Triggers. You can easily register an interest in DML events that occur on a table, by creating a trigger for the event you are interested in. The database also supports system events that you can register an observer for, by creating triggers such as post-create, post-logon and pre-truncate.

You cannot register an observer for less explicit events – business events occurring inside your packages without additional effort. The database does not know about such events. So you need to publish these events to the interested parties yourself. The package is the only one who knows when the event occurs. However, the way interested observers are notified may differ. Let’s look at two ways to implement event publication and consumption. Note: on an even grander scale, we could use Advanced Queues as infrastructure for the events. For this discussion, it would be overkill.

Registering Observers directly with the Observable

One way of implementing the Observer Pattern – which is very close to the Java way – is to register observers – event consumers - with the package that generates the events, the observable. Whenever an event occurs, the package will call each of the registered observers, passing along the name and details of the event. How each observer handles the event is of no concern to the package.

A package can publish multiple types of events. Observers can register for a specific event-type or for all events.
We use two overloaded procedure to register observers - as well as a similar pair to unregister: Register_observer and register_observer(p_observer in varchar2).

In the first case, the object calling in will be registered itself – using the dbms_utility.format_call_stack whocalledme helper function; it will be assumed that the caller is a package that implements a notify() procedure with input parameters p_observable in varchar2 – the name of the package that sent the event – p_event_type in varchar2 and p_event_values hashmap with the parameters associated with the event. Unfortunately, PL/SQL does not have the concept of multiple threads – apart from using dbms_job to spawn a background task – so typically the observers will run after each other rather than simultaneously.

Let’s take the HRM_SALARY_RULES package as an example. It can generate a number of events:

We have the HRM_SALARY_RULES package accept registration of observers in the following way:

Figure 6: Implementing the Observer Pattern – Observe the events from HRM_SALARY_RULES

Procedures like reg_perf_loss_observer and reg_sal_decrease_observer can be invoked to register an observer for the specific event type. The observer must adhere to a predefined interface – it should be a procedure, stand alone or in a package – that accepts the following parameters:

An unlimited number of observers can be registered for each event type. The observers are stored inside the package in an observer-table, a PL/SQL Collection that is manipulated largely through helper procedures in the generic Observer_Pattern_Helper package.

When specific events occur, such as performance degradation or salary decrease, inside the (observed) package body calls are made to a local procedure on_event. For example:

function verify_salary_raise( p_job in varchar2, p_old_salary in number, p_new_salary in number)
  return boolean
  is
    l_start_time number:= dbms_utility.get_time; -- returns a timestamp in 100ths of a second
    l_end_time   number;
    l_result     boolean:= true;
  begin 
    if p_old_salary > p_new_salary
    then
      on_event
      ( C_SALARY_DECREASE_EVENT
      , Observer_Pattern_Helper.key_value_string_to_hashmap
        ( 'old_salary='||p_old_salary||';'
        ||'new_salary='||p_new_salary||';'
        ||'user='||USER||';'
        )
      );
    end if;
  …
  l_end_time:= dbms_utility.get_time;
    -- test for total validation time; if more than 400 ms, fire event
    if (l_end_time - l_start_time) > 40 -- 40 * one 100th of a second
    then
      on_event
      ( C_PERFORMANCE_LOSS_EVENT
      , Observer_Pattern_Helper.key_value_string_to_hashmap
        ( 'validation_time='||10*(l_end_time - l_start_time)||' ms;'
        )
      );    
    end if;
  …

This on_event procedure calls another generic procedure – notify_observers – in the helper package Observer_Pattern_Helper, passing the event type, the internally held list of registered observers, the observed package and a list of parameters associated with the event. 

  procedure on_event
  ( p_event_type in varchar2
  , p_event_parameters in Observer_Pattern_Helper.hashmap
  ) is
  begin
    Observer_Pattern_Helper.notify_observers
    ( p_event_type => p_event_type
    , p_event_parameters  => p_event_parameters
    , p_observable => 'HRM_SALARY_RULES'
    , p_observers => g_observers
    );
  end on_event;

This procedure in turn will make calls to all observers for the current event:

  procedure notify_observers
  ( p_event_type       in varchar2
  , p_event_parameters in hashmap
  , p_observable       in varchar2
  , p_observers        in observer_table_t
  ) is
    i number;
  begin
    set_hashmap(p_hashmap => p_event_parameters);
    -- notify all registerd observers that are interested in this event_type
    i := p_observers.FIRST;  
    WHILE i IS NOT NULL LOOP
      if nvl(p_observers(i).event_type, p_event_type) = p_event_type
      then
        execute immediate 
         'begin
           '||p_observers(i).observer
           ||' ( p_observable   => :1
               , p_event_type   => :2
               , p_event_values => Observer_Pattern_Helper.get_hashmap
               );
            end;'
         using in p_observable
         ,     in p_event_type
         ;
      end if;
      i := p_observers.NEXT(i);  -- get subscript of next element
    END LOOP;
  end notify_observers;

Let’s see this in action. We register an observer for the PERFORMANCE_LOSS_EVENT in package HRM_SALARY_RULES. Then we have this package validate a salary change. Since we built in a deliberate sleeptime into the validation procedure, the performance loss event will occur and the observers will be notified:

declare
procedure test_observer_pattern
 is
   procedure test_job_sal_raise
   ( p_job in varchar2
   , p_old_sal in number
   , p_new_sal in number
   ) is
   begin
     if hrm_salary_rules.verify_salary_raise( p_job => p_job,p_old_salary => p_old_sal 
                                            , p_new_salary => p_new_sal)
     then
       print( 'Salaryraise for '||p_job||'  from '||p_old_sal||' to '||p_new_sal||' is approved.'); 
     else
       print( 'Salaryraise for '||p_job||'  from '||p_old_sal
            ||' to '||p_new_sal||' is NOT approved.');    
     end if;
   end;

 begin
   Performance_Monitor.set_logger('LOGGER.LOG_MESSAGE');
   hrm_salary_rules.set_logger('LOGGER.LOG_MESSAGE');
   hrm_salary_rules.reg_perf_loss_observer('PERFORMANCE_MONITOR.PERFORMANCE_LOSS_OBSERVER');
   dependency_injector.inject_dependencies;
   test_job_sal_raise('SALESMAN', 1000, 1020);
   test_job_sal_raise('SALESMAN', 1000, 1200);
 end test_observer_pattern;

Our Performance Monitor is implemented in a fairly simple way: it logs the fact that performance is below expectations to the logger it has been injected with. The output of the above PL/SQL code is now as follows:

Note the messages HRM_SALARY_RULES: Performance issue in HRM_SALARY_RULES: validation_time=520 ms that is the result of the Performance Loss Event being broadcast to all Observers, including the Performance Monitor.

We have now implemented a basic framework for the Observer-Observable Design Pattern. Without direct dependencies we can have specific events in our packages cause custom pieces of code to be executed. The packages indicate that they are able to send notifications for specific events. Observers can be registered with these packages. When the events occur, all observers are notified. Since the call is made through dynamic pl/sql, there is no compile time dependency.

Trigger Based Observer Pattern Implementation

With another approach we can move much closer to the way DML and System events are dealt with by Oracle. It allows for more decoupling between event generator and event consumer. This approach is based on Instead-Of triggers that consume the events. These triggers are registered on an Events View. Packages publish events by inserting (dummy) values into the Events View; these inserts will fire the instead-of triggers that were registered against it.

A combination of these approaches would be a generic observer that is registered with each package that generates events and that passes the events along by doing a fake-insert on a generic events view. Observers can register instead-of triggers on this events-view, no be notified of the events that take place.

What about DBMS_ALERT?

A very good question you might have to ask by now: is this not the kind of functionality that dbms_alert is intended for? Well, no not exactly. The true value of dbms_alert – more or less superseded by Advanced Queuing – lies in its ability to send notifications to other sessions. These sessions should have registered their interest in special event types (alerts). However, they have to poll for event occurrences; the database will not invoke pieces of PL/SQL code when the alerts are sent. Furthermore, the payload associated with an alert is an unstructured piece of text, a varchar2 in -typesinter-session. However, the wait and waitany procedures are basically pollers. Also: transaction based – circumvent with Autonomous Transaction.

You can download all sources for the Observer Pattern implementation introduced in this paragraph at: http://technology.amis.nl/blog/?p=1123.

Miscellaneous Patterns and Concepts

There are many other concepts from the Java Programming world that could also be useful in our PL/SQL realm. Below I have listed some of them with a suggestion as to how to implement them. I expect to describe them in more detail on our Web Log at: http://technology.amis.nl/blog:

Conclusion

It is fortunate that we PL/SQL developers are not alone in the world. We do not have to solve every problem ourselves. It turns out that – perhaps from unsuspected quarters – ideas and design & programming techniques from outside our bubble can be valuable, if nothing else to trigger us to do some creative thinking.

This paper presented some perhaps far fetched implementations of popular concepts from primarily the world of Java programming. They are primarily intended to provoke you into thinking outside of the box. Look around you and use what you like.

To download the source code for the examples in this paper as well as find a list of other useful resources, please go to:  the AMIS Technology Weblog.

Lucas Jellema has many weird ideas. Some he manages to keep private but others need to be shared. An almost continuous stream of well-meant though sometimes quite bizarre articles is published at the AMIS Technology Weblog. Since 1997 he has visited the ODTUG conferences to spout his ramblings there as well. Every now and again audiences pick up something that is really truly useful. By the way, Lucas worked 8 years at Oracle Corporation, and in 2002 joined AMIS Services, a Dutch consulting firm specializing in Oracle and Java technology. Since November 2005, he is also one of them Oracle ACEs.