LOG ERRORS clause in 10gR2

This was a feature that was introduced in 10gR2 version and is a pretty nifty feature for developers. This clause “LOG ERRORS” is applicable to all the DML statements in Oracle – Insert, Update, Delete and Merge. What this facilitates is the ability to log the failed records rather than failing the entire bulk load itself. For example:

Insert /*+ APPEND */ into Test_Table (col1, col2, col3)

select a, b, c from Another_Table

LOG ERRORS into ERRLOG (‘LOG_FOR_TEST_TABLE’)

REJECT LIMIT UNLIMITED;

What this does is that it will log the failed records into the table LOG_FOR_TEST_TABLE. Not only will it log the failed record(s), it will also log the Oracle error number, the text of the error message, the type of the DML operation that was being performed and the ROWID values if it is an Update or a Delete statement. So, if you are doing say a direct path insert into a table and you are going to insert say 1000,000 records into it, just because a single record fails because of a bad data condition, the entire load should not fail. Use of this feature makes this a breeze without compromising on performance since you can still do the SET based operations.