ODI 12c "ORA-00936: missing expression" Error and getRef() Generates "select select select... sysdate from dual from dual from dual..." in IKM

(Doc ID 2316656.1)

Last updated on OCTOBER 23, 2017

Applies to:

Oracle Data Integrator - Version 12.2.1.2.6 and later
Information in this document applies to any platform.

Symptoms

In Oracle Data Integrator (ODI) 12.2.1.2.6, an integration using IKM Oracle Incremental Update is creating incorrect SQL code in Mappings when 'sysdate' is used in query.

For example, this is the subquery from the odiRef.getFrom() output:

...
select
 STAGINGTABLE.ARGUMENTS AS ARGS,
 STAGINGTABLE.RESULTS AS RES,
 select select select select select select select select select select select select select select
 select select select select select select select select select select select select select select select
 select sysdate from dual from dual from dual from dual from dual from dual from dual from dual from
 dual from dual from dual from dual from dual from dual from dual from dual from dual from dual from
 dual from dual from dual from dual from dual from dual from dual from dual from dual from dual from
 dual from dual
AS REGISTERED_DATE,
 'I' IND_UPDATE
from STAGINGDB.STAGINGTABLE STAGINGTABLE
where (1=1)
 and (STAGINGTABLE.GROUP ='PART'
 AND STAGINGTABLE.NAME ='NAME')
) S
...

When executed, the following error message results:

ODI-1228: Task Insert flow into I$ table-IKM Oracle Incremental Update- fails on the target connection STAGINGCONN.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00936: missing expression
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:466)
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:407)
 at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1113)
 at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:546)
 at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
 at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:603)
 at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:218)
 at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:31)
 at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1000)
 at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1316)
 at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:2168)
 at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:2100)
 at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:363)
 at oracle.odi.runtime.agent.execution.sql.SQLCommand.execute(SQLCommand.java:205)
 ...

Changes

 

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