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);
commit;

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;
TIMESTAMP  SCRIPT  STATEMENT
---------- ------- ---------------------------
MESSAGE
---------------------------------------------------------
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 :-D

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!

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

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>