Oracle - PL/SQL - DML error logging

Introduction

LOG ERRORS handles errors quickly and simplifies batch loading.

When you need to load millions of rows of data into a table, the most efficient way is usually to use an INSERT, UPDATE, or MERGE statement to process your data in bulk. Similarly, if you want to delete thousands of rows, using a DELETE statement is usually faster than using procedural code. But what if the data you intend to load contains values that might cause an integrity or check constraint to be violated, or what if some values are too big for the column they are to be loaded into?

You may well have loaded 999,999 rows into your table, but that last row, which violates a check constraint, causes the whole statement to fail and roll back. In situations such as this, you have to use an alternative approach to loading your data.

For example, if your data is held in a file, you can use SQL*Loader to automatically handle data that raises an error, but then you have to put together a control file, run SQL*Loader from the command line, and check the output file and the bad datafile to detect any errors.

If, however, your data is held in a table or another object, you can write a procedure or an anonymous block to process your data row by row, loading the valid rows and using exception handling to process those rows that raise an error. You might even use BULK COLLECT and FORALL to handle data in your PL/SQL routine more efficiently, but even with these improvements, handling your data in this manner is still much slower than performing a bulk load by using a direct-path INSERT DML statement.

Until now, you could take advantage of the set-based performance of INSERT, UPDATE, MERGE, and DELETE statements only if you knew that your data was free from errors; in all other circumstances, you needed to resort to slower alternatives. All of this changes with the release of Oracle Database 10g Release 2, which introduces a new SQL feature called DML error logging.

Efficient Error Handling

DML error logging enables you to write INSERT, UPDATE, MERGE, or DELETE statements that automatically deal with certain constraint violations. With this new feature, you use the new LOG ERRORS clause in your DML statement and Oracle Database automatically handles exceptions, writing erroneous data and details of the error message to an error logging table you've created.

Articles Related

The Error Table

Error tables store error details. You can define error tables for tables, views, and materialized views only. Error tables are used for the following purposes:

• DML error logging (including physical errors).
• Capturing logical errors when data rules are applied to tables, views, or materialized views.

An error table is generated and deployed along with the base table, view, or materialized view if the shadow table name is set. The error table will have the following columns for DML errors.

Before you can use the LOG ERRORS clause, you need to create an error logging table, either manually with DDL or automatically with the CREATE_ERROR_LOG procedure in the DBMS_ERRLOG package, whose specification is shown in Listing 1.

Code Listing 1: DBMS_ERRLOG.CREATE_ERROR_LOG parameters

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);

All the parameters except DML_TABLE_NAME are optional, and if the optional details are omitted, the name of the error logging table will be ERR_ together with the first 25 characters of the DML_TABLE_NAME. The SKIP_UNSUPPORTED parameter, if set to TRUE, instructs the error logging clause to skip over LONG, LOB, and object type columns that are not supported and omit them from the error logging table.

With the error logging table created, you can add the error logging clause to most DML statements, using the following syntax:

LOG ERRORS [INTO [schema.]table] 
[ (simple_expression) ] 
[ REJECT LIMIT  {integer|UNLIMITED} ]

The INTO clause is optional; if you omit it, the error logging clause will put errors into a table with the same name format used by the CREATE_ERROR_LOG procedure. SIMPLE_EXPRESSION is any expression that would evaluate to a character string and is used for tagging rows in the error table to indicate the process that caused the error, the time of the data load, and so on. REJECT LIMIT can be set to any integer or UNLIMITED and specifies the number of errors that can occur before the statement fails. This value is optional, but if it is omitted, the default value is 0, which effectively disables the error logging feature.

Type of error handled

The following types of errors are handled by the error logging clause:

• Column values that are too large
• Constraint violations (NOT NULL, unique, referential, and check constraints), except in certain circumstances detailed below
• Errors raised during trigger execution
• Errors resulting from datatype conversion between a column in a subquery and the corresponding column of the table
• Partition mapping errors

The following conditions cause the statement to fail and roll back without invoking the error logging capability:

• Violated deferred constraints
• Out-of-space errors
• Any direct-path INSERT operation (/*+ APPEND */) (INSERT or MERGE) that raises a unique constraint or index violation
• Any UPDATE operation (UPDATE or MERGE) that raises a unique constraint or index violation

In addition, you cannot track errors in the error logging table for LONG, LOB, or object type columns.

Statement

Creating the err_sales_target error logging table

SQL> BEGIN
  2       DBMS_ERRLOG.CREATE_ERROR_LOG('SALES_TARGET');
  3    END;
  4    /
PL/SQL procedure successfully completed.

SQL> DESCRIBE err$_sales_target;
 Name                    Null?   Type
 ------------------- 	 ---- 	 ------------- 
 ORA_ERR_NUMBER$                 NUMBER
 ORA_ERR_MESG$                   VARCHAR2(2000)
 ORA_ERR_ROWID$                  ROWID
 ORA_ERR_OPTYP$                  VARCHAR2(2)
 ORA_ERR_TAG$                    VARCHAR2(2000)
 SALES_ID                        VARCHAR2(4000)
 CUST_ID                         VARCHAR2(4000)
 PROD_ID                         VARCHAR2(4000)
 CHANNEL_ID                      VARCHAR2(4000)
 TIME_ID                         VARCHAR2(4000)
 PROMO_ID                        VARCHAR2(4000)
 AMOUNT_SOLD                     VARCHAR2(4000)
 QUANTITY_SOLD                   VARCHAR2(4000)

Violating the constraints and logging the errors with LOG ERRORS

SQL> INSERT  /*+ APPEND */
  2    INTO     sales_target
  3    SELECT  *
  4    FROM    sales_src
  5    LOG ERRORS
  6    REJECT LIMIT UNLIMITED
  7    ;
918834 rows created.
Elapsed: 00:00:05.75

SQL> SELECT count(*)
  2    FROM   err$_sales_target
  3    ;

  COUNT(*)
-----	
            9

Elapsed: 00:00:00.06

Documentation / Reference

• Faster Batch Processing
• Owb - Set based errors - DML Error Logging
• DBMS_ERRLOG package
• Oracle® Database Data Warehousing Guide 10g Release 2 (10.2) - Error Logging and Handling Mechanisms