Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
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)
Exception [EclipseLink-4002] - ORA-25156: old style outer join (+) cannot be used with ANSI joins [message #1694196] Fri, 01 May 2015 20:48 Go to next message
Amita Mehta is currently offline Amita MehtaFriend
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
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 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1380
Registered: July 2009
Senior Member
What was the SQL it used to generate in TopLink?
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 Go to previous messageGo to next message
Amita Mehta is currently offline Amita MehtaFriend
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')))
...))
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 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
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

Re: Exception [EclipseLink-4002] - ORA-25156: old style outer join (+) cannot be used with ANSI joi [message #1695536 is a reply to message #1694894] Fri, 15 May 2015 19:42 Go to previous message
Amita Mehta is currently offline Amita MehtaFriend
Messages: 25
Registered: May 2013
Junior Member
We fixed it by changing the clause to use builder.anyOfAllowingNone

Thanks for the help.
Previous Topic:Basic(fetch=LAZY)
Next Topic:Is it possible to have changetracking working on detached serialized objects?
Goto Forum:
  


Current Time: Mon Mar 01 13:07:10 GMT 2021

Powered by FUDForum. Page generated in 0.02582 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top