A New Use for DML Error Logging
Join the DZone community and get the full member experience.Join For Free
DML error logging is a feature of Oracle that has been around for many years now. It's great for capturing errors during a large load without losing all of the rows that are successfully loaded.
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 its column:
One of the nice things that came into Oracle (I think) way back in version 8.0 was more detailed information when you violate those constraints. So, when I try to insert a null into that table.
Not only am I told that I got an ORA-1400, I am also told the column (VAL) that caused the error. Even if this INSERT is performed from a PL/SQL routine, I still get that information:
That might not seem particularly beneficial in this instance, but consider a more true to life application, which might have tables with dozens of columns, or even hundreds of columns. Knowing which column was in error is a handy piece of information to have when debugging.
You may also like: Oracle Quick SQL Platform.
That all seems just sweet, until I throw a little bulk binding into the mix. Here’s a package that will bulk bind an insert into the T1 table.
Even without running the code, you can see on line 6 and 7 that we are seeding an array with a null value in one of the entries, so table T1 is not going to like that upon insertion! Luckily though, we will be using the SAVE EXCEPTIONS extension to capture any errors and report on them to the calling environment. Here is what happens
The SAVE EXCEPTIONS has captured the error, but notice that the vital column name has been lost. Because SAVE EXCEPTIONS captures the error code, but the time we convert that to standard error message text, the precision has been lost.
Here is where DML error handling can step in and improve things. I’ll recode the package body to use DML error logging and dispense with the SAVE EXCEPTIONS
Notice that the error message now contains the complete information as it did from the standard SQL insert statement. That’s pretty cool. I’m not saying that you should rush out and replace all of your SAVE EXCEPTIONS code with DML error logging. You can see from the example, that there are overheads in the management of this.
You need to have the error logging table defined in advance, you would need to manage the rows from multiple sessions, and, obviously, every load must be suffixed with a query to the error logging table once to see if any errors occurred. But if you really really need that column name, using DML error logging might be the way forward for you.
Published at DZone with permission of Connor McDonald, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.