Oracle log script errors in a table

Prepare a table to store the script error logs

-- this table shall hold our 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));

Prepare a method to call if an error happens in a script:

-- this method can be called from scripts to log some message in case of any exception
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;

Short example how to use it:

create or replace
PROCEDURE DUMMY_PROC AS
      CURSOR ourCursor IS 
        SELECT 
          id
        FROM 
          Companies;
BEGIN
  FOR row IN ourCursor
  LOOP
    BEGIN
      DOSOMECRAZYSTUFFTHATMIGHTFAIL();
    EXCEPTION
      WHEN OTHERS THEN
        log_script_error('name of our script');
        CONTINUE;
    END;
  END LOOP;
END DUMMY_PROC; 

Leave a comment

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