11g dba sql sqlplus

errorlogging in 11g

This is a very neat feature in 11g.

I have a script called foo.sql

create table t(x number primary key);
insert into t(x) values (1);
insert into t(x) values (2);
insert into t(x) values (2);
insert into t(x) values (3);

It is eyes-popping that this script will return an error, but which one?

Let’s errorlog !

SQL>set errorl on
SQL> @foo

Table created.

1 row created.

1 row created.

insert into t(x) values (2)
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C004200) violated

1 row created.

Commit complete.

SQL> set errorl off
SQL> select timestamp,script,statement,message from sperrorlog;
---------- ------- ---------------------------
11:18:56 foo.sql insert into t(x) values (2)
ORA-00001: unique constraint (SCOTT.SYS_C004200) violated

There is also a huge bonus 😀

You can use it with 9i and 10g databases too! Only the client must be 11g. To download the 11g client only, go to Oracle E-Delivery Website

Even small, this is one of my favorite new features!

By Laurent Schneider

Oracle Certified Master

10 replies on “errorlogging in 11g”

[…] Continuing on with the Oracle 11g theme, Laurent Schneider blogs about an outstanding feature in Oracle 11g that logs the errors produced by scripts that you have run. Be sure to check it out, even if you think your code is perfect. […]

Leave a Reply

Your email address will not be published.