COHORT: ORA-00904: "GET_USER_WID": invalid identifier (Doc ID 1666415.1)

Last updated on DECEMBER 11, 2015

Applies to:

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

Symptoms

Users notice the following error message with Cohort ODI ETL:

[2014-04-22T12:51:56.573+02:00] [] [ERROR] [ODI-1217] [] [tid: 251] [ecid: 0000KM8ZK_X3z0nLKmWBye1JLYWf000006,0:30315] Session LOAD_DATASOURCE_TABLE (20803) fails with return code 904.[[
ODI-1226: Step INT_TMPAPPS_Datasource_Table_Full fails after 1 attempt(s).
ODI-1240: Flow INT_TMPAPPS_Datasource_Table_Full fails while performing a Integration operation. This flow loads target table W_EHA_DATASOURCE_CDM.
ODI-1228: Task INT_TMPAPPS_Datasource_Table_Full (Integration) fails on the target ORACLE connection ORACLE_TMP_DATAMART.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00904: "GET_USER_WID": invalid identifier

  at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
  at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
  at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
  at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
  at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
  at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
  at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)
  at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1115)
  at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1488)


o Users addressed the TNS errors already.
o In 3.00 the following statement was successfully deployed:
insert into CDM.I$_W_EHA_DATASOURCE_CDM
(
  ROW_WID,
  DATASOURCE_CODE,
  DATASOURCE_NAME,
  DATASOURCE_DESC,
  CREATED_BY_WID,
  CHANGED_BY_WID,
  CREATED_ON_DT,
  CHANGED_ON_DT,
  DELETE_FLG,
  W_INSERT_DT,
  W_UPDATE_DT,
  ETL_PROC_WID,
  ENTERPRISE_ID
)


select
  C1_ROW_WID,
  C2_DATASOURCE_CODE,
  C3_DATASOURCE_NAME,
  C4_DATASOURCE_DESC,
  NVL(
(SELECT ROW_WID FROM CDM.W_USER_D
WHERE INTEGRATION_ID=C8_INTEGRATION_ID
AND DATASOURCE_NUM_ID=C9_DATASOURCE_NUM_ID), '#ORACLE_HEALTHCARE_ANALYTICS.DEFAULT_WID'),
  NVL(
(SELECT ROW_WID FROM CDM.W_USER_D
WHERE INTEGRATION_ID=C10_INTEGRATION_ID
AND DATASOURCE_NUM_ID=C11_DATASOURCE_NUM_ID), '#ORACLE_HEALTHCARE_ANALYTICS.DEFAULT_WID'),
  C5_CREATED_ON_DT,
  C6_CHANGED_ON_DT,
  C7_DELETE_FLG,
  SYSDATE,
  SYSDATE,
  402802,
  C12_ENTERPRISE_ID
from CDM.C$_0W_EHA_DATASOURCE_CDM
where
  (1=1)

get_user_wid XXXXXX in 3.00 = (SELECT ROW_WID FROM CDM.W_USER_D
WHERE INTEGRATION_ID=C8_INTEGRATION_ID
AND DATASOURCE_NUM_ID=C9_DATASOURCE_NUM_ID), '#ORACLE_HEALTHCARE_ANALYTICS.DEFAULT_WID')

o In 3.01 it'works like this:

insert into CDM.I$_W_EHA_DATASOURCE_CDM
(
  ROW_WID,
  DATASOURCE_CODE,
  DATASOURCE_NAME,
  DATASOURCE_DESC,
  CREATED_BY_WID,
  CHANGED_BY_WID,
  CREATED_ON_DT,
  CHANGED_ON_DT,
  DELETE_FLG,
  W_INSERT_DT,
  W_UPDATE_DT,
  ETL_PROC_WID,
  ENTERPRISE_ID
)


select
  C1_ROW_WID,
  C2_DATASOURCE_CODE,
  C3_DATASOURCE_NAME,
  C4_DATASOURCE_DESC,
  GET_USER_WID(C8_INTEGRATION_ID,C9_DATASOURCE_NUM_ID),
  GET_USER_WID(C10_INTEGRATION_ID,C11_DATASOURCE_NUM_ID),
  C5_CREATED_ON_DT,
  C6_CHANGED_ON_DT,
  C7_DELETE_FLG,
  SYSDATE,
  SYSDATE,
  27803,
  C12_ENTERPRISE_ID
from CDM.C$_0W_EHA_DATASOURCE_CDM
where
  (1=1)

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