SQL*Plus error logging – workaround for ROLLBACK issue

In my previous post Oracle - SQL*Plus error logging , 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.

Oracle - SQL*Plus error logging

SQL*Plus New feature release 11.1

One of the most important things that a developer does apart from just code development is, debugging. Isn't it? Yes, debugging the code to fix the errors that are raised. But, in order to actually debug, we need to first capture them somewhere. As of now, any application has it's own user defined error logging table(s).

Imagine, if the tool is rich enough to automatically capture the errors. It is very much possible now with the new SQL*PLus release 11.1

A lot of times developers complain that they do not have privilege to create tables and thus they cannot log the errors in a user defined error logging table. In such cases, it's a really helpful feature, at least during the unit testing of the code.

I made a small demonstration in SCOTT schema using the default error log table SPERRORLOG, hope this step by step demo helps to understand easily :

NOTE : SQL*Plus error logging is set OFF by default. So, you need to "set errorlogging on" to use the SPERRORLOG table.

SP2 Error

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc sperrorlog;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 USERNAME                                           VARCHAR2(256)
 TIMESTAMP                                          TIMESTAMP(6)
 SCRIPT                                             VARCHAR2(1024)
 IDENTIFIER                                         VARCHAR2(256)
 MESSAGE                                            CLOB
 STATEMENT                                          CLOB

SQL> truncate table sperrorlog;

Table truncated.

SQL> set errorlogging on;
SQL> selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
SQL> select timestamp, username, script, statement, message from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------

SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

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

11-SEP-13 01.27.29.000000 AM
SCOTT


TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------

SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

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

selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.

ORA Error

SQL> truncate table sperrorlog;

Table truncated.

SQL> select * from dula;
select * from dula
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select timestamp, username, script, statement, message from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------

SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

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

11-SEP-13 01.36.08.000000 AM
SCOTT


TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------

SCRIPT
--------------------------------------------------------------------------------

STATEMENT
--------------------------------------------------------------------------------

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

select * from dula
ORA-00942: table or view does not exist

Like shown above, you can capture PLS errors too. 

If you want to execute it through scripts, you can do it like this, and later spool the errors into a file. I kept these three lines in the sperrorlog_test.sql file -

truncate table sperrorlog;
selct * from dual;
select * from dula;

SQL> @D:\sperrorlog_test.sql;

Table truncated.

SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.

select * from dula
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select TIMESTAMP, SCRIPT, STATEMENT, MESSAGE from sperrorlog;

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

STATEMENT
--------------------------------------------------------------------------------

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

11-SEP-13 01.50.17.000000 AM

D:\sperrorlog_test.sql;
SP2-0734: unknown command beginning "D:\sperror..." - rest of line ignored.


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

STATEMENT
--------------------------------------------------------------------------------

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

11-SEP-13 01.50.27.000000 AM

D:\sperrorlog_test.sql

selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.

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

STATEMENT
--------------------------------------------------------------------------------

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

11-SEP-13 01.50.27.000000 AM

D:\sperrorlog_test.sql

select * from dula
ORA-00942: table or view does not exist

SQL>

Check Oracle documentation on SPERRORLOG.

In addition to above, if you want to be particularly specific about each session's error to be spooled into a file you could do this -

SQL> set errorlogging on identifier my_session_identifier

Above mentioned IDENTIFIER keyword becomes a column in SPERRORLOG table. It would get populated with the string value "my_session_identifier". Now you just need to do this -

SQL> select timestamp, username, script, statement, message
2 from sperrorlog
3 where identifier = 'my_session_identifier';

To spool the session specific errors into a file, just do this -

SQL> spool error.log
SQL> select timestamp, username, script, statement, message
2 from sperrorlog
3 where identifier = 'my_session_identifier';
SQL> spool off

NOTE : Whenever ROLLBACK is issued in the session, the feature fails to log the errors. 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.

Please visit SQL*Plus error logging – workaround for ROLLBACK issue to see the workaround for this issue.