Am I running from inside a (DBMS_SCHEDULER) Job?
I’ve talked about improving your code instrumentation in the past, in particular how to use conditional compilation for your extra logger calls. See Debugging, Logger & Conditional Compilation for the details.
This time, I had a different situation. How do I reduce the logging information if my code is running as a scheduled job?
I figured if an application (user) executes the code or a developer runs the code, I want to see logging information. But if the code runs as a scheduled job, then I want to greatly reduce the logging information, especially if said job runs every 5 min, or worse, every minute!
I settled on the following:
g_scheduled := sys_context('userenv','module') = 'DBMS_SCHEDULER';
Then I can have code like this:
package body my_package as g_scheduled boolean; procedure job is l_scope logger_logs.scope%type := gc_scope_prefix || 'job'; l_params logger.tab_param; begin -- logger.append_param(l_params, 'p_param1', p_param1); logger.log('STARTED', l_scope, null, l_params); for i in (...) loop if not g_scheduled then logger.log('... logging activity...', l_scope, null, l_params); end if; ... do more stuff.... end loop; if not g_scheduled then logger.log('END', l_scope, null, l_params); end if; exception when OTHERS then logger.log_error('Unhandled Exception', l_scope, null, l_params); raise; end job; begin g_scheduled := sys_context('userenv','module') = 'DBMS_SCHEDULER'; end my_package;
This will allow me to have a “heartbeat” of information that the job is running, without getting extremely verbose.
Thanks fo the extra feedback, some extra comments…