Data Upsert in Oracle DB

mansoor_nb · Post by **mansoor_nb** » Mon Aug 21, 2006 2:05 pm

Hi All,
I am repartitioning the data based on the columns Say A,B,C and sorting the incoming data on the columns A,B,C by using the sort stage. Then I am upserting the dat into the table XXX in the table which is in the Oracle DB. I am using the Upsert Mode to load the data into the table and the upsert options i have used is "Update Then Insert".The keys used for teh update is A,B,C.

The target table(Staging Table) will be empty.According to my knowledge if we use "Update then Insert", if the records are not found or if the target table is empty to update then it should be inserted into the table.

But the records are getting rejected and i am getting the SQL code as -1.I know the SQL code -1 is for unique key constraint violation, but there are no Duplicates in the Keys.

PLease Help.

kris007 · Post by **kris007** » Mon Aug 21, 2006 2:20 pm

Put a remove duplicates stage(and declare the keys) in between your sort stage and the Oracle stage and then run the job and see if it still gives the same error.

Also, in your target OCI stage, did you declare all your three columns as keys? Just double check.

HTH

mansoor_nb · Post by **mansoor_nb** » Mon Aug 21, 2006 2:58 pm

As i manetioned in my question, there are no duplicates coming in for the keys A,B,C.
I have checked my target Oracle stage,i have declared these three fields as keys.

Actually i am sorting on the 5 fields say A,B,C,D,E and while updating into the Oracle DB i am using only three key fields thais A,B,C. Is this causing an issue.

thumsup9 · Post by **thumsup9** » Mon Aug 21, 2006 3:06 pm

Did you check if the Unique Constraint is having all the 5 keys included on the database side. How is the constraint defined on the table. If it has all the 5 keys then , yes it would be an issue using only 3 keys while loading as it may have duplicates on these 3 keys though not on 5 keys.

mansoor_nb · Post by **mansoor_nb** » Mon Aug 21, 2006 3:18 pm

I have checked from DB side also.All the 5 keys have been included for the Unique constraint.But this is running fine in the QA environment. I am facing this issue in the Production environment.

ray.wurlod · Post by **ray.wurlod** » Mon Aug 21, 2006 9:46 pm

So what's different between the two environments?

"Nothing" is clearly not the correct answer.

mansoor_nb · Post by **mansoor_nb** » Tue Aug 22, 2006 12:23 pm

There is no difference in both environment.Also the DBA has asked me to use Oracle Merger Parallel instead of Upsert.
I have not used this Oracle Merger earlier, can anybody help me out on how to use this Oracle Merger.

Please Help me out.