Rollback Issue: How to fix ORACLE execute error: ORA-00001 in SAS EG

[meatballs12345]

Hi all!

Currently running a specific job and it appends update on an Oracle table. Here's the part of code where

the error occurs:

%let etls_lastTable = &work.mapped;

/*---- Update master table ----*/
%put %str(NOTE: Updating table ...);

data XXXDWH.XX_XX_XX;
modify XXXDWH.XX_XX_XX
&etls_lastTable
updatemode = nomissingcheck ;
by FI_SRC_CD;
/* if the record does not exist in the master, then add it */
if %sysrc(_DSENMR) eq _iorc_ then
output;
/* if the record exists in the master, then replace it */
else if %sysrc(_SOK) eq _iorc_ then
replace;
_iorc_ = 0;
_error_ = 0;
run;

And here's the error:

NOTE: Updating table ...

ERROR: ORACLE execute error: ORA-00001: unique constraint (DWHXXX.XX_XX_PK) violated.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 1 observations read from the data set XXXDWH.XX_XX_XX.
NOTE: The data set XXXDWH.XX_XX_XX has been updated. There were 3992 observations rewritten, 27 observations added and 0
observations deleted.
NOTE: There were 1 rejected updates, 0 rejected adds, and 0 rejected deletes.
ERROR: ROLLBACK issued due to errors for data set XXXDWH.XX_XX_XX.DATA.

The table to be appended came from merged source tables. Now, I'm not so familiar with this kind of issues but as per Googling, it says that it has something to do with the unique keys and so I tried checking if there are any duplicates on the table to be appended on the Oracle table and found nothing. Any idea on how can I resolve this?

Really thankful for this community.

[SASKiwi · Accepted solution]

You need to find out what that unique constraint is. It could be that you are trying to to add a key value that must also be added to another Oracle table as well. If you don't know how to get the definition of an Oracle constraint, ask one of your Oracle DBAs.

You could also modify your code to separately collect rejected rows so you have a better idea of what the offending key values are. In your case only one row is rejected. 

[meatballs12345]

Found it by recreating the target Oracle table so the constraint will be removed (good as temporarily dropping the constraint) then I checked the merged version for the duplicates. Thank you @SASKiwi 😄