ODI 12c Mapping AGGREGATE Component Does Not Properly Handle Reserved Words Like EXCLUDE (Doc ID 2803326.1)

Last updated on JUNE 07, 2023

Applies to:

Symptoms

A table used as target in the Oracle Data integrator (ODI) 12c Mapping has a column called EXCLUDE.

Since this is a reserved word, and ODI 12c no longer uses C1_, C2_, C3_... column prefixes, EXCLUDE has been added to the Oracle Data Integrator (ODI) 12c Language Elements > SQL > Keywords, as explained in Document 2617570.1.

With the above setting, double-quotes are added around the EXCLUDE column name in the Datastore definition, and the Mapping target attribute, which both are correct / intended behavior.

However, the above column is used in an ODI 12c AGGREGATE component, and the SQL code generated by ODI during the "Insert new rows" IKM task is incorrect.

This is working fine in ODI 11g.

The issue can be reproduced at will with the following steps:

1. In ODI 11g, create an Integration Interface where EXCLUDE is a column used in the target Datastore, and is defined as shown in the screen copy below:
   
   
     
2. Execute the 11g Integration Interface, and observe the correct code executed on the "Insert new rows" IKM task:
   

     insert into <TARGET TABLE NAME>
     (
       <COLUMN 1>,
       ...
       <COLUMN N>,
       "EXCLUDE",                                     --> This is correct
       <COLUMN M>,
       ...
     )
     select DISTINCT
       <COLUMN 1>,
       ...
       <COLUMN N>,
       <EXPRESSION FOR THE EXCLUDE COLUMN>,           --> This is correct
       <COLUMN M>,
       ...
     from
       ...
     group By
       ...
       <EXPRESSION FOR THE EXCLUDE COLUMN>,
       ...
     order by ...

3. Smart export the ODI 11g Integration Interface.
     
4. In ODI 12c, add the EXCLUDE keyword to Language Elements > SQL, as explained in Document 2617570.1.
     
5. Smart import into ODI 12c the 11g smart export file.
     
6. Execute the 12c Mapping, and observe the issue:
   
     insert into <TARGET TABLE NAME>
    (
       <COLUMN 1>,
       ...
       <COLUMN N>,
       "EXCLUDE",                                    --> This is correct
       <COLUMN M>,
       ...
     )
     select
     ...
     from (
       SELECT
       DISTINCT
       INLINE_VIEW.<COLUMN 1> AS <    ...
       INLINE_VIEW.<COLUMN N> AS <    <AGGREGATE NAME>."EXCLUDE" AS "EXCLUDE"       --> This is not correct
       INLINE_VIEW.<COLUMN M> AS <    ...

Changes

Cause