ODI Mapping Error 'ORA-00904: "TABLE_ALIAS"."COLUMN": invalid identifier'

(Doc ID 2415636.1)

Last updated on JULY 10, 2018

Applies to:

Oracle Data Integrator - Version 12.2.1.2.6 to 12.2.1.3.0 [Release 12c]
Information in this document applies to any platform.

Symptoms

After upgrading an Oracle Data Integrator (ODI) 11g mapping to 12c, the generated code contains an incorrect reference to TABLE_ALIAS. When it is executed, the following error occurs:

ODI-1228: Task Insert new rows-IKM SQL Control Append- fails on the target connection TARGET_ORCL_PROD.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00904: "TABLE_ALIAS"."INVENTORY_ITEM_ID": 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)
  ...

The generated code will be similar to the following:

insert into DATA_WAREHOUSE.W_TABLE
(
  INVENTORY_ITEM_ID,
  ...
)
select
  TABLE_ALIAS.INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID,
  SOME_REF.SEGMENT1 AS SEGMENT1,
  SOME_REF.ATTRIBUTE11 AS ATTRIBUTE11
  ...
from (
  SELECT
  DISTINCT
  SOURCETABLE_A.INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID ,
  ...
)
FROM
DATA_WAREHOUSE.C$_0SOURCETABLE SOURCETABLE_A
) SOME_REF
where (1=1)

Note the INVENTORY_ITEM_ID table alias is different than the one used for the subquery, which in the example is SOME_REF. It is expected it would be TABLE_ALIAS.

You may experience similar issues with incorrect TABLE_ALIAS generation, with differing results and error messages depending on the code generated. The issue remains the same.

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