Retour
-
Titre
ORA-00001: unique constraint (STAT.MERC_OA_EVENT_PK1) violated while Update Product -
Description
We are upgrading our environment from 5.8 to 6.2 STAT version. When doing the 'Update Product', an error is raised. It's a DB clone from PROD. This is supposed to be a new install for R12.2 EBS.
#### INSERT INTO MERC_OA_EVENT error, ORA-00001: unique constraint (STAT.MERC_OA_EVENT_PK1) violated
The error is captured by a Stat DB trace enabled.
-
Cause
Most likely due to the cloned database did not bring over the correct sequence values and when the procedure GET_NEXT_ID gets the next value from the MERC_OA_EVENT_SEQ1 Sequence, that sequence value is less than the highest EVENT_ID in the MERC_OA_EVENT table -
Résolution
SOLUTION:Update the MERC_OA_EVENT_SEQ1 Sequence to a value greater than the highest EVENT_ID value in MERC_OA_EVENT tableFOR EXAMPLE:In the below 2 queries the NEXTVAL should be greater than the EVENT_IDSELECT MAX(EVENT_ID) FROM STAT.MERC_OA_EVENT;SELECT MERC_OA_EVENT_SEQ1 .NEXTVAL FROM DUAL;Then fill in the below [New_Sequence_Value] with a value greater than MAX(EVENT_ID) from the above queryDROP SEQUENCE STAT.MERC_OA_EVENT_SEQ1;CREATE SEQUENCE STAT.MERC_OA_EVENT_SEQ1 START WITH [New_Sequence_Value] INCREMENT BY 1;IMPORTANT WARNING ***If this Sequence did not get synced up with the underlying table, then most likely all the other Sequence value are also off