Tags
errorlogging rollback issue, SPERRORLOG, sperrorlog rollback issue, SQL*Plus error logging - workaround for ROLLBACK issue, sqlplus rollback error
In my previous post SQL*Plus error logging – New feature release 11.1, as a NOTE I stated an issue that 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.
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.