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 MARCH 08, 2017

Applies to:

Oracle Health Sciences Omics Data Bank - Version 3.0.2 and later
Information in this document applies to any platform.

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

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