"ORA-01719: outer join operator (+) not allowed in operand of OR or IN" Error When Executing a Mapping After Migration From OWB to ODI 12c

(Doc ID 2364112.1)

Last updated on FEBRUARY 26, 2018

Applies to:

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

Symptoms

In Oracle Data Integrator (ODI) 12c, a mapping migrated from Oracle Warehouse Builder (OWB) generates a SQL query for an OR filter with an outer join symbolized by the plus-sign surrounded by parenthesis: (+). This format is not used in the SQL generated in the original OWB mapping. When the mapping is executed, it results in the following error:

ODI-1228: Task Merge rows-IKM Oracle Merge-Load DETAIL_TGT_UPD_DIM_ITEM_LOC fails on the target connection DATAWAREHOUSE_CONN.
Caused By: java.sql.SQLSyntaxErrorException: ORA-01719: outer join operator (+) not allowed in operand of OR or IN

An example section of the generated mapping code:

WHERE
( (DETAIL_ONE_DIM_ITEM_LOCATION.ITEM_LOCATION_ID = DETAIL_TWO_SRC_DIM_ITEM__1.ITEM_LOCATION_ID (+) )
  AND
  ((DETAIL_TWO_SRC_DIM_ITEM__1.WH_DATE_FROM (+) IS NULL )
    OR
    (DETAIL_TWO_SRC_DIM_ITEM__1.WH_DATE_TO (+) IS NULL AND DETAIL_ONE_DIM_ITEM_LOCATION.WH_DATE_FROM >= DETAIL_TWO_SRC_DIM_ITEM__1.WH_DATE_FROM (+) )
    OR
    (DETAIL_TWO_SRC_DIM_ITEM__1.WH_DATE_TO (+) IS NOT NULL AND DETAIL_ONE_DIM_ITEM_LOCATIO.WH_DATE_FROM BETWEEN DETAIL_TWO_SRC_DIM_ITEM__1.WH_DATE_FROM (+) AND DETAIL_TWO_SRC_DIM_ITEM__1.WH_DATE_TO (+) )
  )
)

Changes

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms