Today I came up with idea to overcome the issue: SQLPlus ERRORLOGGING does not keep error log on rollback.

The resolution is to use autonomous transactions to log the errors reported by SQL Plus.

What we need to do is to somehow catch the error that is about to be logged and wrap it in an autonomous transaction.

The features that we will use are:

Create the error logging table.

CREATE TABLE sperrorlog(
  username VARCHAR(256),
  timestamp TIMESTAMP,
  script VARCHAR(1024),
  identifier VARCHAR(256),
  message CLOB,
  statement CLOB
);

Run a script to prove the ROLLBACK statement will cause the errors disappear from the error log.

SET ERRORLOGGING ON
INSERT INTO bad_table(a) VALUES(1);
SET ERRORLOGGING OFF
ROLLBACK;
SELECT timestamp, script, substr(message,1,100) message
  FROM sperrorlog;
SQL*Plus: Release 11.2.0.2.0 Production on Wed Jan 1 23:22:20 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Use "connect username/password@XE" to connect to the database.
SQL> conn hr/hr 
Connected.
SQL> truncate table sperrorlog;

Table truncated.

SQL> SET ERRORLOGGING ON
SQL> INSERT INTO bad_table(a) VALUES(1);
INSERT INTO bad_table(a) VALUES(1)
            *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> SET ERRORLOGGING OFF
SQL> ROLLBACK;

Rollback complete.

SQL> SELECT timestamp, script, substr(message,1,100) message FROM sperrorlog;

no rows selected

SQL>

The error log table is empty.

A little cheat will do the trick.

ALTER TABLE sperrorlog RENAME TO t_sperrorlog;

CREATE VIEW sperrorlog AS SELECT * FROM t_sperrorlog;

CREATE OR REPLACE PROCEDURE sperrorlog_prc(
  username VARCHAR, timestamp TIMESTAMP, script VARCHAR,
  identifier VARCHAR, message CLOB, statement CLOB
) IS
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO t_sperrorlog
  VALUES (username, timestamp, script, identifier, message, statement);
  COMMIT;
END;
/

CREATE OR REPLACE TRIGGER sperrorlog_trg
  INSTEAD OF INSERT ON sperrorlog FOR EACH ROW
 CALL sperrorlog_prc(
   :NEW.username,:NEW.timestamp, :NEW.script,
   :NEW.identifier, :NEW.message, :NEW.statement);
/

Run the same script and see the results.

SET ERRORLOGGING ON
INSERT INTO bad_table(a) VALUES(1);
SET ERRORLOGGING OFF
ROLLBACK;
SELECT timestamp, script, substr(message,1,100) message
  FROM sperrorlog;
SQL*Plus: Release 11.2.0.2.0 Production on Wed Jan 1 23:22:20 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Use "connect username/password@XE" to connect to the database.
SQL> conn hr/hr 
Connected.
SQL> truncate table sperrorlog;

Table truncated.

SQL> SET ERRORLOGGING ON
SQL> INSERT INTO bad_table(a) VALUES(1);
INSERT INTO bad_table(a) VALUES(1)
            *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> SET ERRORLOGGING OFF
SQL> ROLLBACK;

Rollback complete.

SQL> SELECT timestamp, script, substr(message,1,100) message FROM sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
30-JAN-14 01.44.27.000000 PM

ORA-00942: table or view does not exist
SQL>

Logging is now done into SPERRORLOG VIEW via trigger, that executes insert into T_SPERRORLOG table in autonomous transaction.
Even if the transaction is rolled back, the error logs are persisted.

One comment on “SQLPlus ERRORLOGGING issues with error log on rollback resolved

  • Hey – A very good article and proof depicting how one can achieve the concept of autonomous transaction to trap errors where SQL*PLUS fails.

    As they say the proof of pudding lies in it’s eating, I will try this one out and see for myself as that will help me appreciate this learnt concept.

    Cheers!
    Sandeep

Leave a Reply to Sandeep Cancel reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.