SQL*Plus error logging – New feature release 11.1

articles: 

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

Comments

The following feature fails to log an error when a ROLLBACK is issued. I will create another post to explain it in details and the workaround for it.

Very nice facility, I didn't know about it.

I've tested, and the table is permanent, created in your schema the first time you SET ERRORLOG ON. I've replaced it with an on commit preserve rows global temporary table, which avoids the need to keep truncating it. I tried a public synonym for this (so it would work in the same way that the PLAN_TABLE works) but SQL*Plus doesn't like the public synonym. You can however create a private synonym to a common global temporary table, that works perfectly.

Thanks John for sharing the idea of on commit preserve rows GTT, it really avoids the need to truncate SPERRORLOG table.

I will share an issue with ERRORLOGGING when we issue ROLLBACK in a session.

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.

I am working on the workaround. Will post it once I am done with test cases preparation.

Regards,
Lalit

Please refer SQL*Plus error logging - workaround for ROLLBACK issue for the workaround to the ROLLBACK issue mentioned in above comment.

Nice article Lalit... Very clearly explained with examples. keep on posting such interesting articles.

Will try to post more, however, most of the topics have already been touched by others, so left with very few.

Regards,
Lalit

It works between sessions as well i.e. the error raised in one session can be queried in different session using sperrorlog, ofcourse commit is requited.

Regards,
Pointers

@Pointers,

Just like a normal table, it will show you errors from other sessions only when a commit is issued in those sessions.

But before that, you must SET ERRORLOGGING ON in each session to enable error logging. A better idea is, as John suggested, to have a Global temporary table with on commit preserve rows.

Regards,
Lalit