Systems Engineering and RDBMS

  • Click Here for Decipher's Homepage

  • Categories

  • Questions?

    Please send your wish list of things that you would like us to write about or if you have suggestions to help improve this blog site. You can send all questions/suggestions to: Blog Support
  • Archives

  • Blog Stats


Archive for May 9th, 2007

LOG ERRORS clause in 10gR2

Posted by decipherinfosys on May 9, 2007

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



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.

Posted in Oracle | 1 Comment »