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 😀

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

  2. Pingback: 11g on Windows (32-bit) is out! « Renaps’ Blog

Comments are closed.