By David Allan on Jun 08, 2011
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.
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.
If you now generate the intermediate code generation and inspect you will now see the ‘LOG ERRORS INTO’ clause also generated.
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.
You should ensure that you are using 220.127.116.11 at least for this since there was a bug impacting the DML error logging behavior on subsequent executions.