Oracle Select and Delete in job

yugee · Post by **yugee** » Mon Jun 02, 2014 12:44 pm

I have a job which extract the records from Oracle table and inserts into another oracle table. All the records successfully inserted should be deleted from the input table. For this I have below design:

OC Extract
Copy ---> one output is going to OC_insert and another is for Lookup
OC Insert --> I have configured reject link and this is the reference for the lookup
Lookup---> Input is the extract records and reference is the Rejected records.
OC Delete--> I have configured to delete all the umnatched (rejected)records from the lookup

This design works fine if there are no issues in the job. But when the oracle insert fails due to some issue, job aborts but the input records are getting deleted. This is because Copy stage is producing all the recrords and even though the OC Insert stage failed, the reject link is producing zero records and my lookup is thinking no rejects and hence all the inputs are deleted. Is there any way to change this design?

yugee · Post by **yugee** » Mon Jun 02, 2014 12:59 pm

Since I can not use the Oracle reject as reference link to Lookup, I have used a Copy stage. Though the input link to Copy stage is failed, output produced zero records..Is there any way to avoid this..