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.
Articles Related
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'
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$$_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 |
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),
...
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.
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
Reference
- LOG ERRORS handles errors quickly and simplifies batch loading not in OWB but in the PLSQL Environnement.