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

SQL> create table t1 (val number not null);

Table created.

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

SQL> insert into t1 values (null);
insert into t1 values (null)
                       *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."T1"."VAL")

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:

SQL> exec insert into t1 values (null);
BEGIN insert into t1 values (null); END;

*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."T1"."VAL")
ORA-06512: at line 1

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.

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.

SQL> create or replace package bulk_ins as
  2    type t1_tt is table of t1%rowtype;
  3    procedure bulk_insert;
  4  end bulk_ins;
  5  /

Package created.

SQL> create or replace package body bulk_ins as
  2    procedure bulk_insert is
  3      l_data t1_tt:=t1_tt();
  4    begin
  5      l_data.extend(2);
  6      l_data(1).val:=999;
  7      l_data(2).val:=null;
  8      
  9      begin
 10        forall i in 1..l_data.count save exceptions
 11          insert into t1 values l_data(i);
 12      exception
 13        when others then
 14          for i in 1..sql%bulk_exceptions.count loop
 15            dbms_output.put_line( sqlerrm( -sql%bulk_exceptions(i).error_code ) );
 16          end loop;
 17      end forall_loop_with_save_except;
 18      commit;
 19    end bulk_insert;
 20  end bulk_ins;
 21  /

Package body created.

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

SQL> set serverout on
SQL> exec bulk_ins.bulk_insert;
ORA-01400: cannot insert NULL into ()

PL/SQL procedure successfully completed.

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

SQL>  exec   DBMS_ERRLOG.create_error_log(dml_table_name=>'T1')

PL/SQL procedure successfully completed.

SQL> create or replace package body bulk_ins as
  2    procedure bulk_insert is
  3      l_data t1_tt:=t1_tt();
  4    begin
  5      l_data.extend(2);
  6      l_data(1).val:=999;
  7      l_data(2).val:=null;
  8
  9      forall i in 1..l_data.count
 10        insert into t1 values l_data(i) LOG ERRORS REJECT LIMIT UNLIMITED;
 11
 12      commit;
 13    end bulk_insert;
 14  end bulk_ins;
 15  /

Package body created.

SQL>
SQL> exec bulk_ins.bulk_insert;

PL/SQL procedure successfully completed.

SQL>
SQL> select * from err$_t1
  2  @pr
==============================
ORA_ERR_NUMBER$               : 1400
ORA_ERR_MESG$                 : ORA-01400: cannot insert NULL into ("SCOTT"."T1"."VAL")
ORA_ERR_ROWID$                :
ORA_ERR_OPTYP$                : I
ORA_ERR_TAG$                  :
VAL                           :

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.

More on DML error logging here