Set based errors - DML Error Logging
By David Allan on Aug 06, 2007
Error logging enables the processing of DML statements to continue despite errors being encountered during the statement execution. The details of the error such as the error code and the associated error message are stored in an error table. After the DML operation completes, you can use the error table to correct rows with errors and subsequently process. 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 error logging for the tables, views, and materialized views used in set-based PL/SQL mappings. DML error logging is supported only for target schemas created in Oracle DB 10g R2 or later.
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.
|ORA_ERR_NUMBER$||Oracle error number|
|ORA_ERR_MESG$||Oracle error message text|
|ORA_ERR_ROWID$||Rowid of the row in error (for update and delete)|
|ORA_ERR_OPTYPE$||Type of operation: insert (I), update (U), delete (D)|
|ORA_ERR_TAG$||Step or detail audit ID from the runtime audit data. This is the STEP_ID column in the runtime view ALL_RT_AUDIT_STEP_RUNS.|
If you do not want OWB to generate the error table, you can always build your own error table and supply the name to the mapping, the database provides a function DBMS_ERRLOG.CREATE_ERROR_LOG for generating an error table that can also be used.
Enabling DML Error Logging in ETL
DML error logging is generated for set-based PL/SQL mappings if the following conditions are satisfied:
- the Error table name property is set for the operator (table/view/mv)
- the PL/SQL Generated Mode of the module that contains the mapping is set to 10gR2 and above or Default.
When you use a data object in a mapping, the Error Table Name property for this data object is derived from the shadow table name property of the data object . If you modify the error table name of a data object (using the shadow table name property), you must synchronize all the operators bound to this data object.
The execution of mappings that contain data objects for which DML error logging is enabled fails if any of the following conditions occur:
- the number of errors generated exceeds the specified maximum number of errors for the mapping. The default set for this value is 50. You can modify this value by setting the Maximum number of errors configuration property of the mapping. In the Project Explorer, right-click the mapping and select Configure . In the Maximum number of errors property, specify the maximum number of errors that can generated before the mapping execution is terminated.
- errors occur due to functionality that is not supported. (see SQL Reference manual for details of DML Error logging feature restrictions). Depending on your error logging needs you can configure the table operator in a mapping to use the APPEND or NOAPPEND hint. For example, direct-path insert does not support error logging for unique key violations. To log unique key violations, use the NOAPPEND hint (be aware of performance implications of this, there is an interesting article here on some performance findings of using direct path and conventional path modes with the DML error logging feature).
The maximum number of errors was set to 50 (the default).
Now (with OWB 10.2.0.3) I get the following SQL generated now with the 'LOG ERRORS INTO ' clause generated;
LOG ERRORS INTO TSALES_ERR (get_audit_detail_id) REJECT LIMIT 50;
There is also a truncate error table property that can be used for housekeeping.
If you want to process the errors from within the mapping you can retrieve the current execution errors (that is if your error table has errors for more than one execution, it may not since your may choose to truncate at the start of the map execution) by joining the error table with the ALL_RT_AUDIT_STEP_RUNS views and use the get_runtime_audit_id variable in the join condition;
In the above map you see the first step load the TSALES table, the second step will join the error table with the ALL_RT_AUDIT_STEP_RUNS using the audit id recorded in the error table in the column ORA_ERR_TAG$ and the STEP_ID column. You could choose a different strategy here either always truncating the error table for the map or using the max step id for example.
This is a brief introduction to DML Error logging support in OWB 10.2.0.3. More to come I am sure.