Aspect Oriented Programming (AOP) in PL/SQL – Is it hot? Is it cool? Or utterly useless?

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. In this article, I will show a way to introduce AOP in the world of PL/SQL programming. It does not aim to provide the definite solution of AOP in PL/SQL, but I hope it will trigger you to think out-of-the-box with PL/SQL. Note that the article contains fully functional code that allows you to set up PL/SQL based AOP in less than 5 minutes.

Traditionally, a developer would usually 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 for example I want my AOP (http://www.javaworld.com/javaworld/jw-01-2002/jw-0118-aspect.html ) for a more detailed introduction to AOP.

Hooking Aspect Weaving into PL/SQL

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 about to compile or 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. 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. 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.

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 
  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;
 

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

• Logging, Debug and trace statements that for example report every invocation of a procedure along with the values of the input parameters
• Security check to find out whether the current caller of a unit inside a package has the proper database role enabled or an appropriate value set in an application context; for example: @AOP(advice=SECURITY{role=admin,salesmgr})
• Record in an auditing system that a call is made to a certain program unit on a certain moment by a certain user with certain parameters
• Include a call to our user defined function or procedure whenever a certain program unit is invoked

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.

The AOP Processor that is doing the heavy lifting can be seen here: aop_processor. It retrieves the package source code using dbms_metadata.get_ddl. It will then start looking for the @AOP tag and the following advice specification. It uses some of the Oracle 10g Regular Expression functionality to provide some leniency in the syntax – such as case insensitivity and disregard for superfluous space characters. When a such a pointcut definition is found, the weaving in of the aspect takes place. Currently, only the LOG advice can be applied. The weaver locates the first begin statement following the first procedure or function keyword after the @AOP tag. Note that this simplified approach fails when the procedure or function contains embedded program units. Some key pieces of code:

The function get_body that returns the source of the package body:

  function get_body
  ( p_object_name   in varchar2
  , p_object_owner  in
 varchar2
  ) return clob
  is
    l_code clob;
  begin
    -- make sure that dbms_metadata does return the package body 
    DBMS_METADATA.SET_TRANSFORM_PARAM 
    ( transform_handle  => dbms_metadata.SESSION_TRANSFORM
    , name              => 'BODY'
    , value             => true
    , object_type       => 'PACKAGE'
    );
    -- make sure that dbms_metadata does not return the package specification as well
    DBMS_METADATA.SET_TRANSFORM_PARAM 
    ( transform_handle  => dbms_metadata.SESSION_TRANSFORM
    , name              => 'SPECIFICATION'
    , value             => false
    , object_type       => 'PACKAGE'
    );
    l_code:= dbms_metadata.get_ddl('PACKAGE', p_object_name, p_object_owner);
    return l_code;
  end get_body;

The weave function:

  function weave
  ( p_code in out clob
  , p_package_name in varchar2
  ) return boolean
  is
    l_advice_type             varchar2(250); 
    l_program_unit_to_advise  varchar2(250);
    l_begin_of_unit           number;    -- the position in p_code of the "begin"  of the program unit
    l_end_of_unit             number;   -- the position in p_code of the "end" of the program unit
    l_advised                 boolean:= false;
    l_start_pos               number(5):=1;
    l_advice                  varchar2(32000);
    l_param_name              varchar2(4000);    
    procedure start_advice
    ( p_code in out varchar2
    ) is
    begin
      p_code:= p_code
             ||chr(13)||chr(10)
             ||'    -- AOP-ADVICE:'||l_advice_type||'  ; Added by AOP_PROCESSOR on '
             ||to_char(systimestamp,'DD-MM-YYYY HH24:MI:SS')
             ;    
    end start_advice;

    procedure end_advice
    ( p_code in out varchar2
    ) is
    begin
      p_code:= p_code
                 ||chr(13)||chr(10)
                 ||'    -- END OF AOP-ADVICE'
             ;    
    end end_advice;
    
  begin
    while get_pointcut
          ( p_program_unit_to_advise => l_program_unit_to_advise
          , p_advice                 => l_advice_type
          , p_begin_of_unit          => l_begin_of_unit
          , p_end_of_unit            => l_end_of_unit
          , p_code                   => p_code
          , p_start_pos              => l_start_pos
          )
    loop
      if l_advice_type ='LOG'
      then
        start_advice(l_advice);       
        l_advice:= l_advice 
                 ||chr(13)||chr(10)
                 ||'  dbms_output.put_line(''Call to '||p_package_name||'.'||l_program_unit_to_advise
                 ||' on ''||to_char(systimestamp, ''DD-MM-YYYY HH24:MI:SS'')||''.'');'
                 ;
        end_advice(l_advice);
        p_code:= substr(p_code, 1, l_begin_of_unit+5)
                 ||l_advice
                 ||substr(p_code, l_begin_of_unit+5);
      end if; 
      l_advised:= true;
      l_start_pos:= l_end_of_unit;
      l_advice:='';
    end loop;
    return l_advised;
  end weave;

Aspect Management

The implementation of Aspects is probably 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.
 

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;

The procedure reapply_aspect is implemented as follows:

procedure reapply_aspect
  ( p_aspect in varchar2 -- for example LOG
  ) is
    l_body clob;
  begin
    for p in (select object_name package_name, owner from all_objects where object_type ='PACKAGE BODY') loop
      l_body:= get_body( p.package_name, p.owner);
      if  regexp_instr(l_body,'@aop.*\(.*advice.*LOG.*\)' ,1,1,0,'i') > 0 
      then
        advise_package( p_object_name => p.package_name, p_object_type => 'PACKAGE BODY', p_object_owner => p.owner);
      end if;
      
    end loop;
  end reapply_aspect;

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:

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. 

Resources

Download all sources for the examples in this article: aop_plsql.zip