Oracle SQL Developer is at its heart, an Interactive Development Environment (IDE.) So it comes with the territory that developers should expect to be able to write good code quickly. One of the many code ‘helpers’ that a good IDE will provide is a Formatter.

Formatters, or code beautifiers as they’re also known, take poorly formatted text and make them readable again.

This generally comes down to good management of whitespace.

Let’s look at a very quick example, Prior to Version 4.2:

Raw, Unformatted SQL

Hard to read, much?

Ctrl+F7 = Presto-changeo!

Formatted SQL

Better?

But My Formatter is Broken!

Generally speaking, when SQL Developer is doing something you don’t like, there’s an option for that. The Formatter has an entire page of it’s preferences, and you can even set different formatting styles for Oracle and…well, non-Oracle SQL statements.

There’s one particular preference that has kicked me in the pants more than once. Let’s take a look at a much simpler, smaller bit of SQL:

Small statement, still hard to read (for me)

Ctrl+F7 = Nada!?!

Threshold for small SQL

Line Breaks are inserted when you format your SQL statements. There’s one preference that can get in the way, ‘Oracle – Line Breaks – Threshold for small SQL.’

This tells SQL Developer to not make 1 line into multiple lines.

If I change that preference from 80 to something like ’15,’ then my formatted SQL statement now looks like this:

If you don’t like this, then set the options more to your liking.

Now if you’re afraid of breaking something with your formatting preferences, you can preview the code being formatted live in the preference dialog as you toggle and change the options. So you don’t have to guess so much as to what the settings actually do.

Why am I blogging this particular option? Because it’s gotten me in trouble more than once, and now I have a great reminder for the fix when it rears its ugly head again!

Updated for Version 4.2 and Higher

Many of the formatter preferences were poorly worded, confusing, or just…not useful. We changed the formatter from the ground up for version 4.2. Here’s how it looks now.

Max CHAR Line Width is the preference you are looking for now.

So let’s format some code in version 18.2

I’ve set the max length to 45, no new words will be allowed past char pos 45.
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

7 Comments

  1. Please add an option for a linebreak before or after a semi-colon.

    Thanks

  2. OK, found it… You have to open up the formatter and then click the edit button. That wasn’t clear from your post. 🙂

    • Great! I was worried we broke something in 3.2.

      Sorry for the unclear path to the options. I think I covered it in an earlier post but will be sure to cover that in formatting posts going forward.

  3. Jeff,

    Great article… But where did the threshold for small SQL go in SQL Developer 3.2? I can’t seem to find it.

    Thanks,

    Rich

  4. I wish SQL Developer would break out the “more newlines” option, because some of the choices it makes seem unnatural. I’d like to see it put 2 linebreaks at the end of a procedure in a package, but there’s no way to make that happen. I’d like to have “CREATE OR REPLACE PACKAGE packagename” all on 1 line, but seems there’s no way to do that either. I like the right-align master keywords option, but for some reason, the AND-conditions don’t get aligned with it.

    It seems like the SQL Developer formatter still has a lot of quirks. Or maybe I’m the quirky one, still doing all my development in an old version of Formatter Plus, which breaks on all new syntax…

    • The option, ‘more newlines,’ will insert blank lines in the follow circumstances:

      • before and after (2 lines) most BEGIN END or logical code blocks
      • before Create or Replace, (before function and procedure in packages)
      • two single line comments (that are not embedded in another statement)
      • after THEN before IF

      You can see a code sample updated dynamically as you toggle the ‘More’ preference on and off.

      All this being said there are a few whitespace management issues being looked at for the formatter. Preserving blank lines for example is something we need to address. Formatting improvements are something that will never be ‘finished,’ but since it is a largely subjective area, we heavily rely on our customers to focus our efforts. That includes you here 🙂

Reply To Dan T. Cancel Reply