Debugging, Logger & Conditional Compilation

Aside from sharing knowledge, I sometimes blog to document and record something so I can use it again. This is one of those cases.

I use logger extensively, I would say on just about all my code. Code instrumentation is a critical aspect of writing good, maintainable, and scalable code.
However, sometimes, there’s is such a thing as too much logging or as I like to think about it, too verbose.

So I will often use PL/SQL Conditional Compilation to conditionally enable comments.

If I want the extra output, I will use this command before compiling a package:

alter session set PLSQL_CCFLAGS='VERBOSE_OUTPUT:TRUE';

Then the package will look like this:

package body intensive_data_load
as
gc_scope_prefix constant VARCHAR2(31) := lower($$PLSQL_UNIT) || '.';
procedure add_message(p_message_text in varchar2)
is
  l_scope  logger_logs.scope%type := gc_scope_prefix || 'add_message';
  l_params logger.tab_param;
begin
  -- logger.append_param(l_params, 'p_param1', p_param1);
  $IF $$VERBOSE_OUTPUT $THEN
  logger.log('BEGIN', l_scope, null, l_params);
  $END

  mm_api.add_message(p_msg => g_msg, p_message_text => p_message_text);

  $IF $$VERBOSE_OUTPUT $THEN
  logger.log('.. ' || p_message_text, l_scope);
  logger.log('END', l_scope);
  $END

  exception
    when OTHERS then
      logger.log_error('Unhandled Exception', l_scope, null, l_params);
      raise;
end add_message;

end intensive_data_load;

The flag VERBOSE_OUTPUT does not have any special meaning, I made it up. You can make up your own. But the flag could be something predefined like the Oracle DB version (look at dbms_db_version) or APEX version (looks at wwv_flow_api). See some more interesting examples here.

Anyway, I would rarely use the alter session command when I deploy the code to production. I don’t want the extra output all the time. However, if we ever need to troubleshoot we can re-compile the code with a new flag value.

You can turn the flag for your session and compile the package, like this:

alter session set PLSQL_CCFLAGS='VERBOSE_OUTPUT:TRUE';
alter package intensive_data_load compile;

Or with a single command:

alter package intensive_data_load compile
 PLSQL_CCFLAGS = 'VERBOSE_OUTPUT:TRUE'
reuse settings;

Finally, don’t forget to turn the verbose output back off. ;-)

Go ahead, share...

Facebook

Twitter

Linkedin

Google

Tumblr

Buffer

Stumbleupon

Email