Log error messages in Oracle stored procedure

Question

Hi, We plan to configure a stored procedure to run as a batch job daily using Oracle DBMS scheduler package. We would like to know what would be the best way to log an error message when there is an error occured. Is logging to a temporary table an option? or is there a better option. Thanks in advance.

Answer 1

that depends on how you will deal with errors: if you just need to be notified, the email is the best option; if you need to manually continue process the error, the table is good choice.

Answer 2

You could use log4plsql http://log4plsql.sourceforge.net/and change the choice later by configuration changes not code changes

The log4plsql page gives a list of various places it can log.

It also depends how applications and systems are monitored in your environment - if there is a standard way fir example a business I worked add used used irc for monitoring - then you might want a function that calls to that.

Answer 3

If you decide to roll your own logging and log into a table you might go the Autonomous Transaction route.

An Autonomous Transaction is a transaction that can be commited independently of the current transaction you are in.

That way you can log and commit all the info you want to your log table independently of the success or failure of your stored procedure or batch process parent transaction.

CREATE OR REPLACE PROCEDURE "SP_LOG" (
    P_MESSAGE_TEXT VARCHAR2
) IS
  pragma autonomous_transaction;
BEGIN

    DBMS_OUTPUT.PUT_LINE(P_MESSAGE_TEXT);

    INSERT INTO PROCESSING_LOG (
        MESSAGE_DATE,
        MESSAGE_TEXT
    ) VALUES (
        SYSDATE,
        P_MESSAGE_TEXT
    );
    COMMIT;

END;
/

Then if you call it like this, you can still get messages committed to your log table even if you have a failure and roll back your transaction:

BEGIN
  SP_LOG('Starting task 1 of 2');

  ... code for task 1 ...

  SP_LOG('Starting task 2 of 2');

  ... code for task 2 ...

  SP_LOG('Ending Tasks');

  ... determine success or failure of process and commit or rollback ... 

 ROLLBACK;
END;
/

You may want to tidy it up with exceptions that make sense for your code, but that is the general idea, the data written in the calls to SP_LOG persists, but the parent transaction can still be rolled back.

Answer 4

You say that you don't have a lot of control over the DB environment to install logging packages - if this is the case then you'll be limited to querying the information in the dba_scheduler_job_run_details and dba_scheduler_job_log system views - you'll be able to see the history of executions here. Unhandled exceptions will show up in the ADDITIONAL_INFO column. If you need notification you can poll these views and generate email.