SQL Developer: ANSI/Oracle Joins toggle

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.

3 thoughts on “SQL Developer: ANSI/Oracle Joins toggle

  1. 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.

    1. 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?

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.