Design Patterns in PL/SQL – Implementing the Observer Pattern

Next in my series on Design Patterns and their implementation in PL/SQL – also see previous articles on Dependency Injection of Values, Dependency Injection of Interfaces and the Template Pattern – is a discussion of the Observer Pattern. We will see a fairly elegant implementation of this pattern that provides for a very loosely (runtime) coupled architecture where packages can advise procedures of custom events. 

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 an 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 support 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. Let’s take a look at a simple, elegant and effective implementation of the Observer Pattern in PL/SQL.

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.

Two overloaded procedure to register observers as well as a 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 the 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:

• A business rule validation has failed
• The performance of validations has deteriorated below 400 ms
• Someone is attempting a salary decrease
• A salary increase was attempted for the job PRESIDENT

Another interesting event would be: more than 30% of all validations fail. However, that is a conclusion that can only be drawn across all session. When we discuss static variables, we will return to this particular event.

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

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:

• p_observable in varchar2 — the name of the package that sent the event
• p_event_type in varchar2 
• p_event_values in observer_pattern_helper.hashmap

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');
   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 message 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.

Moving Forward

In a next installment, we will take a look at a more refined implementation, based on Database Events and Views with Instead-Of triggers. With that 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.

Resources

Download the PL/SQL Code for this article: plsqlObserverPattern1.zip.