OWB 11gR2 – DML Error Logging

A common technique for set based error handling with the Oracle database is using DML Error Logging which has been in the database since 10gR2, I posted an entry back in 2007 illustrating how to use this with OWB 10gR2. With OWB 11gR2 there is a change to how this error table is incorporated, it is now controlled much more in the mapping, so the DML error table name is defined within the table operator in the mapping (not within the table data object as in OWB 10gR2).

So if you look at a table operator in mapping, you will see the ‘DML Error table name’ property in the ‘Error table’ group and by default it has no value.

owb11gr2_dml_errors_1

By entering a name such as ‘PROD_ERRORS’ you will enable DML error logging on that table, the table will be created if it does not exist when the mapping is deployed.

owb11gr2_dml_errors_2

If you now generate the intermediate code generation and inspect you will now see the ‘LOG ERRORS INTO’ clause also generated.

owb11gr2_dml_errors_3

Looking into the code for the mapping you will see where it attempts to create the DML Error logging table if it does not exist using the DBMS_ERRLOG.CREATE_ERROR_LOG procedure.

owb11gr2_dml_errors_4

You should ensure that you are using 11.2.0.2 at least for this since there was a bug impacting the DML error logging behavior on subsequent executions.

Comments:

Hi,
I am using OWB 11gR2 (11.2.0.1), unable to track errors with error table. Where can I find a patch or next release?
Didn't find any info on Support.oracle.com; also looked for OWB in edelivery site. Not available.

thanks,
usha

Posted by guest on October 18, 2011 at 08:38 AM PDT #

Hi Usha

What do you mean by 'unable to track errors with error table.'? When you generate intermediate code can you see the 'LOG ERRORS INTO' clause as part of your statement?

Cheers
David

Posted by David on October 18, 2011 at 08:41 AM PDT #

We’ve just manually imported an mdl file into an Oracle 11.2.0.3 DB and have identified a large number of
%_ERRLOG tables created in target (which were not there previously)

Under OWB 10.2 we were seeing err$ tables in another schema.

I wanted to read up on this, Note: the tables/screenshots for this post aren’t showing up so can’t see the code/examples

Posted by dave on January 28, 2014 at 08:46 AM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today