X

Welcome to All Things Warehouse Builder

  • ETL
    June 8, 2011

OWB 11gR2 – DML Error Logging

David Allan
Architect

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.

Join the discussion

Comments ( 3 )
  • guest Tuesday, October 18, 2011

    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


  • David Tuesday, October 18, 2011

    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


  • dave Tuesday, January 28, 2014

    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


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.