How To Solve 'ORA-01407: cannot update ("ODB"."W_EHA_SPEC_PATIENT"."SPEC_PATIENT_WID") to NULL' Exception when ODB and CDM Schema are Not Synchronized (Doc ID 2002739.1)

Last updated on JULY 13, 2023

Applies to:

Goal

CDM schema tables have been reloaded from the HDM tables by running the Master Execution plan.
When attempting to run the steps from section 2.5 of the ODB Programmer's guide (http://docs.oracle.com/cd/E35848_08/doc.3021/e35680/prerequisites.htm#CHDBIDJD), the problem is that if a specimen has been loaded and a result file also loaded, the update statement cannot be run if the specimen is now no longer in the CDM.W_EHA_SPECIMEN_PATIENT_H table.
The script returns:

In order to reprocess the CDM tables all tables containing data were truncated as HDM had also been completely refreshed.
Thus as there is no real link between CDM and ODB, the previously inserted sample is still in the w_eha_spec_patient table, but attempting to run the provided update script returns an error as the spec_wid cannot be updated to null.

What should be done for the ODB record(s) that are now no longer in the CDM tables?
 

Solution

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