Search

Top 60 Oracle Blogs

Recent comments

DML error logging

DML error logging redux

I posted a nice little feature of DML error logging recently, and a comment came in mentioned that caution is needed with DML error logging because the errors are logged persistently. Hence if you ran a load multiple times, or multiple sessions were utilizing the facility, then the error logging table can quickly become a soup of data that cannot be tracked back to the code your own session was running.

By default this is true, which we can see with a trivial demo. I’ll do the same load of bad rows twice and “forget” to clear out my error table. Here’s my setup – we have a target table called TGT which has rules on nulls and integers being positive, and a source table SRC which has some data that will violate those rules when it comes to loading the data.

A new use for DML error logging

Many moons ago I did a short video on the DML error logging feature in Oracle. The feature has been around for many years now, and is a great tool for capturing errors during a large load without losing all of the rows that successfully loaded. You can watch that video below if you’re new to DML error logging.

But here is a possible new use case for DML error logging, even if you are not doing large scale loads. Let me describe the problem first, and then show how DML error logging might be a solution.

I’ll create a table with a constraint on it’s column

Combining Features - Wrong Results With Scalar Subquery Caching

Quite often you can get into trouble with Oracle when you start combining different features.In this case of one my clients it is the combination of user-defined PL/SQL functions that can raise exceptions (think of currency conversion and a non-existent currency code gets passed into the function), DML error logging and attempting to improve performance by wrapping the PL/SQL function call into a scalar subquery to benefit from the built-in scalar subquery caching feature of the SQL runtime engine.As long as the scalar subquery didn't get used everything worked as expected, but after adding the scalar subquery after some while it became obvious that wrong results occurred - in that particular case here it meant rows that should have been rejected and written to the error logging table due to the exception raised in the user-defined PL/SQL function suddenly showed up in the target table, and what was even more worrying - they included a co