Home > Database, Oracle > Insert with log errors

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:

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.

Advertisements
Categories: Database, Oracle
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: