Error Logs in Oracle Stored Procedure
We plan to tune the stored procedure daily to run in a batch job using the Oracle DBMS Scheduler Package. We would like to know what would be the best way to log an error message when an error occurs. Is the parameter written to the temporary table? or is there a better option. Thanks in advance.
You say that you don't have much control over the DB environment for installing logging packages - if so, then you will be limited to requesting information in the form of system views dba_scheduler_job_run_details and dba_scheduler_job_log - you will be able to see the execution history here. Unhandled exceptions will appear in the ADDITIONAL_INFO column. If you require notification, you can poll these opinions and create an email.
source to share
If you choose to translate your own log and enter the table, you can follow the Offline Transaction route .
An offline transaction is a transaction that can be committed independently of the current transaction you are in.
This way, you can log and pass all the information you want to use in your log table, regardless of the success or failure of your stored procedure or batch processing of the 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 crash and rollback of 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 might want to remove it with exceptions that make sense to your code, but this is the general idea, the data written in the SP_LOG calls is preserved, but the parent transaction can still be rolled back.
source to share
You can use log4plsql http://log4plsql.sourceforge.net/ and later change the selection by changing the config, not changing the code
The log4plsql page provides a list of the various places it can log.
It also depends on how applications and systems are monitored in your environment - if there is a standard way, like a business I was working with, added the used irc used for monitoring - then you might need a function that calls this.
source to share