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

5

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<br />AFTER CREATE<br />ON SCHEMA<br />declare<br />  PRAGMA AUTONOMOUS_TRANSACTION;<br />  l_job number;<br />BEGIN<br />  if ora_dict_obj_type = 'PACKAGE BODY'<br />     and <br />     not aop_processor.during_advise <br />  then<br />    dbms_job.submit<br />      ( JOB  =&gt; l_job<br />      , WHAT =&gt; 'begin     <br />                   aop_processor.advise_package<br />                   ( p_object_name   =&gt; '''||ora_dict_obj_name ||''' <br />                   , p_object_type   =&gt; '''||ora_dict_obj_type ||'''<br />                   , p_object_owner  =&gt; '''||ora_dict_obj_owner||'''<br />                   );<br />                 end;'<br />      );<br />      commit;<br />  end if;<br />END aop_processor_trg;<br />&nbsp;

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)<br />  function verify_salary_raise( p_job in varchar2, p_old_salary in number, p_new_salary in number)<br />  return boolean<br />  is<br />  begin <br />    ... <br />

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)<br />  function verify_salary_raise( p_job in varchar2, p_old_salary in number, p_new_salary in number)<br />  return boolean<br />  is<br />  begin <br />    -- AOP-ADVICE:LOG  ; Added by AOP_PROCESSOR on 02-03-2006 20:21:58<br />    dbms_output.put_line('Call to HRM_SALARY_RULES.verify_salary_raise on '<br />                       ||to_char(systimestamp, 'DD-MM-YYYY HH24:MI:SS');<br />    -- END OF AOP-ADVICE<br />    ...<br />&nbsp;

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

The weave function:

 

  function weave<br />  ( p_code in out clob<br />  , p_package_name in varchar2<br />  ) return boolean<br />  is<br />    l_advice_type             varchar2(250); <br />    l_program_unit_to_advise  varchar2(250);<br />    l_begin_of_unit           number;    -- the position in p_code of the &quot;begin&quot;  of the program unit<br />    l_end_of_unit             number;   -- the position in p_code of the &quot;end&quot; of the program unit<br />    l_advised                 boolean:= false;<br />    l_start_pos               number(5):=1;<br />    l_advice                  varchar2(32000);<br />    l_param_name              varchar2(4000);    <br />    procedure start_advice<br />    ( p_code in out varchar2<br />    ) is<br />    begin<br />      p_code:= p_code<br />             ||chr(13)||chr(10)<br />             ||'    -- AOP-ADVICE:'||l_advice_type||'  ; Added by AOP_PROCESSOR on '<br />             ||to_char(systimestamp,'DD-MM-YYYY HH24:MI:SS')<br />             ;    <br />    end start_advice;<br /><br />    procedure end_advice<br />    ( p_code in out varchar2<br />    ) is<br />    begin<br />      p_code:= p_code<br />                 ||chr(13)||chr(10)<br />                 ||'    -- END OF AOP-ADVICE'<br />             ;    <br />    end end_advice;<br />    <br />  begin<br />    while get_pointcut<br />          ( p_program_unit_to_advise =&gt; l_program_unit_to_advise<br />          , p_advice                 =&gt; l_advice_type<br />          , p_begin_of_unit          =&gt; l_begin_of_unit<br />          , p_end_of_unit            =&gt; l_end_of_unit<br />          , p_code                   =&gt; p_code<br />          , p_start_pos              =&gt; l_start_pos<br />          )<br />    loop<br />      if l_advice_type ='LOG'<br />      then<br />        start_advice(l_advice);       <br />        l_advice:= l_advice <br />                 ||chr(13)||chr(10)<br />                 ||'  dbms_output.put_line(''Call to '||p_package_name||'.'||l_program_unit_to_advise<br />                 ||' on ''||to_char(systimestamp, ''DD-MM-YYYY HH24:MI:SS'')||''.'');'<br />                 ;<br />        end_advice(l_advice);<br />        p_code:= substr(p_code, 1, l_begin_of_unit+5)<br />                 ||l_advice<br />                 ||substr(p_code, l_begin_of_unit+5);<br />      end if; <br />      l_advised:= true;<br />      l_start_pos:= l_end_of_unit;<br />      l_advice:='';<br />    end loop;<br />    return l_advised;<br />  end weave;<br />

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     <br />  aop_processor.reapply_aspect<br />  ( p_aspect   =&gt; 'LOG'<br />  );<br />end;<br />

The procedure reapply_aspect is implemented as follows:

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

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

 

 

&nbsp;
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.

5 Comments

  1. Gerwin,

    The jury is still out. I do not believe that you should rush in, take my prototype imlpementation and start using it for real. What I hope to achieve is get smart people thinking about the concept of AOP applied
    to a PL/SQL development environment. AOP can be very powerful. Whether the PL/SQL
    technology can be AOP-enabled in a viable way is still a question. For AOP to work, there needs to be a blance between development taks and developer’s and project manager’s mentality on the one hand and supporting tools and technology on the other.

    However, having said all that, there is no reason why we could not start with AOP for very simple things like logging or debugging. The proof of the pudding…

  2. The title of this post interested me. After reading it I understand the concept of AOP. In the title you ask: “Is it Hot? Is it Cool or is it utterly useless?” The concept itselve looks usefull to me. However I still wonder whether your implementation of this concept within PL/SQL is usefull or not. Can you answer this question?

  3. Andrej,

    Thanks for your comment. Good to hear from you again.

    Like I say in the article, what I am doing here is compile time AOP – or even slightly before compile time, as the source itself is altered. What I mention but do not show in code is that it is not very hard to define the aspects to apply external to the code. So that aspect (no pun intended) is covered. However, as you saw in the examples, it will be impossible to change an aspect and not recompile the advised code (as is the case with Java’s Compile Time AOP solutions. What I have not worked out in a very neat fashion, is how to define the aspects outside of the AOP Processor.

    All in all, I wanted to get people – like yourself – thinking about ways forward with PL/SQL. Many good ideas and concepts are being developed in other arenas, like the Java arena, that may very well be applicable to PL/SQL programming as well. Expect my article on Dependency Injection in PL/SQL later this week (I am not kidding you).

  4. This seems more like adding macros than aop to plsql (also very useable). The power of aop is that you can externally specify where to add the extra functionality. I think it will be very hard to add aop to plsql. As you mention, because of wrapping you can’t apply the aspects to the source code, so you’d have to manipulate the pcode generated by oracle. For java you have some good bytecode manipulating libraries, I’m not aware of anything similar for plsql. Plus, you can also compile plsql to platform dependent code, which makes pcode manipulation almost impossible.