unique constraint error

[dnat]

Hi,

I have a server job and have the target table as an oracle stage. I am doing inset/update (insert new rows or upsert existing rows). But still i am getting this oracle error
ORA-00001: unique constraint (CVID_UAT.PK_CV_WE_PRE_INSPECT) violated
Ideally we should not get this in an upsert mode, isnt it?

[swarnkar]

Hi,

I have a server job and have the target table as an oracle stage. I am doing inset/update (insert new rows or upsert existing rows). But still i am getting this oracle error
ORA-00001: unique constraint (CVID_UAT.PK_CV_WE_PRE_INSPECT) violated
Ideally we should not get this in an upsert mode, isnt it?

Your Update statement is getting failed so Datastage is trying to insert the record. Check your update query.

If Update query fails, for any reason system would go for insert.

[chulett]

Ideally you shouldn't ever get any errors in any mode.

Insert/Update means the insert is first attempted and if it fails an update is attempted. In order for the insert to fail there must be a unique index / primary key on the target that gets violated. However, any error reported from this operation is from the second action not the first and the way you get a unique constraint violation from an update is if you are updating keys bound into a unique index to a value that already exists.

[Katie09]

Yes...this error should be not be there for upsert mode..
however check the constraints on the table and the keys used for the updation...table might have more than one contraint which might be violated by the data which you are proessing..

hope this helps..

[srinagesh]

try update else insert

[kandyshandy]

so many answers going on...

DNAT, Did you add the sub property "Insert Array Size" and set it to 1 in Oracle EE stage?

[chulett]

Server job, so no "EE" stage here. And the OCI stage will automatically drop the array size down to 1 if it is not already there when using these combo actions.

[kandyshandy]

Sorry Craig. I overlooked.

[dnat]

hi,

my update clause had two keys(in the where clause as well as keys indicated in the metadata) but the table had only one key. I think this has caused the updated to fail. I am yet to do the full testing, but since i had to complete the task in a hurry, i deleted the records which had to be insert/update and reran the job..all the records were inserted properly..

so, here as craig said the issue happens to be with the update statement.