SQL*Plus error logging – A forgotten feature

Some days ago a customer show me a script where he did intensive PL/SQL Code for logging errors during exection. This additional code was responsible for same Performance Problems during the script execution. After same code Review i find out that there was a much easier way the same Debugging without the Overhead.

You can also check the documentation on SPERRORLOG

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

SQL> set errorlogging on;
SQL> desc sperrorlog;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                           VARCHAR2(256)
 TIMESTAMP                                          TIMESTAMP(6)
 SCRIPT                                             CLOB
 IDENTIFIER                                         VARCHAR2(256)
 MESSAGE                                            CLOB
 STATEMENT                                          CLOB

For example i generate a SQL error (dual Table wrong written)

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

You can now easily query the script error

SQL> select timestamp, username, script, statement, message from sperrorlog;
16-JUN-15 11.20.45.000000 PM   ARGOS_ECM             select * from dua   ORA-00942: table or view does not exist

If you have more then one session working, then you can set a session identifier

SQL> set errorlogging on identifier working_on_big_batch_job

Above mentioned IDENTIFIER keyword becomes a column in SPERRORLOG table. It would get populated with the string value “working_on_big_batch_job”.

select timestamp, username, script, statement, message from sperrorlog where identifier = 'working_on_big_batch_job';

Also keep in mind that the records in the error table are created in your transaction. This means if you issue a rollback then also the records in the SPERRORLOG table will be rolled back.

As always comments are welcome.