Error Logging Tables.

Error logging tables are a new feature introduced in oracle 10g release 2. They are used in places where the a unique constraint or a check constraint of a single row can result in the failure of a DML statement involving huge amount of data. For example you are importing data from a temporary table which has say a million records. The insert statement runs for about an hour and then gives a unique constraint error and rolls back all of the data inserted. In these cases wouldn't it be nice if the SQL inserted all the rows except the ones which generated the errors and logged the erroneous records in a separate table. Error logging tables do exactly that.

We can create an error table by using the procedure dbms_errlog.create_error_log.

The following syntax can be used to log the errors into the log table.

LOG ERRORS [INTO [schema.]table]
[ (simple_expression) ]
[ REJECT LIMIT {integer|UNLIMITED} ]


Below is the sample code to understand how the new error logging works.

drop table src ;

drop table tgt;

CREATE TABLE src (x,y,z)
AS
SELECT object_id
, object_type
, object_name
FROM all_objects
WHERE ROWNUM <= 5;


CREATE TABLE tgt
AS
SELECT *
FROM src
WHERE ROWNUM <= 3;

alter table tgt add constraint pky primary key (x);

insert into tgt
select * from src;

drop table error_tgt;

exec dbms_errlog.create_error_log(dml_table_name=>'tgt',err_log_table_name=>'error_tgt');

insert into tgt
select * from src
log errors into error_tgt ('log errors testing') reject limit unlimited;

select x , y ,z ,ORA_ERR_TAG$ from error_tgt;


References :
Natalka's Blog on ORAFAQ
Tim's OracleBase site
Databasejournal site
Adrian Billington's site
Mark Rittman's article in Oracle Magazine