Delete and Upsert Requirement

[samarvind]

Hi,

I have a requirement where I need to delete an incoming row from table 1 and insert or update a row into table-2. At the moment, we have delete and insert option where I delete the record from table-1 using a user-defined query and insert user-defined query to insert the row; however, we are facing an issue where the same row again turns up in the source and make the insert fail due to unique constraint error in Oracle target. Since it is a near real time job where are all the complex business logic is developed in a same job, there is no room to create another job to handle the deletes separately.

Is there way to accomplish this functionality in single stage using Oracle PX connector? I think we can use Stored procedure stage but exploring other options here.

We use DS 8.1 and for Oracle targets we use Oracle PX connector

[nayanpatra]

Perform the delete and insert operation sequentially and not in parallel. Maintain a row level commit between the operations.