Design Patterns in PL/SQL – The Template Pattern

These days I am working on a series of articles discussing Design Patterns and their implementation in PL/SQL. In previous articles, I have discussed Aspect Oriented Programming and Dependency Injection. Subsequent articles will go into the Observer Pattern, Reflection/Introspection and Static Variables and Protected Methods. In this article you are reading now, we will look into the well known Template Pattern. This Pattern is quite popular. It is applied at great length in for example the Spring Framework. While usually it makes use of OO concepts, typically inheritance, it can also be implemented using Composition. And that’s how we can do it in PL/SQL!

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, another 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:
 

The Core Package should not be touched, it may even be wrapped. 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.

create or replace package price_calculations is

  -- Author  : LUCAS_J
  -- Created : 3/9/2006 11:00:00 PM
  -- Purpose : provides a number of services for calculating prices for orders etc.
  
  procedure calculate_order_price
  ( p_product           in  varchar2
  , p_list_price        in  number
  , p_count             in  number
  , p_delivery_date     in  date
  , p_customer_status   in  varchar2
  , p_customer_id       in  number
  , p_campaign_discount in  number
  , p_price             out number
  , p_total_discount    out number
  );
  
end price_calculations;
 

However, the component allows a certain extend 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.
 
The companion package’s specification:

create or replace package price_calculations_companion is

  -- Author  : LUCAS_J
  -- Created : 3/9/2006 11:03:55 PM
  -- Purpose : the companion package for the core price_calculations package with hooks that are called from within the core package

  -- this procedure allows for an additional surcharge for speedy delivery or bonus for delivery off-season
  -- it is called by the core price_calculations package before any other calculations have been performed. 
  procedure apply_delivery_date_surcharge
  ( p_price             in out  number
  , p_count             in  number
  , p_delivery_date     in  date
  , p_customer_status   in  varchar2
  , p_total_discount    out number
  );

  
  -- this procedure allows for an additional customer specific discount 
  -- it is called by the core price_calculations package after the total price based on product, list_price, order size and delivery_date
  -- has been calculated
  procedure apply_customer_discount
  ( p_price             in out  number
  , p_customer_status   in  varchar2
  , p_customer_id       in  number
  , p_total_discount    out number
  );  
  
end price_calculations_companion;
 

The initial implementation of these hooks is empty. Here I have provided an implementation for one of the two hooks:

create or replace package body price_calculations_companion is

  -- this procedure allows for an additional surcharge for speedy delivery or bonus for delivery off-season
  -- it is called by the core price_calculations package before any other calculations have been performed. 
  procedure apply_delivery_date_surcharge
  ( p_price             in out  number
  , p_count             in  number
  , p_delivery_date     in  date
  , p_customer_status   in  varchar2
  , p_total_discount    out number
  ) is
  begin
    if p_delivery_date < sysdate -- request delivery in the past
    then
      p_price:= p_price * 10;
    elsif p_delivery_date - sysdate > 500 -- request delivery in the distant future - and pay immediately by the way
    then
      p_total_discount:= p_total_discount + 0.2 * p_price;
      p_price:= p_price * 0.8;
    elsif to_char(p_delivery_date,'D') in (7,1) -- Saturday or Sunday
    then
      p_total_discount:= p_total_discount - 0.02 * p_price;
      p_price:= p_price * 1.02;    
    end if;
      
  end apply_delivery_date_surcharge;

  
  -- this procedure allows for an additional customer specific discount 
  -- it is called by the core price_calculations package after the total price based on product, list_price, order size and delivery_date
  -- has been calculated
  procedure apply_customer_discount
  ( p_price             in out  number
  , p_customer_status   in  varchar2
  , p_customer_id       in  number
  , p_total_discount    out number
  ) is
  begin
    null; -- at the present I have no additional customer specific discount calculation model in my depa
rtment, thank you very much

 end apply_customer_discount;
  
end price_calculations_companion;

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. However: users are free to change the implementation of the version label derivation implementation in this companion package.
 

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.

Define the p_check_parameter as an in out parameter in the ‘abstract’  hook procedure, for which an implementation should be provided.

-- this procedure allows for an additional customer specific discount 
  -- it is called by the core price_calculations package after the total price based on product, list_price, order size and delivery_date
  -- has been calculated
  procedure apply_customer_discount
  ( p_price             in out  number
  , p_customer_status   in  varchar2
  , p_customer_id       in  number
  , p_total_discount    out number
  , p_check_parameter   in out number
  );

 
Specify a user defined exception for situations where the abstract hooks have not been implemented:

  e_hook_proc_not_implemented exception;

 
In the Core package, prepare a local variable to use as check parameter. Assign it a value and record that value for verification after the call. Call the hook procedure with the local variable linked to the check parameter. If the check parameter has not been changed by the hook procedure, we will assume that the hook has not been implemented by the developer. Hence an exception is raised.

  procedure calculate_order_price
  ( p_product           in  varchar2
  , p_list_price        in  number
  , p_count             in  number
  , p_delivery_date     in  date
  , p_customer_status   in  varchar2
  , p_customer_id       in  number
  , p_campaign_discount in  number
  , p_price             out number
  , p_total_discount    out number
  ) is
    l_price number(10,2):=0;
    l_discount number(10,2):=0;
    l_check_parameter number:= dbms_random.value;
    l_original_check_parameter number:= l_check_parameter;
  begin
    ...
    -- call hook in companion package
    price_calculations_companion.apply_customer_discount
    ( p_price           => l_price
    , p_customer_status => p_customer_status
    , p_customer_id     => p_customer_id
    , p_total_discount  => l_discount
    , p_check_parameter => l_check_parameter
    );
    -- if the l_check_parameter is returned unchanged by the hook procedure, it is safe to assume
    -- that no implementation of the hook procedure has been provided
    if l_original_check_parameter = l_check_parameter
    then
      raise e_hook_proc_not_implemented;
    end if;
    p_price:= l_price;
    p_total_discount:= l_discount;
  end calculate_order_price;

Resources

Download the source code for the examples in this article: templatePattern.zip