My experiences with LOG ERRORS clause

Oracle database 10g Release 2 comes with lot of features. In this article we will see one of the most awaited features that will cheer up at least some of the faces.

The LOG ERRORS clause is the topic for the article. This clause is available to all DML statements.

What is the clause doing?
Well simply stating this clause will skip errors encountered in middle of DML operation, and insert all those errors onto another table which we mention.

What is the benefit of the clause?
Consider a situation where 1000 records are being loaded using a insert into table_name select field_names from another_table_name statement. Say for example the insert succeeds for 999 records and for the last record it gives a error say unique constraint error. Oracle will rollback all the records inserted till that point. With this clause you will be able to insert the other 999 records and only the one record that had errors will be skipped.

What is the syntax?
The syntax of the clause is very simple:

{Any_DML_Statement}
LOG ERRORS INTO {log_table_name}
REJECT LIMIT {record_count}

For Example:

INSERT INTO b_table
SELECT *
FROM a_table
LOG ERRORS INTO error_table
REJECT LIMIT 200;

Now my experience with LOG ERRORS:
I wanted to experiment how easy is to get going with LOG ERRORS clause. Believe me, this is the first time I am going to use it.

I created two tables:

create table oldtable(field1 number);

create table newtable(field1 number primary key);

insert into oldtable values(1);

insert into oldtable values(2);

insert into oldtable values(3);

insert into oldtable values(3);

commit;

select * from oldtable;

FIELD1
1
2
3
3

Now i tried without the LOG ERRORS clause:

insert into newtable select * from oldtable;

I got the following error:
ORA:00001: unique constraint(MYUSER.SYS_C00297183) violated.

Now I have to create a log_table. I did this by calling dbms_errlog.create_error_log procedure.

exec dbms_errlog.create_error_log('newtable','err_new_table');

Now I am ready for using the LOG ERRORS clause.

insert into newtable
select * from oldtable
log errors into err_new_table
reject limit UNLIMITED;

It said, 3 rows created.

Note that oldtable contains 4 records but only 3 records where inserted; the other record were rejected due to unique constraint errors.


To see the errors logged use the following query:

select ora_err_number$, ora_err_mesg$, field1
from err_new_table;

ORA_ ORA_ERR_MESG$ FIELD1
---------------------------------------------------------------
1 ORA-00001: unique constraint (ARUP.PK_ACCOUNTS) violated 3

The value 3 in FIELD1 was already inserted into newtable. The second row with value 3 was rejected through this statement.

This is really wonderful. There was no problems in getting configured.

NOTE: This clause was introduced only in Oracle database 10g Release 2.

Read more about this on Oracle