Incorrect DML Generated for ODI Mapping with LEFT JOIN (Doc ID 2486900.1)

Last updated on JUNE 16, 2022

Applies to:

Symptoms

In ODI 12.2.1.3.0, when attempting to run a mapping that contains a reusable mapping containing a left join, the following error occurs.

ODI-1228: Task Insert new rows-IKM Oracle Insert-Load <LOADNAME> fails on the target connection <CONNECTION>.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00904: "TABLE_NAME"."COLUMN_NAME": invalid identifier 
  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.T4CStatement.doOall8(T4CStatement.java:214)
  at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:38)
  at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:891)
  at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1194)
  at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1835)
  at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1790)
  ...
Caused by: Error : 904, Position : 311, Sql = <SQL containing left join>
, Error Msg = ORA-00904: TABLE.COLUMN: invalid Identifier
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:499)
  ... 30 more

The issue can be reproduced with the following steps:

1. Create a reusable mapping that has in it one or two inner joins.
2. Create a mapping that uses the above reusable mapping and run it. It will complete successfully.
3. Change one inner join into a left one by checking the box for left join.
4. Run the mapping again and observe the error.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.