COHORT: ORA-30554: function-based index <SCHEMA>.W_EHA_SPEC_PATIENT_M1 is Disabled Error with Diagnosis and Gene Variant (Doc ID 1955512.1)

Last updated on MAY 23, 2016

Applies to:

Oracle Health Sciences Cohort Explorer - Version 3.0.2 and later
Information in this document applies to any platform.

Symptoms

Cohort 3.0.2, when querying against the genomic data, you notice the following errors:


A. Query with the inclusion of a diagnosis parameter and inclusion of a gene variant parameter:

ERROR #1:
=========
oracle.jbo.SQLStmtException: SQL error during statement preparation. Statement: SELECT *
FROM
(SELECT IQ.*,
rownum ERN
FROM
(SELECT rownum row_id,
PATIENT_ID PS_WID,
PATIENT_IDENTIFIER Identifier,
tot_count
FROM
(SELECT PATIENT_ID,
PATIENT_IDENTIFIER,
COUNT(DISTINCT patient_id) over() tot_count
FROM
(WITH
/********************BEGIN named inclusion subqueries*********************************/
S20
/* SORT METHOD: dynamic */
AS
(
SELECT
/*+ materialize */
DISTINCT RPV42.ROW_WID AS PATIENT_WID, RPV42.PATIENT_IDENTIFIER AS PATIENT_IDENTIFIER
FROM
W_EHA_DIAGNOSIS_D DXD40,
W_EHA_DX_PATIENT_H_V DXPH41,
W_EHA_RESEARCH_PATIENT_V RPV42
WHERE
DXD40.ROW_WID = DXPH41.DIAGNOSIS_WID
AND DXPH41.PATIENT_WID = RPV42.ROW_WID
AND DXPH41.DELETE_FLG = 'N'
AND RPV42.DELETE_FLG = 'N'
AND (
DXD40.DIAGNOSIS_LVL14_CODE IN (:qe_1,:qe_2,:qe_3,:qe_4,:qe_5,:qe_6,:qe_7) OR
DXD40.DIAGNOSIS_LVL13_CODE IN (:qe_1,:qe_2,:qe_3,:qe_4,:qe_5,:qe_6,:qe_7) OR
DXD40.DIAGNOSIS_LVL12_CODE IN (:qe_1,:qe_2,:qe_3,:qe_4,:qe_5,:qe_6,:qe_7) OR
DXD40.DIAGNOSIS_LVL11_CODE IN (:qe_1,:qe_2,:qe_3,:qe_4,:qe_5,:qe_6,:qe_7) OR
DXD40.DIAGNOSIS_LVL10_CODE IN (:qe_1,:qe_2,:qe_3,:qe_4,:qe_5,:qe_6,:qe_7) OR
DXD40.DIAGNOSIS_LVL9_CODE IN (:qe_1,:qe_2,:qe_3,:qe_4,:qe_5,:qe_6,:qe_7) OR
DXD40.DIAGNOSIS_LVL8_CODE IN (:qe_1,:qe_2,:qe_3,:qe_4,:qe_5,:qe_6,:qe_7) OR
DXD40.DIAGNOSIS_LVL7_CODE IN (:qe_1,:qe_2,:qe_3,:qe_4,:qe_5,:qe_6,:qe_7) OR
DXD40.DIAGNOSIS_LVL6_CODE IN (:qe_1,:qe_2,:qe_3,:qe_4,:qe_5,:qe_6,:qe_7) OR
DXD40.DIAGNOSIS_LVL5_CODE IN (:qe_1,:qe_2,:qe_3,:qe_4,:qe_5,:qe_6,:qe_7) OR
DXD40.DIAGNOSIS_LVL4_CODE IN (:qe_1,:qe_2,:qe_3,:qe_4,:qe_5,:qe_6,:qe_7) OR
DXD40.DIAGNOSIS_LVL3_CODE IN (:qe_1,:qe_2,:qe_3,:qe_4,:qe_5,:qe_6,:qe_7) OR
DXD40.DIAGNOSIS_LVL2_CODE IN (:qe_1,:qe_2,:qe_3,:qe_4,:qe_5,:qe_6,:qe_7) OR
DXD40.DIAGNOSIS_LVL1_CODE IN (:qe_1,:qe_2,:qe_3,:qe_4,:qe_5,:qe_6,:qe_7) OR
DXD40.DIAGNOSIS_CODE IN (:qe_1,:qe_2,:qe_3,:qe_4,:qe_5,:qe_6,:qe_7)

)
)
,
S19
/* SORT METHOD: dynamic */
AS
(
SELECT
/*+ no_merge materialize index (SP W_EHA_SPEC_PATIENT_M1) */
DISTINCT odb_func_idx_util.patient_from_spec(SP.SPEC_PATIENT_WID) AS PATIENT_WID
FROM
(
select main1.SPECIMEN_WID
from 
(select rsq.SPECIMEN_WID
from
(
select *
from
(
select /*+ no_merge */ rs1.RESULT_SPEC_WID as SPECIMEN_WID, rs1.*
from W_EHA_RSLT_SEQUENCING rs1
where
rs1.GENE_WID in (26354,72810)
)) rsq,
(
select gseg1.gene_wid, (gseg1.start_position + ds1.start_position-1) as start_position, (gseg1.end_position + ds1.start_position-1) as end_position
from W_EHA_GENE_SEGMENT gseg1, W_EHA_DNA_SOURCE ds1
where gseg1.source_wid = ds1.row_wid
) gco1
WHERE
rsq.gene_wid = gco1.gene_wid
and rsq.start_position between gco1.start_position and gco1.end_position
) main1 
where 1=1) ODBQ39,
W_EHA_SPEC_PATIENT SP
WHERE
SP.DELETE_FLG = 'N'
AND SP.RSLT_SPECIMEN_WID = ODBQ39.SPECIMEN_WID
AND EXISTS
--PATIENT_WID IN
(
SELECT /*+ hash_sj */
NULL
FROM
S20
WHERE
S20.PATIENT_WID = odb_func_idx_util.patient_from_spec(SP.SPEC_PATIENT_WID))
)
/********************END named inclusion subqueries*********************************/
SELECT DISTINCT S20.PATIENT_WID AS PATIENT_ID, S20.PATIENT_IDENTIFIER AS PATIENT_IDENTIFIER
FROM
S20,
S19
WHERE
S19.PATIENT_WID = S20.PATIENT_WID
)
ORDER BY patient_identifier
)
) IQ
WHERE row_id )
WHERE ERN > :BindFirstRow

ADF_FACES-60097:For more information, please see the server's error log for an entry beginning with: ADF_FACES-60096:Server Exception during PPR, #8

 

 


B. Query 2 with inclusion of a single gene variant parameter:

ERROR #2:
=========
An error has occurred.  Please contact support with reference 'LOG-1491390509'

 

 

C. In the serverlog, you see the same errors, however, there is an additional error:

ERROR #3
==========
at weblogic.work.ExecuteThread.run(ExecuteThread.java:221)
Caused by: java.sql.SQLException: ORA-30554: function-based index <Schema>.W_EHA_SPEC_PATIENT_M1 is disabled

 

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