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

> Data Integration Tool (ETL/ELT) > Oracle Warehouse Builder

1 - About

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 10.2.0.3 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.

Advertising

3 - 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

4 - With data rule

4.1 - 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.
Advertising

4.2 - 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$$$_AUDIT_RUN_ID Linked to the column WB_RT_AUDIT_DETAIL.RTA_IID
ERR$$$_AUDIT_DETAIL_ID Linked to the column WB_RT_AUDIT_DETAIL.RTD_IID
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
Advertising

4.3 - 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.

CREATE TABLE "MDM_VENDOR_ERR"  (
   "ORA_ERR_NUMBER$"   NUMBER,
   "ORA_ERR_MESG$"   VARCHAR2(2000),
   "ORA_ERR_ROWID$"   ROWID,
   "ORA_ERR_OPTYP$"   VARCHAR2(2),
   "ORA_ERR_TAG$"   VARCHAR2(2000),
   "ERR$$$_AUDIT_RUN_ID"   NUMBER(22),
   "ERR$$$_AUDIT_DETAIL_ID"   NUMBER(22),
   "ERR$$$_ERROR_ID"   NUMBER(22),
   "ERR$$$_ERROR_REASON"   VARCHAR2(2000),
   "ERR$$$_SEVERITY"   VARCHAR2(1),
   "ERR$$$_OPERATOR_NAME"   VARCHAR2(30),
   "ERR$$$_ERROR_OBJECT_NAME"   VARCHAR2(30),
   "ADDRESS"   VARCHAR2(35),
   "CITY"   VARCHAR2(35),
   "COUNTRY"   VARCHAR2(3),
   "CUSTOMER_ID"   VARCHAR2(10),
   "FAX"   VARCHAR2(31),
...

4.4 - Performance

The mapping which contain data rules run in PL/SQL before the 10.2.0.3 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 10.2.0.3 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.

5 - 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

ERR$_TABLE_NAME

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

6 - Reference