Eclipse Community Forums: EclipseLink » Exception [EclipseLink-4002] - ORA-25156: old style outer join (+) cannot be used with ANSI joins

Home » Eclipse Projects » EclipseLink » Exception [EclipseLink-4002] - ORA-25156: old style outer join (+) cannot be used with ANSI joins(Exception [EclipseLink-4002] - ORA-25156: old style outer join (+) cannot be used with ANSI joins)

Show: Today's Messages :: Show Polls :: Message Navigator

Exception [EclipseLink-4002] - ORA-25156: old style outer join (+) cannot be used with ANSI joins [message #1694196]

Fri, 01 May 2015 20:48

Amita Mehta

Messages: 25
Registered: May 2013

Junior Member

We are migrating from TopLink 10.1.3.5 to EclipseLink.

We have an expression query that creates an outer join
optionExp =
builder.anyOfAllowingNone("oList").getField(
"OPTTYP_TYPE_C(+)");
optionExp =
optionExp.notEqual("ABC").and(optionExp.notEqual("XYZ"));

It generates the following SQL clause:
))) AND ((t2.OPTTYP_TYPE_C(+) <> 'ABC') AND (t2.OPTTYP_TYPE_C(+) <> 'XYZ')))

This used to work with TopLink but throws an error with EclipseLink.
Is there any setting that can be changed so that we do not have to change the application code?
Thanks for the help.

Exception:
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.4.2.v20130514-5956486): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: ORA-25156: old style outer join (+) cannot be used with ANSI joins

Report message to a moderator

Re: Exception [EclipseLink-4002] - ORA-25156: old style outer join (+) cannot be used with ANSI joi [message #1694364 is a reply to message #1694196]

Mon, 04 May 2015 19:38

Chris Delahunt

Messages: 1380
Registered: July 2009

Senior Member

What was the SQL it used to generate in TopLink?

Report message to a moderator

Re: Exception [EclipseLink-4002] - ORA-25156: old style outer join (+) cannot be used with ANSI joi [message #1694482 is a reply to message #1694364]

Tue, 05 May 2015 14:43

Amita Mehta

Messages: 25
Registered: May 2013

Junior Member

EclipseLink generates a left outer join query -
SELECT DISTINCT t0.col1, to.col2.......................
FROM TAB_PCV t0
LEFT OUTER JOIN TAB_PCVNT t2
ON ((t2.PCV_C = t0.PCV_C)
AND ((t2.COUNTRY = t0.COUNTRY)
AND (t2.MYEAR_C = t0.MYEAR_C))),
TAB_ELGPCV t1
WHERE (((((((t0.COUNTRY IN ('AAT'))
...
AND ((t2.OPTTYP_TYPE_C(+) <> 'ABC')
AND (t2.OPTTYP_TYPE_C(+) <> 'XYZ')))
...))

TopLink does not generate a left outer join and adds some extra related tables too-
SELECT DISTINCT t1.col1, t1.col2.......................
FROM MGLSA25_PCVST t6,
TAB_PCVNT t5,
TAB_ELGPCV t4,
TAB_COUNTRY t3,
TAB_BAND t2,
TAB_PCV t1,
TAB_SOURCE t0
WHERE ((((((((t0.COUNTRY IN ('AAT'))
...
AND ((t2.OPTTYP_TYPE_C(+) <> 'ABC')
AND (t2.OPTTYP_TYPE_C(+) <> 'XYZ')))
...))

Report message to a moderator

Re: Exception [EclipseLink-4002] - ORA-25156: old style outer join (+) cannot be used with ANSI joi [message #1694894 is a reply to message #1694482]

Fri, 08 May 2015 19:25

Chris Delahunt

Messages: 1380
Registered: July 2009

Senior Member

The OPTTYP_TYPE_C(+) clauses seem to be exactly the same, though shouldn't it be T5 in the TopLink SQL? Since you are using an builder.anyOfAllowingNone("oList") expression, TopLink should be creating an outer join for the oList relationship. Isn't there some clauses in the TopLink SQL for the TAB_PCV to TAB_PCVNT join? What database are you using, and what is the exception you get with this query?

[Updated on: Fri, 08 May 2015 19:26]

Report message to a moderator