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.
Although unfortunately, the insert into the error log table is part of the same transaction. So a script that decides to issue a ROLLBACK when it encounters an error would not save the error !
Hi,
thanks for highlighting this. The error table in SQLPlus is not the general-purpose tool but can be very helpful in different Szenarios.
regards
Peter