Groovy Script Execution in ODI 12.2.1.3 Causes "ORA-00932: inconsistent datatypes: expected - got CLOB" Error Due to Distinct SQL Statement

(Doc ID 2345082.1)

Last updated on JANUARY 18, 2018

Applies to:

Oracle Data Integrator - Version 12.2.1.3.0 to 12.2.1.3.0 [Release 12c]
Oracle Data Integrator Cloud Service - Version 17.1.3 and later
Information in this document applies to any platform.

Symptoms

When trying to execute a Groovy script via Oracle Data Integrator (ODI) 12.2.1.3, the following error stack is encountered:

ODI-10182: Uncategorized exception during repository access.
ORA-00932: inconsistent datatypes: expected - got CLOB
(Subtract 18 from the error line number to account for the standard imports)
oracle.odi.core.repository.UncategorizedRepositoryAccessException: ODI-10182: Uncategorized exception during repository access.
ORA-00932: inconsistent datatypes: expected - got CLOB
  at oracle.odi.core.repository.RepositoryAccessExceptionTranslator.translateSpringException(RepositoryAccessExceptionTranslator.java:240)
  at oracle.odi.core.repository.RepositoryAccessExceptionTranslator.translateSQLException(RepositoryAccessExceptionTranslator.java:195)
  at oracle.odi.core.repository.RepositoryAccessExceptionTranslator.translateEclipseLinkException(RepositoryAccessExceptionTranslator.java:147)
  at oracle.odi.core.repository.RepositoryAccessExceptionTranslator.translateExceptionIfPossible(RepositoryAccessExceptionTranslator.java:113)
  at oracle.odi.core.persistence.jpa.JpaOdiEntityManager.translateDataAccessException(JpaOdiEntityManager.java:353)
  at oracle.odi.core.persistence.jpa.toplink.JpaTopLinkOdiEntityManager.readAll(JpaTopLinkOdiEntityManager.java:207)
  at oracle.odi.core.persistence.jpa.toplink.JpaTopLinkOdiEntityManager.readAll(JpaTopLinkOdiEntityManager.java:192)
  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  at java.lang.reflect.Method.invoke(Method.java:498)
  ...
Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected - got CLOB
  at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:495)
  at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:447)
  at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1055)
  at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:624)
  at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:253)
  at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:613)
  at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:227)
  at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:60)
  at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:748)
  at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:965)
  at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1157)
  at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3833)
  at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1344)
  at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3875)
  at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1166)
  at sun.reflect.GeneratedMethodAccessor70.invoke(Unknown Source)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  at java.lang.reflect.Method.invoke(Method.java:498)
  ...
Caused by: Error : 932, Position : 526, Sql = SELECT DISTINCT t1.I_TABLE, t1.IND_JRN, t1.JRN_ORDER, t1.WS_NAME, t1.WS_ENTITY_NAME, t1.TABLE_TYPE, t1.TABLE_ALIAS, t1.IND_WS, t1.IND_SHOW, t1.FIRST_DATE, t1.FIRST_USER, t1.GLOBAL_ID, t1.INT_VERSION, t1.LAST_DATE, t1.LAST_USER, t1.PARTITION_METH, t1.TABLE_NAME, t1.OLAP_TYPE, t1.RES_NAME, t1.R_COUNT, t1.I_SC_ORIG_TABLE, t1.SC_ORIG_TABLE_TAG, t1.SUB_PARTITION_METH, t1.FILE_DEC_SEP, t1.FILE_SEP_FIELD, t1.FILE_FORMAT, t1.FILE_SEP_ROW, t1.FILE_FIRST_ROW, t1.FILE_ENC_FIELD, t1.FILE_SEP_COLL, t1.FILE_ESC_CHAR, t1.HIVE_BUCKETS, t1.HIVE_LOCATION, t1.INPUT_FORMAT, t1.IND_HIVE_MANAGED, t1.IND_HIVE_NATIVE, t1.FILE_SEP_MAP, t1.FILE_NULL_VALUE, t1.OUTPUT_FORMAT, t1.HIVE_ROW_FORMAT, t1.SCHEMA_FILE, t1.SERDE, t1.SERDE_PROP, t1.STORAGE_HANDLER, t1.STORAGE_PROP, t1.TABLE_PROP, t1.I_TABLE_FORMAT_TYPE, t1.I_TXT_DESC, t1.I_SC_BASE_TABLE, t1.I_SC_TABLE, t1.I_SUB_MODEL, t1.I_MOD FROM SNP_FF_VALUEW t0, SNP_TABLE t1 WHERE ((t0.NUM_VALUE IN (:1 )) AND ((t0.I_INSTANCE = t1.I_TABLE) AND (t0.I_OBJECTS = :2 ))), OriginalSql = SELECT DISTINCT t1.I_TABLE, t1.IND_JRN, t1.JRN_ORDER, t1.WS_NAME, t1.WS_ENTITY_NAME, t1.TABLE_TYPE, t1.TABLE_ALIAS, t1.IND_WS, t1.IND_SHOW, t1.FIRST_DATE, t1.FIRST_USER, t1.GLOBAL_ID, t1.INT_VERSION, t1.LAST_DATE, t1.LAST_USER, t1.PARTITION_METH, t1.TABLE_NAME, t1.OLAP_TYPE, t1.RES_NAME, t1.R_COUNT, t1.I_SC_ORIG_TABLE, t1.SC_ORIG_TABLE_TAG, t1.SUB_PARTITION_METH, t1.FILE_DEC_SEP, t1.FILE_SEP_FIELD, t1.FILE_FORMAT, t1.FILE_SEP_ROW, t1.FILE_FIRST_ROW, t1.FILE_ENC_FIELD, t1.FILE_SEP_COLL, t1.FILE_ESC_CHAR, t1.HIVE_BUCKETS, t1.HIVE_LOCATION, t1.INPUT_FORMAT, t1.IND_HIVE_MANAGED, t1.IND_HIVE_NATIVE, t1.FILE_SEP_MAP, t1.FILE_NULL_VALUE, t1.OUTPUT_FORMAT, t1.HIVE_ROW_FORMAT, t1.SCHEMA_FILE, t1.SERDE, t1.SERDE_PROP, t1.STORAGE_HANDLER, t1.STORAGE_PROP, t1.TABLE_PROP, t1.I_TABLE_FORMAT_TYPE, t1.I_TXT_DESC, t1.I_SC_BASE_TABLE, t1.I_SC_TABLE, t1.I_SUB_MODEL, t1.I_MOD FROM SNP_FF_VALUEW t0, SNP_TABLE t1 WHERE ((t0.NUM_VALUE IN (?)) AND ((t0.I_INSTANCE = t1.I_TABLE) AND (t0.I_OBJECTS = ?))), Error Msg = ORA-00932: inconsistent datatypes: expected - got CLOB
  at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:499)
... 74 more
Script exited.

Changes

 An upgrade has occurred from a previous version of ODI to ODI 12.2.1.3.

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