A good example of PLSQL-OO
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:
- I need to declare some variables and a constant for the table containing the parameter-values and for the scope
- With each call to logger I must specify that scope
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'; begin 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'); begin 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_start; lggr.log('My business logic starts here'); -- Do my business logic lggr.log('My business logic ends here'); lggr.log_end; 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.