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