Reject Link from an Oracle Enterprise stage - DELETE action
Posted: Mon Jan 19, 2009 10:12 pm
We have an archiving process that deletes rows from a top level table, which then uses DELETE CASCADE RI constraints to remove rows from subordinate tables - potentially many levels deep.
In some cases, a low level table may have two parent foreign keys - ie. two different paths by which it may be archived. In such cases, we pick the "main" one and make it DELETE CASCADE and the other one ON DELETE NO ACTION. This has the desired result of preserving the row until both parents are ready to archive.
The problem is that some attempted deletes fail.
What I want to happen is for these "failed" deletes to be output to a Reject link like an Update/Upsert action, but DS will not allow me to add a reject link to an Oracle enterprise stage with a DELETE action.
It looks like there is no way to suppress or handle these errors (short of using PL/SQL).
I have tried using a Server job with the reject link hanging off the transformer, but that generates a line in the log for EVERY row that fails. I don't want to expose the logs to an unknown volume of entries - it could get out of control.
Any ideas?
In some cases, a low level table may have two parent foreign keys - ie. two different paths by which it may be archived. In such cases, we pick the "main" one and make it DELETE CASCADE and the other one ON DELETE NO ACTION. This has the desired result of preserving the row until both parents are ready to archive.
The problem is that some attempted deletes fail.
Code: Select all
ORA-02292: integrity constraint (OFXDEV.CONTACT_ID) violated - child record found
It looks like there is no way to suppress or handle these errors (short of using PL/SQL).
I have tried using a Server job with the reject link hanging off the transformer, but that generates a line in the log for EVERY row that fails. I don't want to expose the logs to an unknown volume of entries - it could get out of control.
Any ideas?