My Oracle Support Banner

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

Last updated on JUNE 16, 2022

Applies to:

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


In ODI, 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(
  at oracle.jdbc.driver.T4CTTIoer11.processError(
  at oracle.jdbc.driver.T4C8Oall.processError(
  at oracle.jdbc.driver.T4CTTIfun.receive(
  at oracle.jdbc.driver.T4CTTIfun.doRPC(
  at oracle.jdbc.driver.T4C8Oall.doOALL(
  at oracle.jdbc.driver.T4CStatement.doOall8(
  at oracle.jdbc.driver.T4CStatement.doOall8(
  at oracle.jdbc.driver.T4CStatement.executeForRows(
  at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(
  at oracle.jdbc.driver.OracleStatement.executeInternal(
  at oracle.jdbc.driver.OracleStatement.execute(
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(
  ... 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.




To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

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