Replicat Abends With Unique Constraint Error Ora - 00001 On UPDATE For Unique Key Column Which Has MULTIPLE NULL Values (Doc ID 2144291.1)

Last updated on MAY 31, 2016

Applies to:

Oracle GoldenGate - Version 11.2.1.0.14 and later
Information in this document applies to any platform.

Symptoms

Replicat abends with below error. The table in question has a unique key column and data in that column contains null value more than once.

 

2016-05-20 11:34:12 WARNING OGG-01154 SQL error 1 mapping ABC.AA_INTERFACE_LINES_ALL to XXXX.ABC_RA_INTERFACE_LINES_ALL ORA-00001: unique constraint (XXXX.ABC_RA_INTERFACE_LINES_U1) violated SQL UPDATE /*+ RESTRICT_ALL_REF_CONS */ "XXXX"."ABC_RA_INTERFACE_LINES_ALL" SET "INTERFACE_LINE_ID" = '100102412',"SET_OF_BOOKS_ID" = '194',"CONVERSION_DATE" = TO_DATE('2016-05-20 00:00:00','YYYY-MM-DD HH24:MI:SS'),"CUSTOMER_TRX_ID" = NULL,"TRX_DATE" = TO_DATE('2016-04-29 00:00:00','YYYY-MM-DD HH24:MI:SS'),"GL_DATE" = TO_DATE('2016-04-29 00:00:00','YYYY-MM-DD HH24:MI:SS'),"TRX_NUMBER" = '07124012-JT1',"SHIP_DATE_ACTUAL" = NULL,"RULE_START_DATE" = NULL,"SALES_ORDER_DATE" = NULL,"PURCHASE_ORDER_DATE" = NULL,"LAST_UPDATED_BY" = '77969',"LAST_UPDATE_DATE" = TO_DATE('2016-05-20 04:07:54','YYYY-MM-DD HH24:MI:SS'),"LAST_UPDATE_LOGIN" = '250503716',"ORG_ID" = '1244',"ORIGINAL_GL_DATE" = TO_DATE('2016-04-29 00:00:00','YYYY-MM-DD HH24:MI:SS') WHERE "INTERFACE_LINE_ID" IS NULL.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms