ODI Reusable Mapping Error "ORA-00904:<SOURCE_DATASTORE>.ROWID:invalid identifier" (Doc ID 2589425.1)

Last updated on SEPTEMBER 24, 2019

Applies to:

Symptoms

After creating an Oracle Data Integrator (ODI) mapping using a Reusable Mapping with ROWID, the generated contains an incorrect reference to <SOURCE_TABLE>.ROWID instead. When it is executed, the following error occurs:

ODI-1228: Task Insert new rows-IKM Oracle Insert-Load <TARGET_DATASTORE> fails on the target connection <DATABASE_CONNECTION>.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00904: "<SOURCE_DATASTORE>"."ROWID": invalid identifier
  at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:495)
  ...

The generated code is similar to the following:

INSERT
  /*+ APPEND PARALLEL */
  INTO <DATABASE_CONNECTION>.<TARGET_TABLE>
  (
    COL1 ,
    COL2 ,
    ADDED_ROWID
  )
SELECT
  <SOURCE_DATASTORE>_<REUSABLE_MAP>_1.COL1 ,
  <SOURCE_DATASTORE>_<REUSABLE_MAP>_1.COL2 ,
  <SOURCE_TABLE>.ROWID
FROM
  <DATABASE_CONNECTION>.<SOURCE_TABLE> <SOURCE_DATASTORE>_<REUSABLE_MAP>_1

Cause

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