DSXchange

I need to implement Delete-and-Insert into an oracle table.I know that there is an alternative- 'upsert' - available in the enterprise edition to achieve this.But I am still curious to find out if we can implement delete & insert; since upserts are running slow on my table.
To implement delete-insert; I tried with using two streams in a job from a transformer : one for deleting rows and the other to insert rows using 'load' option.But I'm facing errors while using 'load' due to triggers defined on the table.

Could somebody please guide me.

Thanks

Hi Prasad,
Welcome Aboard :D
Pls specify what kind of error you are getting.
And more over, you cant delete the records based on certain key and Insert few records based on the same set of keys in the same job.

kumar_s wrote:Hi Prasad,
Welcome Aboard :D
Pls specify what kind of error you are getting.
And more over, you cant delete the records based on certain key and Insert few records based on the same set of keys in the same job.

Thanks, Kumar :D

I'm getting the error as : "SQL*Loader-937: parallel load requested and <table_name> has enabled triggers or constraints"
because, table has a trigger defined on it.While searching on the forums, i found a topic which mentioned that we can overcome this error by using open & close commands.But it was also mentioned there that "these commands try to run it on each processing node and possibly get duplicate or conflicting enable/disable requests".So i want to avoid the load option.

Regarding the delete and inserts, if both operations can not be implemented in the same job then my story ends here!

BTW, Is there any alternate way to implement delete-n-insert - apart from an upsert??

prasadcp wrote:BTW, Is there any alternate way to implement delete-n-insert - apart from an upsert??

Sure, two separate operations - first perform all of the deletes and then all of the inserts.

Or one after another with seperated commit in a singe user defined query.
Btw, Open command would execute before the Load Write and Close would execute after the Load write.

Hi,

You could make use of two Oracle enterprise stages in this scenario. Where in one should do the insert operation while the other does only delete operation. These stages should get input links from Transformer stage. In Transformer stage you can specify "link ordering" with "Sequential partitioning" based on your priority of DML statements execution.

Thanks & regards
Sudhindra P S