My Oracle Support Banner

Post Upgrade from ODI 11g to ODI 12c - Incorrect SQL Code is Generated for Filters that Implement Hard Coded Outer Joins (+) (Doc ID 2623335.1)

Last updated on JUNE 25, 2023

Applies to:

Oracle Data Integrator - Version 12.1.2.0.0 to 12.2.1.3.0 [Release 12c]
Information in this document applies to any platform.

Symptoms

After having upgraded Oracle Data Integrator (ODI) from 11g to 12c, the SQL code generated loses the (+) outer join symbol in Mappings Filters.

For example, if an ODI 11g Integration Interface / 12c Mapping defines:

.. in ODI 11g, the SQL code generated uses TABLE_B filter first, then outer join with TABLE_A, and TABLE_C outer join TABLE_A:

insert into <I$ TABLE NAME>
(
  ...
)
select
  TABLE_A.COLUMN1,
  TABLE_A.COLUMN2,
  TABLE_C.COLUMN3
  ...
from <TABLE A> TABLE_A, <TABLE B> TABLE_B, <TABLE C> TABLE_C
where
  (1=1)
  and (TABLE_A.COLUMN1 = TABLE_B.COLUMN1 and TABLE_A.COLUMN2 = TABLE_B.COLUMN2(+))
  and (TABLE_A.COLUMN3 = TABLE_C.COLUMN3(+))
  and (TABLE_B.COLUMN4(+) = <A VALUE>

... while in ODI 12c, the code generated has two issues:

insert into <I$ TABLE NAME>
(
  ...
)
select
  ...
from (
  select
    INLINE_VIEW.COLUMN1,
    INLINE_VIEW.COLUMN2,
    TABLE_C.COLUMN3
  from
  (
    select
      TABLE_A.COLUMN1,
      TABLE_B.COLUMN2,
      TABLE_A.COLUMN3,
      TABLE_B.COLUMN4
    from <TABLE A> TABLE_A, <TABLE B> TABLE_B
    where
      ( TABLE_A.COLUMN1 = TABLE_B.COLUMN1(+) and TABLE_A.COLUMN2 = TABLE_B.COLUMN2(+)
       )
    ) INLINE_VIEW, TABLE_C TABLE_C
where
  (INLINE_VIEW.COLUMN3 = TABLE_C.COLUMN3(+)
  )and (INLINE_VIEW.COLUMN4 = <A VALUE>
  )
) DIST_COMP
where
  (1=1)

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