Wednesday, October 8, 2014

Migrating OWB to ODI: ORA-02292 integrity constraint (DEV_ODI_REPO.FK_MAP_EXPR_REF_3) violated - child record found

I have run into a persistent error when trying to migrate my OWB mappings to ODI. I use this simple mapping for test purposes:
2014-10-08 10_53_15-Oracle Warehouse Builder _ _DEMO_DB11G_DEMO_MAPPING_2
When I migrate this to ODI I get the following result:
2014-10-08 11_10_10-Oracle Data Integrator Studio 12c _ MAPPING_2
So far so good and exactly as expected. Now Expression_0 is no longer needed and has to be removed, so select the object, press delete and save. The object disappears, but the 2014-10-08 11_13_38-Oracle Data Integrator Studio 12c _ MAPPING_2 icon stays highlighted. Pressing the icon again seems to save the changes. Close and reopen the mapping, and lo and behold the expression_0 object is still present. Now delete it again and press the save all button instead and…..
Unable to save MAPPING_3 (ODI-10143: Error while accessing the ODI repository.   
ORA-02292 integrity constraint (DEV_ODI_REPO.FK_MAP_EXPR_REF_3) violated - child record found
I never used to get this error before, but I think I might just have pressed the save button and not noticed the object did not disappear. This looks like a bug in the migration utility to me.
There is a workaround, but that requires me to modify the original OWB mapping, I have not yet found a way to solve the problem in ODI.
The workaround is as follows:
  • either remove the expression in OWB before the migration
  • or replace the expression by a constant (you can still use expressions in a constant and since it the input for a pre-mapping it will only be evaluated once)

No comments:

Post a Comment