My Oracle Support Banner

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

To view full details, 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 a vibrant support community of peers and Oracle experts.