CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
user175698
user175698

Reputation:

Log error messages in Oracle stored procedure

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.

Upvotes: 4

Views: 29564

Answers (4)

David Mann
David Mann

Reputation: 2000

Logging into a temporary table can be a good solution. For example, you could create a simple logging table like this:

create table PROCESSING_LOG(
    MESSAGE_DATE timestamp,
    MESSAGE_TEXT varchar2(4000)
);

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.

Upvotes: 16

dpbradley
dpbradley

Reputation: 11915

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 (or in the corresponding views for the current user, ALL_SCHEDULER_JOB_RUN_DETAILS and ALL_SCHEDULER_JOB_LOG).

Unhandled exceptions will show up in the ADDITIONAL_INFO column. If you need notification you can poll these views and generate email.

Documentation for Oracle 19:

Upvotes: 2

mmmmmm
mmmmmm

Reputation: 32671

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.

Upvotes: 2

Henry Gao
Henry Gao

Reputation: 4936

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.

Upvotes: -1

Related Questions