Error Logging in SQLPlus

This is pretty cool. You can now log errors in to table within SQLPlus. I think when this feature first came out it was just for DML (insert, update and delete) but now it seems to work for any error that is raised within a SQLplus session.

The command to turn this on is a SQLPlus SET command, it's simplest format is:

SET ERRORLOGGING on

With this SQLPlus will start putting errors into a table called sperrorlog. What's cool about this statement is that if the table doesn't exist it will created it. In the more verbose format it would look something like this:

SET ERRORLOGGING on TABLE hsperrorlog TRUNCATE IDENTIFIER HARNESS

With this I have to create a table named hsperrorlog. The TRUNCATE option truncates the contents of the table before it starts logging anything into it. The IDENTIFIER option allows you to populate a column in the table, with the name of (you guessed it) IDENTIFIER. (Pretty cleaver eh?)

The table has these columns (all can be null):

USERNAME data type VARCHAR2(256) the user running the command.

TIMESTAMP data type TIMESTAMP(6) when the error was encountered.

SCRIPT data type VARCHAR2(1024) the name of the script being run. If this is an interactively entered command this will be null.

IDENTIFIER data type VARCHAR2(256) the optional id used when turning on error logging. You can change the identifier by just doing a set command like this:

set errorlogging ON IDENTIFIER RVD

This doesn't change the table that the errors are being logged into.

MESSAGE data type CLOB this is the error message.

STATEMENT data type CLOB this is the statement that raised the error.

I've been using it to add some needed functionality to the Hotsos Harness and it's already helped me clean up a few minor errors in the harness that have been quite hard to track down with out this.

Here is a very simple example, the output was reformatted to make this easier to read:

OP@ORCL112> SET ERRORLOGGING on TABLE hsperrorlog TRUNCATE IDENTIFIER HARNESS
OP@ORCL112> select * from XYZ;
select * from XYZ
*
ERROR at line 1:
ORA-00942: table or view does not exist

OP@ORCL112> select * from hsperrorlog;

USERNAME
-----------
OP

TIMESTAMP
----------------------------
23-NOV-10 12.59.39.000000 PM

SCRIPT
----------------------------


IDENTIFIER
----------
HARNESS

MESSAGE
---------------------------------------
ORA-00942: table or view does not exist

STATEMENT
-----------------
select * from XYZ

Check this out, it should make debugging SQL scripts a lot easier!! There are some limitations to it of course, it's not on for recursive SQL which makes a lot of sense since it could easily get into an infinite loop. Also if your scripts do reconnections, you'll have to turn it back on each time you reconnect. It is off by default.

Oh and to turn it off it looks like:

OP@ORCL112> SET ERRORLOGGING off
OP@ORCL112>
OP@ORCL112> show errorl
errorlogging is OFF