Use DML Error Logging

06/07/2012 in Splaining using tags oracle , sql , sqlloader

Introduced with Oracle 10gR2, DML Error Logging allows you to make standard SQL INSERT, UPDATE, and MERGE statements behave more like the SQL*Loader utility, logging errors to a table rather than failing and rolling back the first time an error condition is encountered.

The syntax is the same for all DML statements. Just add the following clause to the end of your DML:

    LOG ERRORS 

The database will automatically create an error log table for you. Per the documentation (11g, 11gR2): > … the database assigns the default name generated by the DBMSERRLOG package. The default error log table name is ERR$ followed by the first 25 characters of the name of the table upon which the DML operation is being executed.

In most cases you’ll probably want to specify your own log table, like this:

    LOG ERRORS INTO error_table

If you do, you’ll need to create the table beforehand with DBMS_ERRLOG.

    EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('table', 'error_table');

Like SQL*Loader, you can specify a maximum number of errors to tolerate before failing and rolling back the transaction. You can also just say UNLIMITED if you don’t ever want to fail the transaction.

    LOG ERRORS INTO error_table REJECT LIMIT 99
    LOG ERRORS INTO error_table REJECT LIMIT UNLIMITED

Since the default limit is zero, it makes sense to always include this clause.

The last option is to specify a string that identifies that particular DML operation, so that you can group sets of errors in the log table. Here is an example that incorporates SYSDATE to tag all errors belonging to this DML operation.

    LOG ERRORS INTO error_table ('Import from external datasource '||TO_CHAR(SYSDATE)) REJECT LIMIT UNLIMITED

Putting it all together, here is a short example demonstrating the use of DML Error Logging for merging data between two similar, but non-identically structured tables. An alternate strategy might have involved creating a custom interface table with a block of PL/SQL to perform inserts and mark the failed rows. Here, we take advantage of the native capabilities of the database to simplify and accelerate the process.

    create table users_loc (id number primary key, name varchar2(75));
    
    create table users_ext (uuid varchar2(10), fname varchar2(15), lname varchar2(20));
    insert into users_ext values('1000', 'Scotty',  'P');
    insert into users_ext values('2000', 'Melinda', 'Manning');
    insert into users_ext values('200J', 'Jamie',   'Brooks');
    insert into users_ext values('1000', 'Jason',   'Jennings');
    insert into users_ext values('4000', 'Marcus',  'Nevada');
    insert into users_ext values('AAAA', 'Alfred',  'Butler');
    insert into users_ext values('ABAB', 'Simon',   'Jones');
    insert into users_ext values('6000', 'Joon',    'Patel');
    insert into users_ext values('7000', 'Wanda',   'Lutz');
    insert into users_ext values('8000', 'Joseph',  'McBride');
    
    exec dbms_errlog.create_error_log('users_loc','users_errs');
    
    insert into users_loc (id, name) 
    select uuid, 
           fname||' '||lname 
      from users_ext 
    log errors into users_errs ('import from users_ext: '||to_char(sysdate)) 
    reject limit unlimited;
    
    col ora_err_mesg$ format a40
    col ora_err_rowid$ format a15
    col ora_err_tag$ format a50
    col id format a10
    col name format a30
    select * from users_errs;
    
    ORA_ERR_NUMBER$ ORA_ERR_MESG$                            ORA_ERR_ROWID$  ORA_ERR_OPTYP$ ORA_ERR_TAG$                                       ID         NAME                         
    --------------- ---------------------------------------- --------------- -------------- -------------------------------------------------- ---------- ------------------------------
               1722 ORA-01722: invalid number                                I              import from users_ext: 07-JUN-2012 12:00:09        200J       Jamie Brooks                   
                                                                                                                                                                                         
    
                  1 ORA-00001: unique constraint (NUCDBA.SYS                 I              import from users_ext: 07-JUN-2012 12:00:09        1000       Jason Jennings                 
                    _C00128181) violated                                                                                                                                                 
                                                                                                                                                                                         
    
               1722 ORA-01722: invalid number                                I              import from users_ext: 07-JUN-2012 12:00:09        AAAA       Alfred Butler                  
                                                                                                                                                                                         
    
               1722 ORA-01722: invalid number                                I              import from users_ext: 07-JUN-2012 12:00:09        ABAB       Simon Jones                    

comments powered by Disqus