Archive for September, 2012

Insert with log errors

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:

   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

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.

Categories: Database, Oracle