The ability to switch between ANSI and Oracle style joins was introduced in SQL Developer 19.2.
How to use
The example will show how a query using the Oracle outer join style can be changed to use the ANSI style.
Highlight the SQL statement
Right click to bring up the context menu and select Refactoring
Select Toggle Ansi/Oracle joins
The query is now changed to use ANSI join style.
Caution. Toggling can be hazardous to your code.
It’s still early days for this feature and people have reported problems. A minor inconvenience is the removal of statement semi-colon. Comments left on Jeff’s post mention other issues so as with any tool know it’s limitations and check any changes it makes to ensure the query is still doing what you expect it to.
Categories: Oracle SQL Developer
oraclefrontovik
Developer
Great post! I tried refactoring a query with Oracle JOINS to ANSI JOINS. Seems the tool got the translation completely wrong. I noticed when the query took forever to run. Somehow it mixed up INNER/LEFT/RIGHT.
It’s so bad that when I toggled it back to Oracle JOINS, it was nowhere nearly equivalent to the original.
Ah well, like you say, it’s still new. But coming from Oracle itself, it’s quite disappointing.
Thanks for the kind comments about the post which are much appreciated. Totally agree, since I wrote that post I have seen some strange results when using this toggle. What version of SQL Developer are you using?
I’m using Version 20.4.0.379