Loading LOAD_DX_PATIENT_BRIDGE Fails with ORA-14402: Updating Partition Key Column Would Cause A Partition Change (Doc ID 1900740.1)

Last updated on MARCH 14, 2016

Applies to:

Oracle Healthcare Translational Research - Version 3.0.1 and later
Information in this document applies to any platform.

Symptoms

On Oracle Health Sciences Translational Research Center (TRC) Release 3.0.1 version, ETL related issues

While running TRC Master_Execution_Plan, the following error is generated when processing LOAD_DX_PATIENT_BRIDGE:

ERROR
-----------------------
Session Name : LOAD_DX_PATIENT_BRIDGE
Scenario Name: LOAD_DX_PATIENT_BRIDGE
error message:
ODI-1217: Session LOAD_DX_PATIENT_BRIDGE (1030561) fails with return code 14402.
ODI-1226: Step INT_TMPAPPS_DX_PATIENT_H fails after 1 attempt(s).
ODI-1240: Flow INT_TMPAPPS_DX_PATIENT_H fails while performing a Integration operation. This flow loads target table W_EHA_DX_PATIENT_H.
ODI-1228: Task INT_TMPAPPS_DX_PATIENT_H (Integration) fails on the target ORACLE connection ORACLE_TMP_DATAMART.
Caused By: java.sql.SQLException: ORA-14402: updating partition key column would cause a partition change

Step name: INT_TMPAPPS_DX_PATIENT_H
Step Type Flow (Interface)
Order Number 20

ODI-1226: Step INT_TMPAPPS_DX_PATIENT_H fails after 1 attempt(s).
ODI-1240: Flow INT_TMPAPPS_DX_PATIENT_H fails while performing a Integration operation. This flow loads target table W_EHA_DX_PATIENT_H.
ODI-1228: Task INT_TMPAPPS_DX_PATIENT_H (Integration) fails on the target ORACLE connection ORACLE_TMP_DATAMART.
Caused By: java.sql.SQLException: ORA-14402: updating partition key column would cause a partition change

Task Name: Integration
  INT_TMPAPPS_DX_PATIENT_H
  Update existing rows

I-1228: Task INT_TMPAPPS_DX_PATIENT_H (Integration) fails on the target ORACLE connection ORACLE_TMP_DATAMART.
Caused By: java.sql.SQLException: ORA-14402: updating partition key column would cause a partition change

Target code that failed
/* DETECTION_STRATEGY = NOT_EXISTS */
update CDM.W_EHA_DX_PATIENT_H T
set
(
T.DIAGNOSIS_WID,
T.PATIENT_WID,
T.ANATOMICAL_SITE_WID,
T.DIAGNOSIS_STATUS_WID,
T.DIAGNOSIS_ONSET_DT,
T.DIAGNOSIS_ONSET_DT_OBF,
T.DIAGNOSIS_REPORTED_DT,
T.DIAGNOSIS_REPORTED_DT_OBF,
T.DIAGNOSIS_END_DT,
T.DIAGNOSIS_END_DT_OBF,
T.AGE_AT_FIRST_ONSET,
T.AGE_AT_FIRST_ONSET_OBF,
T.CREATED_BY_WID,
T.CHANGED_BY_WID,
T.CREATED_ON_DT,
T.CHANGED_ON_DT,
T.DELETE_FLG,
T.ETL_PROC_WID,
T.ENTERPRISE_ID,
T.ENCOUNTER_WID,
T.FACILITY_WID,
T.SERVICE_PROVIDER_WID,
T.SPECIALTY_WID,
T.ENCOUNTER_TYPE_WID,
T.ENCOUNTER_SEQUENCE_NUMBER
) =
(
select S.DIAGNOSIS_WID,
S.PATIENT_WID,
S.ANATOMICAL_SITE_WID,
S.DIAGNOSIS_STATUS_WID,
S.DIAGNOSIS_ONSET_DT,
S.DIAGNOSIS_ONSET_DT_OBF,
S.DIAGNOSIS_REPORTED_DT,
S.DIAGNOSIS_REPORTED_DT_OBF,
S.DIAGNOSIS_END_DT,
S.DIAGNOSIS_END_DT_OBF,
S.AGE_AT_FIRST_ONSET,
S.AGE_AT_FIRST_ONSET_OBF,
S.CREATED_BY_WID,
S.CHANGED_BY_WID,
S.CREATED_ON_DT,
S.CHANGED_ON_DT,
S.DELETE_FLG,
S.ETL_PROC_WID,
S.ENTERPRISE_ID,
S.ENCOUNTER_WID,
S.FACILITY_WID,
S.SERVICE_PROVIDER_WID,
S.SPECIALTY_WID,
S.ENCOUNTER_TYPE_WID,
S.ENCOUNTER_SEQUENCE_NUMBER
from CDM.I$_W_EHA_DX_PATIENT_H S
where T.DATASOURCE_NUM_ID =S.DATASOURCE_NUM_ID
and T.INTEGRATION_ID =S.INTEGRATION_ID
  )
, T.W_UPDATE_DT = SYSDATE

where (DATASOURCE_NUM_ID, INTEGRATION_ID)
in (
select DATASOURCE_NUM_ID,
INTEGRATION_ID
from CDM.I$_W_EHA_DX_PATIENT_H
where IND_UPDATE = 'U'
)


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Execute Master_Execution_Plan for incremental load.


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