Speed Up Development with Logger: Generate a Template

Instrumentation of PL/SQL code is essential. My favourite tool to instrument PL/SQL is Logger.
Martin Giffy D'Souza wrote a few blogs on how to speed up development with Logger and I want to chime in with my own productivity booster.
What I have written is a PL/SQL package that will generate a Procedure or Function body with all the calls to Logger. This includes all the arguments, or at least the IN and IN/OUT arguments.
When an exception occurs, it is quite handy to have all the arguments at your disposal to make it easier to debug your code. But to include the arguments manually when you are writing code is quite a lot of work.
You can download (or fork or whatever) the package on GitHub.

How does it work?

Install the package, which contains a single procedure called Template.

   procedure template (p_procedure  in varchar2
                      ,p_standalone in boolean := false
                      );

The Template procedure has two arguments; one for the complete (pacakged) procedure name, and one to indicate if it is standalone (not pacakged) or not.

When you have written the procedure (or function) signature, something like

create or replace package demo_pkg is
   
   procedure test (p_arg1 in     varchar2
                  ,p_arg2 in out varchar2
                  ,p_arg3 out    varchar2
                  );

end demo_pkg;
/

... and when it compiles successfully, then you can generate the stored procedure body using my generator_pkg.
The package uses DBMS_OUTPUT to show the template, so you should set the serveroutput on in SQL*Plus.

set serveroutput on format wrapped

begin
   generator_pkg.template ('demo_pkg.test');
end;
/

This will generate the following:

   is
      l_scope  constant varchar2(61) := g_package||'test';
      l_params logger.tab_param;
   begin
      logger.append_param (p_params => l_params, p_name => 'p_arg1', p_val => p_arg1);
      logger.append_param (p_params => l_params, p_name => 'p_arg2', p_val => p_arg2);
      logger.log_information (p_text => 'Start'
        ,p_scope => l_scope
        ,p_params => l_params
        );
      [==> Actual Program goes here ==]
      logger.log_information (p_text => 'End'
        ,p_scope => l_scope
        );
   end test;

Because it is a packaged procedure the l_scope variable contains a reference to g_package.
Each of my packages contains a Global Constant called g_package which is defined as

   g_package constant varchar2(31) := $$plsql_unit || '.';

Now that the template is generated, simply copy-and-paste it in your editor and start to write the actual program where it says

   [==> Actual Program goes here ==]

And that's it, now you have a starting point for development including the references to Logger.

The blogs that Martin wrote:

• Part 1
• Part 2