ORA-01719: outer join operator (+) not allowed in operand of OR or IN after upgrading to ODI 12c

Hello All,

I'm writing here after a long gap. Hope you all are doing great, and will continue to read my posts!!

Recently, after upgrading one of our customers from Oracle Data Integrator (ODI) 11g to Oracle Data Integrator (ODI) 12c, we faced an issue with a few of the upgraded mappings. When we run the mapping/package/scenario, they fail with an error "ORA-01719: outer join operator (+) not allowed in operand of OR or IN".

When we dug into this further, we found that this issue was caused by the usage of an older type of JOIN syntax, also called Non ANSI join syntax (With a (+) symbol) in Oracle. For example, we have 3 tables A, B, and C with the following join conditions in ODI 11g.

A = B

A= C

B=C(+)

On top of these, we have a filter condition on the table A like A>1.

In ODI 11g, the filter used to work without issues. However, post upgrade, ODI 12c adds a (+) symbol in the filter, like A(+) > 1, and we are thrown the above error when we run the ODI mapping/package/scenario.

To resolve this issue, we need to edit the mapping that has this issue in ODI 12c designer, and then change all the joins to "Generate ANSI Syntax". You get this option, when you select the Join component in the mapping shown as below.

Once this is done for all the Join components in the mapping, you could regenerate the scenario and run to see that the issue is fixed.

If you have any questions on the solution, please post in the comments section. Happy to help.

Note: Oracle recommends that we switch to the ANSI syntax in place of the older Non ANSI syntax, if the Oracle Database version is above 9i.

Cheers,

Siva