My Oracle Support Banner

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

Last updated on FEBRUARY 21, 2019

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 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

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
Symptoms
Changes
Cause
Solution
References


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