CODEWITHSUNDEEP
oracle-databaseoracle11g
JOTN
JOTN

Reputation: 6317

Oracle scheduler job log output

I'm using dbms_scheduler to execute a PL/SQL stored procedure. I would like to be able to have that code create some text logging output and associate it with the run to verify it's working, but I can't find anything to do that in the docs. Is there a facility to do this that I'm missing? This is an 11g database running under Unix. It would be real nice if I could use dbms_output so I could also run it from sqlplus and get output.

Upvotes: 0

Views: 13868

Answers (3)

Joshua Huber
Joshua Huber

Reputation: 3533

I agree with what the others have said. Here's the actual nuts and bolts, but with a nice interface, too. The way I typically do this:

Make a logging table:

CREATE TABLE job_log (
    ts TIMESTAMP DEFAULT SYSTIMESTAMP PRIMARY KEY
  , message VARCHAR2(255)
);

Make a stored proc that easily writes into your log table:

CREATE OR REPLACE PROCEDURE job_logger (v_message VARCHAR2)
IS
BEGIN
  INSERT INTO job_log(message) VALUES (v_message);
  COMMIT;
END;
/

Then within your job, you are probably running a stored procedure. Within your own stored procedure, simply add lines that call the job_logger() procedure to write to your log. This keeps the ugly INSERT ... COMMIT clutter out of your interesting stored proc code.

CREATE OR REPLACE PROCEDURE foo 
IS
BEGIN
  job_logger('Starting job foo.');
  ...
  {your code here}
  ...
  job_logger('Another message that will be logged.');
  ...
  job_logger('Completed running job foo.');
EXCEPTION
    ...
    job_logger('Oops, something bad happened!');
    ...
END;
/

Your log table is automatically timestamped and indexed by the primary key. To view the log, you might run this

SELECT * FROM job_log ORDER BY ts DESC;

Now if would rather not use the Oracle scheduler, and want instead to use the DBMS_OUTPUT way of writing output, and want to run this under a Unix shell, that is possible also.

You would make a script that calls sqlplus, somewhat like this. If your user is SCOTT and the stored proc is called FOO,

#!/bin/sh
. /whatever/script/that/sets/your/oracle/environment
echo "
  set serveroutput on feedback off
  exec foo
" | sqlplus -s -l scott/tiger@orcl

Note, the -s flag suppresses the Oracle SQL Plus banner for cleaner output. The -l flag makes it so that sqlplus will abort if the password is bad or something else wrong, rather than try to prompt for username. Feedback off suppresses the PL/SQL "Anonymous block completed" message.

If you want to schedule this, you can call it from cron like this:

00 00 * * * /path/to/the/above/script.sh > /where/you/want/your/output/saved.log 2>&1

Upvotes: 0

Eddie Awad
Eddie Awad

Reputation: 3729

There are a bunch of Oracle Scheduler data dictionary views that will help you monitor jobs. Here are two documentation pages related to that:

Moreover, Oracle Scheduler declares some internal Scheduler variables that you can use like any other PL/SQL identifier in your PL/SQL stored procedure. Here is the list of these variables.

If you want to log application specific information, I suggest you create your own log table. You can then insert into this table from within your stored procedure. You can even insert any of the Scheduler's internal variables there, like job_name and job_scheduled_start.

Upvotes: 3

Randy
Randy

Reputation: 16677

i make a table JOB_LOG insert into that table from inside your procedure...

Upvotes: 1

Related Questions