My Oracle Support Banner

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:

ORA-01407: cannot update ("ODB"."W_EHA_SPEC_PATIENT"."SPEC_PATIENT_WID") to NULL


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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Goal
Solution
References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.