My Oracle Support Banner

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

Last updated on AUGUST 31, 2021

Applies to:

Oracle Data Integrator - Version 12.2.1.4.200304 and later
Information in this document applies to any platform.

Symptoms

A table used as target in the 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

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.