A good example of PLSQL-OO

Published by Richard Martens on

I am a PL/SQL nerd. I love the language and what you can do with it. I consider myself a PL/SQL programmer first and only secondarily an APEX developer.

For quite some time now I am highly in favour of using Object-Oriented techniques within packages but have difficulties convincing others to share that position. In my opinion OO gives us the opportunity to split “technical” logic from “business” logic.
With technical logic I mean all logic relating to database actions. CRUD actions mostly.

I think I have found another good example for using OO.. “Logger”!

Who is not using the logger utility started by Tyler Muth and continued by Martin Giffy D’Souza? If you’re not using it. Start right now! You can find it at https://github.com/OraOpenSource/Logger

By itself logger is great, but I really would have liked to see things a bit different:

  1. I need to declare some variables and a constant for the table containing the parameter-values and for the scope
  2. With each call to logger I must specify that scope

as so:

procedure test(p_firstname  in varchar2
              ,p_lastname   in varchar2
              ,p_has_titles in boolean) is
  t_params   logger.tab_param;
  l_logscope constant varchar2(100) := g_logscope || 'test';
  logger.append_param(p_params => t_logparams
                     ,p_name   => 'p_firstname'
                     ,p_val    => p_firstname);
  logger.append_param(p_params => t_logparams
                     ,p_name   => 'p_lastname'
                     ,p_val    => p_lastname);
  logger.append_param(p_params => t_logparams
                     ,p_name   => 'p_has_titles'
                     ,p_val    => p_has_titles);
  logger.log(p_text => 'Start', p_scope => l_logscope, p_params => t_logparams);
  logger.log(p_text => 'My business logic starts here', p_scope => l_logscope);
  -- Do my business logic
  logger.log(p_text => 'My business logic ends here', p_scope => l_logscope);
  logger.log(p_text => 'End', p_scope => l_logscope);
end test;

I think a lot of code to just insert “Start” and “End” in the logging table. And not only that.. It blurs my focus on what is really happening.. here: “– Do my business logic”

My first attempt was creating a logging utility myself, but almost all our clients want to use what is already available.. I understand.. I therefore took the liberty to write an OO wrapper for logger (so you can still use it in projects where logger is already deployed).

The wrapper is available at github and allows code like this:

procedure test_oo(p_firstname  in varchar2
                 ,p_lastname   in varchar2
                 ,p_has_titles in boolean) is
  lggr logger_obj := new logger_obj(p_scope => g_logscope || 'test_oo');
  lggr.add_param(p_name => 'p_firstname' , p_value => p_firstname);
  lggr.add_param(p_name => 'p_lastname'  , p_value => p_lastname);
  lggr.add_param(p_name => 'p_has_titles', p_value => p_has_titles);
  lggr.log('My business logic starts here');
  -- Do my business logic
  lggr.log('My business logic ends here');
end test_oo;

Personally I really like the fact that I don’t have to declare scope and parameter table no more and also that I don’t have to repeat that scope parameter all the time.

Have fun using my wrapper.

Categories: PL/SQL

Richard Martens

Richard Martens has been involved in information technology for more than 20 years. He started as a web developer using the Oracle database as no more than data storage. Richard has been responsible for major European multilingual websites and has been working with the Oracle database since 2000. During those years, he developed himself using a multitude of technologies and specialized in PL/SQL and Oracle APEX. With APEX, he combines the things he loves most: the Oracle database and web technologies.