Oracle Log Script Errors

Table to Hold the script errors

create table script_error_log (
  error_timestamp TIMESTAMP(9),
  script_name VARCHAR2(255),
  error_number NUMBER,
  error_message VARCHAR2(255),
  error_stack VARCHAR2(2000));

Method to call from within the script

create or replace procedure log_script_error (scriptname VARCHAR2) is
  errornumber NUMBER;
  errormessage VARCHAR2(250);
  errorstack VARCHAR2(2000);
  
  PRAGMA AUTONOMOUS_TRANSACTION;
begin
  errornumber := sqlcode;
  errormessage := sqlerrm;
  errorstack := substr(dbms_utility.format_error_stack,1,2000);
  
  INSERT INTO script_error_log(error_timestamp, script_name, error_number, error_message, error_stack) VALUES (SYSTIMESTAMP, scriptname, errornumber, errormessage, errorstack);
  
  COMMIT;
end;

Small example how to use it

create or replace
PROCEDURE TestProc AS
BEGIN
    BEGIN
      DOSOMECRAZYSTUFFTHATMIGHTFAIL();
    EXCEPTION
      WHEN OTHERS THEN
        log_script_error('TestProc');
        CONTINUE;
    END;
END DUMMY_PROC; 

Leave a comment

Your email address will not be published. Required fields are marked *