Reject Link from an Oracle Enterprise stage - DELETE action

[rleishman]

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.

Code: Select all

ORA-02292: integrity constraint (OFXDEV.CONTACT_ID) violated - child record found

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?

[rleishman]

I'll take that overwhelming round of silence as a "dunno". Either that or I didn't express the problem clearly.

Anyway, as a heads-up to anyone who searches on this in the future, I'll give an update on how we proceeded.

Rather than use DS to fetch the rows to delete in an Enterprise Stage and then delete then in another Enterprise Stage (with a DELETE action), we decided to use an Oracle stored procedure.

The Stored procedure will load the ROWIDs to delete into a Global Temporary Table (to avoid ORA-01555), fetch them from the GTT using BULK COLLECT 1000 rows at a time, then FORALL DELETE to delete them 1000 rows at a time using SAVE EXCEPTIONS to capture the ones with child FK issues, which will be placed in a second GTT.

This stored procedure will be called from the Before-SQL of an Oracle Stage in DS, then that Oracle Stage will select the failed deletes from the GTT and drop them to a file in case Prod Support want to inspect them.

[gnan_gun]

Hi,

For the delete Oracle stage can not support the reject link. It will show the following message in output tab of oracle stage "The currently selected link is a reject output link. There are no properties that can be set for this type of link".