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!

Put your code in <code> and </code> tags

10 Responses to “errorlogging in 11g”

  1. Dimitri Gielis Says:

    Very nice tip Laurent!

    Dimitri

  2. Asif Momen Says:

    Good one Laurent. Will test it soooooon.

  3. Patrick Wolf Says:

    That’s a real nice tip!

    Thanks
    Patrick

  4. Paweł Barut Says:

    Is there 11g Client available for Windows ?

  5. Laurent Schneider Says:

    not yet

  6. Eriks Says:

    Does this also work for inserts as select from other table?

  7. Laurent Schneider Says:

    this work for every sql command. insert/delete/alter/drop whatever…

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

    […] 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. […]

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

    […] With the Windows client, I will be able to use cool new features like errorlogging. […]

  10. sonj Says:

    Oracle 11g instantclient - basiclite can find here http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html

Leave a Reply

Use <code> and </code> to post code