**I did this in Oracle 11gR1 but it’s supposed to be available beginning with Oracle 10gR2

I came across this error logging utility while researching an issue.  This is great for “large” jobs that are failing because of a small number of rows.  You can skip them and deal with them later.  The idea here is to create a table that logs the rows that would normally cause a transaction to fail, and to finish the transaction using the rows that don’t cause any issue.  A simple example:

1. Create an error logging table for the table that you’re inserting into:

exec dbms_errlog.create_error_log ('YOUR_TABLE_NAME','ERROR_LOG_TABLE_NAME',NULL,NULL,TRUE);

2. Add ‘log errors into ERROR_LOG_TABLE_NAME reject limit unlimited’ to your sql statement:

insert into YOUR_TABLE_NAME (*some data*) log errors into ERROR_LOG_TABLE_NAME reject limit unlimited;

And voila!  This is enough to get you going with error logging!  Some things to keep in mind:

–Deal with the rows in the error logging table!  In my case I was working with an archiving job that sometimes ran into duplicate data and failed with ORA-00001: unique constraint violated, so the rows I logged into my error table were trash and I added a statement at the end  of the job to truncate the error logging table.  Your case might be similar or very different, just be sure you do something with the rows.

–Supposedly you can omit the ‘into ERROR_LOG_TABLE_NAME‘ from your sql:

insert into YOUR_TABLE_NAME (*some data*) log errors reject limit unlimited;

Since the procedure to create the table specifies the table you’re using and the error logging table names, Oracle should know which table to log the errors into.  This never worked for me and I kept getting ORA-00942: table or view does not exist even though I created the error logging table from the same schema.  Let me know if you get this to work.

–You can change the reject limit to something appropriate for your job.  If your job attempts to insert a million rows and 999,999 of them are failing you may want to investigate further…

insert into YOUR_TABLE_NAME (*some data*) log errors into ERROR_LOG_TABLE_NAME reject limit 1000;

–The NULLs that I used in the example are actually for the table owner and tablespace, and the TRUE value is to skip unsupported datatypes.  These are all optional, the only thing you have to specify is the name of the table you’re working with.  More info on syntax here.

 

Leave a comment to let me know what kind of things you used this for or any problems that you have with it.