Yesterday I found a new and quite cool feature in Oracle 10gR2: the ability to use an error logging clause with INSERT in order to reject individual records without failing the entire statement. This can be quite useful in ETL situations, where bad data needs to be scrubbed and forced into more stringent tables.
In order to use the clause, an error log table must be created. This can be done with CREATE_ERROR_LOG:
DBMS_ERRLOG.CREATE_ERROR_LOG ( dml_table_name IN VARCHAR2, err_log_table_name IN VARCHAR2 := NULL, err_log_table_owner IN VARCHAR2 := NULL, err_log_table_space IN VARCHAR2 := NULL, skip_unsupported IN BOOLEAN := FALSE);
Alternatively the table can be created manually, which is useful if the database user lacks access rights to CREATE_ERROR_LOG:
create table app_error_log ( ora_err_number$ number, ora_err_mesg$ varchar2(2000), ora_err_rowid$ rowid, ora_err_optyp$ varchar2(2), ora_err_tag$ varchar2(2000) );
With the table in place, it is possible to insert data that would normally fail:
INSERT INTO some_table (pk, col1, col2) SELECT val1, val2, val3 FROM some_other_table LOG ERRORS INTO app_error_log REJECT LIMIT UNLIMITED;
If there are duplicate values for val1, for example, the duplicate records will be rejected and logged and the others will succeed and make it into the target table. Very useful.