Using ANSI Joins with ADF business components

If you select the Model project and bring up project properties, there is a project setting (under business components/View Objects)  you can change to make entity based view objects use ANSI style joins instead of the Oracle style.  

i.e.

instead of having this in the where clause

AND (XrefSalesTransactionAccount.ACCOUNT_ID = Account.ACCOUNT_ID(+))

You would have this join clause predicate

LEFT OUTER JOIN ACCOUNT Account ON (XrefSalesTransactionAccount.ACCOUNT_ID = Account.ACCOUNT_ID)

I see three advantages to using ANSI style joins.  

•  They are clearer and easier to read 
• They give separation between the table joins and the where clause criteria so that when you add extra where clause criteria it doesn’t get mixed in with the table join criteria, so is easier to change in the future.  i.e.  everything in the query clauses input box will be custom sql added by the developer, not mixed in with the join criteria put there by jdeveloper. 
•  If you add new entities to the view in the future, jdeveloper won’t have to update the where clause so it will be easier to keep the original where clause intact when you get the message “The query has a   custom where clause.  Would you like to overwrite the customized where clause by the default where clause?  If not you may need to manually add a join clause for the new entity in the where clause”.  There seems to be a bug in jdeveloper in that if you select “No”  it still deletes the custom where clause, except now you can save off the whole thing and paste it back in without having to separate the custom part from the generated part.

One problem you may discover after changing this setting is that if you open any pre-existing view object and edit anything (including hint text),  it will convert it to use ANSI joins, however it will leave the original "where" clause predicates in place containing the old join sequence.   The query may still work unless it contained outer joins but there will be redundant criteria in the "where" clause that should be deleted to make it clean.

Also if you have a view links that was customized to contain the Oracle outer join (+) syntax.  This type of view link won't work if the view being linked uses ANSI joins because the link is created using bind variables in the where clause and you can't combine Oracle and ANSI joins.  You could potentially use this type of link to a ANSI style view if the view uses expert mode and wraps the entire query in a extra select clause so that the where clause created by the link is added outside of the inner query but I don't recommend using Expert mode.

I  discovered one bug when using ANSI joins that occurs when you attempt to create a view object containing a Many-to-Many association between two entities.   A Many-to-Many association is when you have a intersecting xref table that joins the two tables together.   ADF will generate invalid SQL when you attempt to use this association in your view object and you will get  
(java.sql.SQLSyntaxErrorException) ORA-00933: SQL command not properly ended.
The work-around is to include the intersecting entity in your view object and use a "one to many" association instead of "many to many".  I have reported this bug to Oracle and verified it exists in 11.1.1.4 and 11.1.1.6
update: Oracle has confirmed and opened Bug 16781328 : ERROR ORA-00933 USING ANSI JOIN IN BUSINESS COMPONENTS.


I don't like the fact that the ANSI join style is a project setting and not a view object setting.   I think the determination of style should be on a case by case basis to mitigate the problems described above. It's easy enough to switch the setting on and off but I shouldn't have to.

I would be interested to hear from others who have used ANSI joins in ADF so please reply to this posting if you've had any experience or problems using them.