SQL*Plus error logging - workaround for ROLLBACK issue

In my previous blog entry SQL*Plus error logging – New feature release 11.1, in my comments I stated an issue SPERRORLOG - Issue with Rollback. Whenever ROLLBACK is issued in the session, the feature fails to log the errors. Thanks to Jacek Gebal for his blog "Oracle Thoughts". I was really impressed by the workaround.

Let's look at the issue.

SQL> show errorlogging;
errorlogging is OFF

SQL> set errorlogging on;

SQL> show errorlogging;
errorlogging is ON TABLE SPERRORLOG

SQL> insert into emp(empno) values ('abcd');
insert into emp(empno) values ('abcd')
                               *
ERROR at line 1:
ORA-01722: invalid number


SQL> select timestamp, script, message from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

03-MAY-14 01.23.48.000000 AM

ORA-01722: invalid number


SQL> rollback;

Rollback complete.

SQL> select timestamp, script, message from sperrorlog;

no rows selected

Now that's something which we don't want to happen. If we use this feature as an automated error logging for any batch process or scheduled jobs, and if our program is designed to rollback the session if an error occurs, then this feature will never work for us. It will never capture the error once the session is rolled back. But we want to know what exactly caused the error.

Workaround is simple but tricky.

Three simple steps :
1. Rename table SPERRORLOG to a new table.
1. Create a view as "SPERRORLOG"
2. Create a procedure with autonomous transaction.
3. Create an INSTEAD OF trigger to call above procedure. Instead of insert on SPERRORLOG, rather insert into the new table.

SQL> ALTER TABLE sperrorlog RENAME TO sperrorlog_new;

Table altered.

SQL> CREATE VIEW sperrorlog AS SELECT * FROM sperrorlog_new;

View created.

SQL> CREATE OR REPLACE PROCEDURE p_sperrorlog(
  2    username VARCHAR2, timestamp TIMESTAMP, script VARCHAR2,
  3    identifier VARCHAR2, message VARCHAR2, statement VARCHAR2
  4  ) IS
  5   PRAGMA AUTONOMOUS_TRANSACTION;
  6  BEGIN
  7    INSERT INTO sperrorlog_new
  8    VALUES (username, timestamp, script, identifier, message, statement);
  9    COMMIT;
 10  END;
 11  /

Procedure created.

SQL> SHOW ERRORS;
No errors.

SQL> CREATE OR REPLACE TRIGGER r_sperrorlog
  2    INSTEAD OF INSERT ON sperrorlog FOR EACH ROW
  3   CALL p_sperrorlog(
  4     :NEW.username,:NEW.timestamp, :NEW.script,
  5     :NEW.identifier, :NEW.message, :NEW.statement)
  6  /

Trigger created.

SQL> SHOW ERRORS;
No errors.

Let's test it and see.

SQL> show errorlogging;
errorlogging is OFF

SQL> set errorlogging on;

SQL> show errorlogging;
errorlogging is ON TABLE SPERRORLOG

SQL> insert into emp(empno) values ('abcd');
insert into emp(empno) values ('abcd')
                               *
ERROR at line 1:
ORA-01722: invalid number


SQL> select timestamp, script, message from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

03-MAY-14 06.38.42.000000 AM

ORA-01722: invalid number


SQL> ROLLBACK;

Rollback complete.

SQL> select timestamp, script, message from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

03-MAY-14 06.38.42.000000 AM

ORA-01722: invalid number

It works! Now we could capture the errors even if ROLLBACK is issued.