DBMS_ERRLOG and LOG ERRORS – How It Can Help You

The other day I had to load thousands of rows of data, from an external table. During the load the data was also transformed by a function.

I was waiting patiently to type in the commit statement at the end. To my surprise, instead of getting the X number of rows inserted message, I got the error, ORA-01400: cannot insert null into (string).
Finding the troubled row or rows, was not an easy task at all … not until I found the package DBMS_ERRLOG.

To tell you the truth, I was not aware of this package, not until now, as I don’t recall using it before. Thinking back now, I might have learned about it during one of the OCP preparation exams, however I don’t remember ever using it. And most likely there are other DBAs, just like me that are not aware of it either.

Because I want to help other DBAs, I though to share this goodness with you!

Some history on the DBMS_ERRLOG package.

The DBMS_ERRLOG package was first introduced in 10.2.0.1 version together with the LOG ERRORS clause.

The DBMS_ERRLOG package enables you to create an error logging table.
This table is useful when performing DML operations on a table, such as insert, update, delete.

The operation on the table would continue even if there are rows that do not comply with the constraints on the table, and would be rejected. Instead of the whole transaction failing and being rolled back in case of a constraint violation, the DML operation continues, and all rejected rows are written into the error logging table.

I find this very useful! It can save hours of troubleshooting!

I will show you today at a high level, the process of working with this package, and then next week, I’ll walk you through an exercise with examples!

• First, you must determine which table you plan to perform the DML statements on. Let’s call this table A.
• Second, you create the error logging table for table A, using DBMS_ERRLOG package
• Third, you run the DML statement on the table A, and add the LOG ERRORS clause to the end of the statement
• Then last, you verify the error logging table for the rejected rows.

It is that simple!

Tune in next week for a full working example of the DBMS_ERRLOG package and LOG ERRORS clause!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!