10gR2 New Feature: DML Error Logging

DML error logging is a new feature for 10gR2. Have you ever tried to update 30 million records, only to have the update fail after twenty minutes because one record in 30 million fails a check constraint? Or, how about an insert-as-select that fails on row 999 of 1000 because one column value is too large? With DML error logging, adding one clause to your insert statement would cause the 999 correct records to be inserted successfully, and the one bad record to be written out to a table for you to resolve.

This article will show you how to use DML error logging in your INSERT, UPDATE, MERGE and DELETE statements.

Getting started

Let's start by creating a table with a few constraints for us to violate:

SQL> create table dmlel 
  2>   (pkey varchar2(100) primary key, field1 varchar2(1), field2 varchar2(10) not null);

Table created.

Now, let's write a script that will fail without DML error logging. The following script will insert a few rows, and then fail with "value too large for column":

declare i number;
begin
  i := 0;
  while i <= 10 loop
    insert into dmlel (pkey, field1, field2)
    values (i, i, i);
    i := i+1;
  end loop;
end;
/
*
ERROR at line 1:
ORA-12899: value too large for column "BULKLOAD"."DMLEL"."FIELD1" (actual: 2, maximum: 1)
ORA-06512: at line 5

In order to handle this error with DML Error Logging, we first have to create a table for the DML errors to be logged to. Oracle refers to such a table, unsurprisingly, as an error logging table.

Creating the Error Logging Table

There are two ways to create the error logging table -- automatically or manually. We'll start with the automatic method. Oracle supplies a built-in pl/sql package,
DBMS_ERRLOG, specifically for this purpose.

SQL> begin
  2  dbms_errlog.create_error_log('DMLEL','ERROR_LOG_DMLEL') ;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> desc error_log_dmlel
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORA_ERR_NUMBER$                                    NUMBER
 ORA_ERR_MESG$                                      VARCHAR2(2000)
 ORA_ERR_ROWID$                                     ROWID
 ORA_ERR_OPTYP$                                     VARCHAR2(2)
 ORA_ERR_TAG$                                       VARCHAR2(2000)
 PKEY                                               VARCHAR2(4000)
 FIELD1                                             VARCHAR2(4000)
 FIELD2                                             VARCHAR2(4000)

You can also create the error logging table manually, using standard DDL. Manually creating the error logging table gives you more control over which source-table columns are duplicated in the error logging table. DBMS_ERRLOG.CREATE_ERROR_LOG copies all the table columns into the error log (unless the table has columns in unsupported datatype; see "Errors handled by DML Error Logging" below). But if you have a large table with many large columns, this can create unnecessary overhead. To avoid that overhead, create the error log manually. The only mandatory columns are the five ORA_ERR_* columns listed above; your table must contain these columns with the datatypes and lengths listed above.

SQL> create table error_log_dmlel_2
  2  (ora_err_number$ number, 
  3   ora_err_mesg$ varchar2(2000),
  4   ora_err_rowid$ rowid,   
  5   ora_err_optyp$ varchar2(2),
  6   ora_err_tag$ varchar2(2000) );

Table created.

See the error logging table specification for more information.

Logging an Error

Once the DML error logging table has been created for a particular table, all that's needed to log DML errors against that table is to add an error logging clause to your insert, update, merge or delete statement.

declare i number;
begin
  i := 0;
  while i <= 10 loop
    insert into dmlel (pkey, field1, field2)
    values (i, i, i)
    LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;
    i := i+1;
  end loop;
end;
/

PL/SQL procedure successfully completed.

With the addition of the error logging clause, this anonymous pl/sql block completed gracefully instead of halting pl/sql execution with an ora-12899 error on the last insert. And the failure on the last insert was logged to error_log_dmlel:

SQL>set lines 110
SQL> col num$ for 9999999  
SQL> col ora_err_mesg$ for a50
SQL> col ora_err_rowid$ for a25
SQL> col typ for a3 
SQL> col pkey for a4
SQL> col field1 for a4
SQL> select ora_err_number$ num$, ora_err_mesg$, ora_err_rowid$, ora_err_optyp$ typ, pkey, field1 
  2  from error_log_dmlel
SQL> /

      NUM$ ORA_ERR_MESG$                                      ORA_ERR_ROWID$            TYP PKEY FIEL
---------- -------------------------------------------------- ------------------------- --- ---- ----
     12899 ORA-12899: value too large for column "BULKLOAD"."                           I   10   10  
           DMLEL"."FIELD1" (actual: 2, maximum: 1)

The Oracle error number is stored in ORA_ERR_NUMBER$ and the message in ORA_ERR_MESG$. The ROWID value here is null because an insert failed (ORA_ERR_OPTYP$ of I), but it would be populated with the failing row's ROWID for an update, merge or delete statement. And the columns in ERROR_LOG_DMLEL that correspond to DMLEL's columns have been populated with the data of the row that failed insertion.

And now, an interesting feature of DML error logging:

SQL> rollback ;

Rollback complete.

SQL> select ora_err_number$ num$, ora_err_mesg$, ora_err_rowid$, ora_err_optyp$ typ, pkey, field1 
  2  from error_log_dmlel 
SQL> /

      NUM$ ORA_ERR_MESG$                                      ORA_ERR_ROWID$            TYP PKEY FIEL
---------- -------------------------------------------------- ------------------------- --- ---- ----
     12899 ORA-12899: value too large for column "BULKLOAD"."                           I   10   10  
           DMLEL"."FIELD1" (actual: 2, maximum: 1)

It seems that DML errors are logged as an autonomous transaction; rolling back the DML that spawned the error does not clear the error logging table. (The error logging table can be cleared with a standard DELETE statement, of course.) This makes DML error logging handy even when you do want a statement to fail if not all rows go through:

1. Execute DML statement.
   
2. Check error logging table for errors.
   
3. If no errors found in the error logging table, commit. Else, roll back.
   
4. You can now pass information on exactly which rows failed to your application or debug log.
   

Syntax

Now that we've worked through an example, let's examine the error logging clause in more detail. Its syntax is simple:

LOG ERRORS [INTO schema.table] [ (simple_expression) [ REJECT LIMIT {integer|UNLIMITED} ]

See complete syntax diagram at http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/error_logging_clause.gif

Specifics:

• Note that the name of the error logging table ("INTO table") is optional. If a table name is not specified, the default is the same as the default table name created by DBMS_ERRLOG.CREATE_ERROR_LOG, which is 'ERR$_' || substr(table_name,1,25) .
  
• While the "REJECT LIMIT" clause is technically optional, the default reject limit is zero, so the error logging clause is ineffective if a reject limit is not specified.
  
• The "simple_expression" subclause allows you to specify a statement tag, which will be logged in the ORA_ERR_TAG$ field of the error logging table, to identify which statement caused the error. More on this below.
  

Tagging your error messages

If you're coding a complex package, you might find it handy to issue multiple DML statements with error logging, then handle all the logged errors at once. In that case, it would be nice to know which statement produced which error. This is where the ORA_ERR_TAG$ field and the "simple expression" in the error logging clause come into their own.

In the listing below, I've used
DBMS_RANDOM
to randomize the values used by the insert statements in the loop. The 101 insert statements issued in the loop will attempt to insert a value between 0 and 9 into dmlel.pkey ; as this field is the primary key, I would expect 10 of them to succeed and 91 to fail, but there's no way to know ahead of time which statements will succeed or fail.

declare i number;
begin
  i := 0;
  dbms_random.initialize(2);
  while i <= 100 loop
    insert into dmlel (pkey, field1, field2)
    values (trunc(dbms_random.value*10), 0, 0)
    LOG ERRORS INTO ERROR_LOG_DMLEL ('Iteration number: ' || to_char(i))
    REJECT LIMIT 1;
    i := i+1;
  end loop;
end;
/
PL/SQL procedure successfully completed.

SQL> select count(*) from dmlel ;

  COUNT(*)
----------
        10

SQL> select count(*) from error_log_dmlel ;

  COUNT(*)
----------
        91

We can identify which of the 101 statements errored out from the ORA_ERR_TAG$ field:

SQL>set lines 110
SQL> col num$ for 999999  
SQL> col ora_err_mesg$ for a65
SQL> col pkey for a4
SQL> col ora_err_tag$ for a25
SQL> ora_err_mesg$, pkey, ora_err_tag$
  2  from error_log_dmlel
  3  order by to_number(substr(ora_err_tag$,19,4))
SQL> /

      NUM$ ORA_ERR_MESG$                                                     PKEY ORA_ERR_TAG$
---------- ----------------------------------------------------------------- ---- ---------------------
         1 ORA-00001: unique constraint (BULKLOAD.SYS_C0021079) violated     2    Iteration number: 1
         1 ORA-00001: unique constraint (BULKLOAD.SYS_C0021079) violated     2    Iteration number: 3
         1 ORA-00001: unique constraint (BULKLOAD.SYS_C0021079) violated     7    Iteration number: 4
         1 ORA-00001: unique constraint (BULKLOAD.SYS_C0021079) violated     0    Iteration number: 5
         1 ORA-00001: unique constraint (BULKLOAD.SYS_C0021079) violated     2    Iteration number: 8
.....      .....                                                             ...  .....
         1 ORA-00001: unique constraint (BULKLOAD.SYS_C0021079) violated     4    Iteration number: 99
         1 ORA-00001: unique constraint (BULKLOAD.SYS_C0021079) violated     0    Iteration number: 100

Errors handled by DML Error Logging

Only a certain subset of all possible DML errors will be logged; the rest will cause the operation to abort with an error, as it would have without DML error logging. For example, violating a NOT NULL constraint on a LONG column will cause the operation to abort. I've summarized the supported errors, and their exceptions, in this table.

Conclusion

We've seen how to create DML error logging tables and modify DML to log errors instead of failing. We've explored a few situations where the features of DML error logging come in handy, and I'm sure that by now, any developers reading this article are brimming with dozens more. And best of all, this powerful new feature is easy to use. I expect to see it widely adopted by database analysts and developers in the years to come.