Design Patterns in PL/SQL – The Template Pattern

8

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

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<br /><br />  -- Author  : LUCAS_J<br />  -- Created : 3/9/2006 11:03:55 PM<br />  -- Purpose : the companion package for the core price_calculations package with hooks that are called from within the core package<br /><br />  -- this procedure allows for an additional surcharge for speedy delivery or bonus for delivery off-season<br />  -- it is called by the core price_calculations package before any other calculations have been performed. <br />  procedure apply_delivery_date_surcharge<br />  ( p_price             in out  number<br />  , p_count             in  number<br />  , p_delivery_date     in  date<br />  , p_customer_status   in  varchar2<br />  , p_total_discount    out number<br />  );<br /><br />  <br />  -- this procedure allows for an additional customer specific discount <br />  -- it is called by the core price_calculations package after the total price based on product, list_price, order size and delivery_date<br />  -- has been calculated<br />  procedure apply_customer_discount<br />  ( p_price             in out  number<br />  , p_customer_status   in  varchar2<br />  , p_customer_id       in  number<br />  , p_total_discount    out number<br />  );  <br />  <br />end price_calculations_companion;<br />&nbsp;

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<br /><br />  -- this procedure allows for an additional surcharge for speedy delivery or bonus for delivery off-season<br />  -- it is called by the core price_calculations package before any other calculations have been performed. <br />  procedure apply_delivery_date_surcharge<br />  ( p_price             in out  number<br />  , p_count             in  number<br />  , p_delivery_date     in  date<br />  , p_customer_status   in  varchar2<br />  , p_total_discount    out number<br />  ) is<br />  begin<br />    if p_delivery_date &lt; sysdate -- request delivery in the past<br />    then<br />      p_price:= p_price * 10;<br />    elsif p_delivery_date - sysdate &gt; 500 -- request delivery in the distant future - and pay immediately by the way<br />    then<br />      p_total_discount:= p_total_discount + 0.2 * p_price;<br />      p_price:= p_price * 0.8;<br />    elsif to_char(p_delivery_date,'D') in (7,1) -- Saturday or Sunday<br />    then<br />      p_total_discount:= p_total_discount - 0.02 * p_price;<br />      p_price:= p_price * 1.02;    <br />    end if;<br />      <br />  end apply_delivery_date_surcharge;<br /><br />  <br />  -- this procedure allows for an additional customer specific discount <br />  -- it is called by the core price_calculations package after the total price based on product, list_price, order size and delivery_date<br />  -- has been calculated<br />  procedure apply_customer_discount<br />  ( p_price             in out  number<br />  , p_customer_status   in  varchar2<br />  , p_customer_id       in  number<br />  , p_total_discount    out number<br />  ) is<br />  begin<br />    null; -- at the present I have no additional customer specific discount calculation model in my depa
rtment, thank you very much<br />
 end apply_customer_discount;<br />  <br />end price_calculations_companion;<br /><br />

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 <br />  -- it is called by the core price_calculations package after the total price based on product, list_price, order size and delivery_date<br />  -- has been calculated<br />  procedure apply_customer_discount<br />  ( p_price             in out  number<br />  , p_customer_status   in  varchar2<br />  , p_customer_id       in  number<br />  , p_total_discount    out number<br />  , p_check_parameter   in out number<br />  );<br />

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

 

  e_hook_proc_not_implemented exception;<br />

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

Resources

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

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

8 Comments

  1. Zlatko Sirotic on

    The formatting of my code example is lost. Link for formatted example:
    “The Template Design Pattern in PL/SQL”
    http://www.quest-pipelines.com/pipelines/plsql/tips06.htm#OCTOBER

    I agree that the curent object types in Oracle aren’t practical for transient objects, first because we need references between transient objects.
    But, I think that Oracle object types are OK for persistent objects – for “column objects”, not for “row objects”:
    “OR(DBMS) or R(DBMS), That is the Question”
    http://www.quest-pipelines.com/pipelines/plsql/tips.htm#OCTOBER

    C.J.Date in “An introduction to Database Systems” (2004, page 885) says:
    “… object/relational systems … are, or should be, basically just relational systems that support the relational domain concept (i.e., types) properly
    – in other words, true relational systems, meaning in particular systems that allow users to define their own types.”

    Best regards,
    Zlatko

  2. 5) And most important, you cannot have private members, meaning, you cannot add members (method or variable) to the type body that don’t exist in the type spec, so all members are publicly accessible . And this ruins the whole concept of encapsulation principle. Packages have both public spec and private implementation, why not object have it only God knows..

  3. I don’t find the curent objects types in Oracle practical, because:
    1) When you want to change a supertype, you get an error that says you have to drop the subtypes first.. This is not a practical solution cause first you have to generate the scripts for all subtypes (and all their subtypes in the type hierarchy, then do your work on the super type and regenerate all the sub type tree in the correct order, and then change the sub types if necessary (which means the process continues recursively).. It could be done with an automated script, but why does Oracle want this I don’t understand.
    2) Also, if some of the subtypes are abstract (not instantiable) then it’s quite possible a Forms application to give “time signature changed” errors, as it does on package specs..
    3) As in the example Zlatko Sirotic gives, you have to use a dummy member variable in order to create interfaces or abstract classes.. By the way, I didn’t think of doing this so I shyed away from using objects types, but now I see I can create interfaces with a dummy variable:)

  4. Hi together
    I think this Template Pattern can be implementet a very clean way with object types. I tried with a simple pseudo object and it really works!
    First I thought ever not using object types. Why should I?
    Now I got it – I can almost implement every desing pattern the clean way with object types. So I think every pl/sql developer should start using it.
    At the moment I’m looking forward to use the Template Pattern in our production environment ;-)

  5. Zlatko Sirotic on

    Dear Lucas,

    My opinion is that PL/SQL classes (object types) still aren’t finished.
    Database 9i has made a giant leap forward compared to 8i (inheritance, polymorphism …),
    but in 10g there is hardly anything new.
    In my opinion it is a priority to add references between transient objects and then,
    perhaps, even a garbage collector.

    Between the way, I sent my PL/SQL “wish list” to Steven Feuerstein’s site “21st Century PL/SQL”
    http://www.oracleplsqlprogramming.com/IC/ideas/data_structures_and_datatypes/object_types/index.php
    (Add advanced O-O features like those found in Eiffel)
    and
    to the AskTom site: Thanks for the question regarding “What don’t you like about Oracle?”
    http://asktom.oracle.com/pls/ask/f?p=4950:8:4407442689342146348::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:48704116042682
    (My wishes – new features of Oracle Database 11br).

    In addition, I present you my two texts in witch I’m pointing out some of PL/SQL object oriented characteristics:
    Calling the Parent Object’s Version of an Overridden Method
    http://www.quest-pipelines.com/pipelines/plsql/tips03.htm#JUNE
    and
    Dynamic Method Dispatch and Method Overloading in a Subtype
    http://www.quest-pipelines.com/newsletter-v4/0503_C.htm

    But, adding more OO capabilities to PL/SQL is the second thing in my wish list.
    The first thing is – better support for business rules in the database.
    We need “What Not How: The Business Rules Approach to Application Development” (book by C.J.Date).
    My motto is: “Thou shall not do in the Middle Tier, what thou could have done in the Data Tier”
    (Toon Koppelaars in “A first JDeveloper project: Choices made, lessons learned”, Oracle World 2002).

    If the database would support business rules, then this two complex examples would be futile:
    Solving “COMMIT business rules” on the Database Server
    http://www.oracle.com/technology/oramag/code/tips2002/032402.html
    and
    Avoid a “Vicious Cycle”
    http://www.oracle.com/technology/oramag/code/tips2003/081003.html

    Best regards,

    Zlatko Sirotic

  6. Dear Zlatko,

    Thanks a lot for this very interesting example! Most of us tend to shy away from using the Oracle Object Extensions – certainly in part because of ignorance – so having your demonstration is valuable to a lot of us, including myself.

    Are you making use of the Object Extensions and the OO facilities available for Object Types a great deal?

    best regards,

    Lucas

  7. Zlatko Sirotic on

    We can try to use PL/SQL 9i/10g object types.

    But then in PL/SQL 9i/10g we must use MEMBER (non-STATIC) methods (procedures/functions):

    CREATE OR REPLACE TYPE templ_method_abstract AS OBJECT (
    dummy VARCHAR2(10),
    MEMBER PROCEDURE template_method,
    NOT INSTANTIABLE MEMBER PROCEDURE operation1,
    NOT INSTANTIABLE MEMBER PROCEDURE operation2
    )
    NOT FINAL
    NOT INSTANTIABLE
    /
    CREATE OR REPLACE TYPE BODY templ_method_abstract IS
    MEMBER PROCEDURE template_method IS
    BEGIN
    operation1;
    operation2;
    END;
    END;
    /

    CREATE OR REPLACE TYPE templ_method_concrete_a UNDER templ_method_abstract (
    OVERRIDING MEMBER PROCEDURE operation1,
    OVERRIDING MEMBER PROCEDURE operation2
    )
    /
    CREATE OR REPLACE TYPE BODY templ_method_concrete_a IS
    OVERRIDING MEMBER PROCEDURE operation1 IS
    BEGIN
    DBMS_OUTPUT.PUT_LINE (‘Operation 1 in class CONCRETE A’);
    END;

    OVERRIDING MEMBER PROCEDURE operation2 IS
    BEGIN
    DBMS_OUTPUT.PUT_LINE (‘Operation 2 in class CONCRETE A’);
    END;
    END;
    /

    CREATE OR REPLACE TYPE templ_method_concrete_b UNDER templ_method_abstract (
    OVERRIDING MEMBER PROCEDURE operation1,
    OVERRIDING MEMBER PROCEDURE operation2
    )
    /
    CREATE OR REPLACE TYPE BODY templ_method_concrete_b IS
    OVERRIDING MEMBER PROCEDURE operation1 IS
    BEGIN
    DBMS_OUTPUT.PUT_LINE (‘Operation 1 in class CONCRETE B’);
    END;

    OVERRIDING MEMBER PROCEDURE operation2 IS
    BEGIN
    DBMS_OUTPUT.PUT_LINE (‘Operation 2 in class CONCRETE B’);
    END;
    END;
    /

    DECLARE
    l_templ_method_object templ_method_abstract;
    BEGIN
    l_templ_method_object := NEW templ_method_concrete_a (‘DUMMY’);
    l_templ_method_object.template_method;

    l_templ_method_object := NEW templ_method_concrete_b (‘DUMMY’);
    l_templ_method_object.template_method;
    END;
    /

    Why we can’t use STATIC methods?
    First, in PL/SQL 9i/10g STATIC method can’t bee abstract (NOT INSTANTIABLE):

    CREATE OR REPLACE TYPE template_method_abstract_2 AS OBJECT (
    dummy NUMBER(1),
    STATIC PROCEDURE template_method,
    NOT INSTANTIABLE STATIC PROCEDURE operation1,
    NOT INSTANTIABLE STATIC PROCEDURE operation2
    )
    NOT FINAL
    NOT INSTANTIABLE
    /

    LINE/COL ERROR
    ——– —————————————————————–
    4/38 PLS-00169: modifier ‘STATIC’ conflicts with prior ‘NOT
    INSTANTIABLE’ specification

    5/38 PLS-00169: modifier ‘STATIC’ conflicts with prior ‘NOT
    INSTANTIABLE’ specification

    Second, we can try to use non-abstract STATIC methods in (abstract) parent:

    CREATE OR REPLACE TYPE template_method_abstract_3 AS OBJECT (
    dummy NUMBER(1),
    STATIC PROCEDURE template_method,
    STATIC PROCEDURE operation1,
    STATIC PROCEDURE operation2
    )
    NOT FINAL
    NOT INSTANTIABLE
    /
    CREATE OR REPLACE TYPE BODY template_method_abstract_3 IS
    STATIC PROCEDURE template_method IS
    BEGIN
    operation1;
    operation2;
    END;

    STATIC PROCEDURE operation1 IS
    BEGIN
    NULL; — simulates abstract method
    END;

    STATIC PROCEDURE operation2 IS
    BEGIN
    NULL; — simulates abstract method
    END;
    END;
    /

    but, (in PL/SQL 9i/10g) we can’t override STATIC method:

    CREATE OR REPLACE TYPE template_method_concrete_3 UNDER template_method_abstract_3 (
    OVERRIDING STATIC PROCEDURE operation1,
    OVERRIDING STATIC PROCEDURE operation2
    )
    /
    LINE/COL ERROR
    ——– —————————————————————–
    2/32 PLS-00169: modifier ‘STATIC’ conflicts with prior ‘OVERRIDING’
    specification

    3/32 PLS-00169: modifier ‘STATIC’ conflicts with prior ‘OVERRIDING’
    specification

    Regards,
    Zlatko Sirotic

  8. I’ve impelement this using oracle types, which provide abstract methods and others OO t
    types.

    I think it worth to show those design patterns also using oracle types