Owb - Error Handling (Error Logging Table and Data Rules)

Card Puncher Data Processing


Error logging enables the processing of DML statements to continue despite errors being encountered during the statement execution. DML error logging is supported for SQL statements such as INSERT, UPDATE, MERGE, and multi-table insert. It is useful in long-running, bulk DML statements - for example processing 1 million records and 10 fail, with DML error logging all good records can be committed and the 10 error rows recorded in an error table. Until OWB this was only possible with row based mapping code, now it is possible in set based mode also.

Warehouse Builder provides the Oracle - PL/SQL - DML error logging database features.

DML error logging is :

  • supported only for target schemas created in Oracle DB 10g R2 or later.
  • work for tables, views, and materialized views used in set-based PL/SQL mappings.

See the DML error logging article for more information on this plsql features.

Enable error logging

The best way to turn on this feature is to add a OWB - Data Rules. See this article for more detail : OWB - How to implement and manage data rule in a mapping for a table ?

To turn on this features without OWB - Data Rules, you have to fill the error table name property parameters for DML Error logging in the Error Table Properties from the table operator.

Case Without Error table name With Error table name
Without data rule Not Enable Enable
With data rule Enable with the shadow table name table_name_err Enable

If you modify the error table name for a data object (using the Error table name property), you must synchronize all the operators bound to this data object

With data rule

Logging error table

When the error logging table is enabled ( See OWB - How to implement and manage data rule in a mapping for a table ? ) and if you don't set the name of the error table in the properties, by default, it will take the form :

name of the target table + '_ERR'

Table Operator Notice that the the operator has two groups :
* The top group is the regular in/out group for the table data and you can map from and to this group.
* The second group (ERR_GROUP above) reflects all the columns that are in the logging error table and can be mapped from (not into).
So there is no need to join these two tables or to import the logging error table to retrieve rows from. You can map from both groups in the same mapping and even join them back together (simply think of them as two tables).

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.

Error Table Definition

The Logging Error Table have two set of columns :

  • ORA_ERR_* columns to manage Oracle Error during the process (Constraint, …)
  • ERR$$_* columns to manage the data rule error.

For more detail about the ORA_ERR_* columns of this table, see this article Oracle - PL/SQL - DML error logging

Column Definition
ERR$$_ERROR_ID I don't know the source table but the id is the same by data rule type.
* 2 for NAME_ADRESS_CHECK, 9 for telephon, 10 for not null
ERR$$_ERROR_REASON A description of the error
ERR$$_SEVERITY A number to define the severity (but I don't know the signification, I have always the number 3)
ERR$$_OPERATOR_NAME The operator name in the mapping
ERR$$_ERROR_OBJECT_NAME The rule name in the operator name
ORA_ERR_NUMBER$ Oracle Error number causing the error
ORA_ERR_MESG$ Oracle Error message text describing the error
ORA_ERR_ROWID$ Rowid of the row causing the error (this is for updates and deletes)
ORA_ERR_OPTYPE$ Type of operation causing the error: I = Insert, U = Update, D = Delete
ORA_ERR_TAG$ Step or detail audit ID from the Warehouse Builder runtime audit data. This is populated with the STEP_ID in the ALL_RT_AUDIT_STEP_RUNS runtime public view

When the error table logging is created ?

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.

For instance, when you add (APPLY) a data rule to a table, you will see that a script name script_name_ERR.ddl appear. It's the script to create the Error table.

   "ORA_ERR_MESG$"   VARCHAR2(2000),
   "ORA_ERR_TAG$"   VARCHAR2(2000),
   "ERR$$$_AUDIT_RUN_ID"   NUMBER(22),
   "ERR$$$_ERROR_ID"   NUMBER(22),
   "ERR$$$_ERROR_REASON"   VARCHAR2(2000),
   "ADDRESS"   VARCHAR2(35),
   "CITY"   VARCHAR2(35),
   "FAX"   VARCHAR2(31),


The mapping which contain data rules run in PL/SQL before the patch (as opposed to SQL, set-based) mode and therefore might not perform as well as ones without data rules applied.

From Dave Allan, since the patch the data rule enabled map can also be executed set based, there were some fixes to support set based delete that made this possible. In 11gR2 also there are some changes in error table.

Without data rule

Without data rule, you have to create the error table with the help of the package DBMS_ERRLOG.CREATE_ERROR_LOG (see this article) and to fill it in the error table field name.

Example :

exec DBMS_ERRLOG.CREATE_ERROR_LOG ('table_name')

The name syntax of the error table will be


Owb Error Table Name Manually

this techniques requires that the mapping must be processed in set-based mode


Discover More
Card Puncher Data Processing
Application - Fault Handling

fault handling
Owb Table Operator Properties
OWB - Error Table Properties

This page describe the Error Table section of properties and you can find it only for the mapping operators where a load can be perform : Table, View , Materialized View, External Table, ...
Card Puncher Data Processing
OWB - How to debug a Mapping ?

Oracle Warehouse Builder give several way to find information to debug a mapping : a that enables you to see the data flow through the mapping in a graphical interface. The mapping debugger is embedded...
Owb Table Operator With Err Table
OWB - How to implement and manage data rule in a mapping for a table ?

A data rule in OWB is a property of a table and you can manage its behaviour in the mapping. To implement data rule, you must : in the objects : define a then apply (of add) them to the table...
Owb Load Fact Mdm
OWB - How to load a fact table for a data quality cube ?

In one mapping, you can check all the data rules against a table and you can load the results in one fact table. On the check table STG_VENDOR_SAP4 (in the bottom left side), I have set the data rules...
Owb Table Operator Data Rule Properties
OWB - Table

The Table operator enables you to source data from and load data into tables stored You can set Target Filter for Update, Target Filter for Delete, and Match By Constraint. When a data...
Card Puncher Data Processing
Oracle Warehouse Builder

Owb Documentation - Understanding Data Quality Management White...

Share this page:
Follow us:
Task Runner