DB Upgrade Failed With ORA-02291 FK1_CODE_LIST_DETAIL_DISCRETE (Doc ID 2768921.1)

Last updated on JULY 13, 2023

Applies to:

Symptoms

Database upgrade failed when running Argus database 8.2.2 or 8.2.3 upgrade (dbinstaller.bat) from AS 8.2.1.1.

The following error is output in the dbinstaller_xxx.log.

***ERROR: Unexpected error running Liquibase: ORA-02291: integrity constraint (ARGUS_APP.FK1_CODE_LIST_DETAIL_DISCRETE) violated - parent key not found
ORA-06512: at line 28
ORA-06512: at line 28
[Failed SQL: (2291) DECLARE

CURSOR cur_enterprise
IS
SELECT enterprise_id FROM cfg_enterprise;
BEGIN
BEGIN pkg_rls.set_context('system',0,'ARGUS_SAFETY');end;
FOR c_ent IN cur_enterprise
LOOP
pkg_rls.set_context('system',c_ent.enterprise_id,'ARGUS_SAFETY');
FOR c2 IN (SELECT CODE_LIST_ID,CODE FROM (SELECT DISTINCT CODE_LIST_ID,CODE,
CASE WHEN REGEXP_LIKE(CODE, '^[[:digit:]]+$') THEN TRIM(CODE) ELSE '0' END NUM_CODE
FROM ARGUS_APP.CODE_LIST_CODE_ATTRIBUTES CLDA
WHERE
CODE_LIST_ID IN
('REPORTING_DESTINATION_TYPE', 'CASE_CLASSIFICATION',
'REPORTER_TYPE', 'CAUSALITY_CATEGORY')
AND DELETED IS NULL
AND NOT EXISTS
(SELECT 1
FROM CODE_LIST_DETAIL_DISCRETE
WHERE CODE_LIST_ID = CLDA.CODE_LIST_ID
AND DECODE_CONTEXT = 'MFDS'
AND CODE = CLDA.CODE
AND DELETED IS NULL)
)
WHERE TO_NUMBER(NUM_CODE) >100000)
LOOP
INSERT INTO ARGUS_APP.CODE_LIST_DETAIL_DISCRETE
(
CODE_LIST_ID,
DECODE_CONTEXT,
CODE,
DISPLAY_VALUE,
PREFERRED,
SORT,
LAST_UPDATE_TIME
) values
(c2.CODE_LIST_ID ,
'MFDS',
c2.code,
NULL,
0,
NULL,
SYSDATE );
END LOOP;
END LOOP;
BEGIN pkg_rls.set_context('system',0,'ARGUS_SAFETY');end;
END;]

Note:
Reviewed the data in the CODE_LIST_DETAIL_DISCRETE and CODE_LIST_CODE_ATTRIBUTES tables and confirmed that no missing record exists in CODE_LIST_CODE_ATTRIBUTES table based on CODE and CODE_LIST_ID. (Reference : Doc ID 2382344.1)

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.