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.

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.