SQL*Plus error logging – 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
- Lalit Kumar B's blog
- Log in to post comments
Comments
The error logging fails when a ROLLBACK is issued
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.
SPERRORLOG - enhancement
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.
SPERRORLOG - Issue with Rollback
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 selectedNow 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
SQL*Plus error logging - workaround for ROLLBACK issue
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
Nice article Lalit... Very clearly explained with examples. keep on posting such interesting articles.
Thanks
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
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
Of course you need COMMIT to see new changes from other sessions
@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