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;
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. ;-)