Am I running from inside a (DBMS_SCHEDULER) Job?

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
  g_scheduled boolean;

procedure job
  l_scope  logger_logs.scope%type := gc_scope_prefix || 'job';
  l_params logger.tab_param;
  -- logger.append_param(l_params, 'p_param1', p_param1);
  logger.log('STARTED', l_scope, null, l_params);

  for i in (...)
    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;

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

  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.

After posting…

Thanks fo the extra feedback, some extra comments…

Refer to logger.ok_to_log for optimizing your logger calls. And as Martin D’Souza blogs here Logger with Batch Processes.

And, as Martin Berger mentions if you have access to v$session maybe that may work better for you. But that’s not always available.

Hi, I'm Jorge Rimblas. Father, husband, photographer, Oraclenerd, Oracle APEX expert, Oracle ACE, coffee lover, car guy, gadget addict, etc... I'm an APEX Tech Lead DRW. I have worked with Oracle since 1995 and done eBusiness Suite implementations and customizations. Nowadays I specialize almost exclusively in Oracle APEX.

I love comments, write me a line