Is there a way to enable procedure logging for an Oracle Scheduled Job?

Question

I'm trying to enable logging on an Oracle Scheduled Job so that when I look at the run details of the job, I can discern what the procedure of the job worked on and what it did. Currently, the procedure is written to log out through dbms_output.put_line() since this is nice for procedures and SQL*Plus by just enabling set serveroutput on to see it. However, I cannot figure out how to have this logging information show up in the job run details. Specifically, I'm looking at:

select additional_info from dba_scheduler_job_run_details;

Also, this data seems to appear to be displaying in the run details within the enterprise manager under instances of the job.

So, is there a way, a setting or a different package, to have simple logging for an Oracle Scheduled Job?

Answer 1

You could add something at the end of the call. It can call dbms_output.get_lines, load the data into a CLOB and store that somewhere for your perusal. You'd probably want something to call DBMS_OUTPUT.ENABLE at the start too.

Personally, I've avoid using DBMS_OUTPUT.PUT_LINE and have your own logging routine, either to a flat file or a table (using autonomous transactions).

Answer 2

Autonomous transactions is the way to go!

You define a log table with the information you want to log and write a procedure that inserts & commits into this table as an autonomous transaction. That way, you can see what's going on, even when the job is still running.

AFAIK there is no other way to get the information while the job is still running, DBMS_OUTPUT will only display after the job is done.