Oracle connector UpSert rejects some records

gaurang033 · Post by **gaurang033** » Tue Dec 27, 2011 5:51 am

Hi guys,

I am using ORACLE Connector stage.

For upsert If i use Insert then update it works fine, however as my files has updates more then insert it takes too much time.

So i Changed it to Update then insert. Now the problem is when it fails to update the rows rather then inserting it sends it to reject link.

Any idea on why ???

nagarjuna · Post by **nagarjuna** » Tue Dec 27, 2011 10:46 am

Check what is the sql reject code & depending on that you can if you have any constraints on the table .

ray.wurlod · Post by **ray.wurlod** » Tue Dec 27, 2011 3:49 pm

There are more reasons to fail an update than the row does not exist. It is only row not existing that will trigger an insert.

jreddy · Post by **jreddy** » Tue Dec 27, 2011 5:06 pm

Are you able to use a CDC change instead to determine if its a 'insert' or 'update' and then direct them to the appropriate target oracle stage ?

gaurang033 · Post by **gaurang033** » Fri Dec 30, 2011 4:32 am

To nagarjuna
It rejects with the error text as "Row not updated" with error code "0" which says successful completion.

To jreddy
Before this load job i have Transformer job where i m taking CDC, finding which are update and insert and then merging them.

The thing is my insert then update works fine, it updates all the row which are already exist.

But update then insert rejects all the new rows rather then inserting it.

For me oracle connector stage is behaving in a weird manner. Any solution where all these rows are get inserted.