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!

Published by

Laurent Schneider

Oracle Certified Master

10 thoughts on “errorlogging in 11g”

  1. Pingback: Log Buffer #60: a Carnival of the Vanities for DBAs · Steve Karam · The Oracle Alchemist

Leave a Reply