Template-style "IKM Oracle Incremental Update" Generates Incorrect Inline Query With DUAL (Doc ID 2283618.1)

Last updated on JULY 21, 2017

Applies to:

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

Symptoms

Having an Oracle Data Integrator (ODI) 12c Mapping with an expression as source, and an Oracle Datastore as target.

When component-style "IKM Oracle Update" is being used, the Mapping execution succeeds, and the generated SQL code is correct.

When template-style "IKM Oracle Incremental Update" is used instead, the Mapping execution fails, and the SQL code generated on task "Insert flow into I$" is incorrect:

/* DETECTION_STRATEGY = NOT_EXISTS */
insert into IC_DW.I$_WRK_BST_STEUERUNG
...
from {#NL#}
$[QUERY.getConstantFromClauseText(false)]
where (1=1)
...

Error message:

ODI-1228: Task Insert flow into I$ table-IKM Oracle Incremental Update- fails on the target connection MYDB.
Caused By: java.sql.SQLException: Non supported SQL92 token at position: 180
  at oracle.jdbc.driver.OracleSql.handleODBC(OracleSql.java:1522)
  at oracle.jdbc.driver.OracleSql.parse(OracleSql.java:1393)
  at ...

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