ODI 12c Mapping Error "ORA-00904: <TABLE ALIAS>.<COLUMN ALIAS>: invalid identifier" (Doc ID 2415636.1)

Last updated on AUGUST 17, 2020

Applies to:

Symptoms

After upgrading an Oracle Data Integrator (ODI) 11g Integration Interface to 12c mapping, 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 <DATABASE CONNECTION>.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00904: "<TABLE ALIAS>"."<COLUMN ALIAS>": invalid identifier
  at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:495)
  at ...

The generated code is similar to the following:

insert into <SCHEMA>.<TABLE>
(
  <COLUMN>,
  ...
)
select
  <TABLE ALIAS>.<COLUMN> AS <COLUMN ALIAS>,
  <TABLE2 ALIAS>.<COLUMN2> AS <COLUMN2 ALIAS>,
  <TABLE2 ALIAS>.<COLUMN3> AS <COLUMN3 ALIAS>,
  ...
from (
  SELECT
  DISTINCT
  <SOURCE TABLE ALIAS>.<COLUMN> AS <COLUMN ALIAS>,
  ...
)
FROM
<SCHEMA>.C$_<SOURCE TABLE> <SOURCE TABLE ALIAS>
) <TABLE2 ALIAS>
where (1=1)

Note the <TABLE ALIAS> table alias is different than the one used for the sub-query, which in the example is <SOME TABLE ALIAS>. It is expected to 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

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