Use DML Error Logging
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 DBMS_ERRLOG 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