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
Ctrl+F7 = Presto-changeo!
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:
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.’
If I change that preference from 80 to something like ’15,’ then my formatted SQL statement now looks like this:
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.
So let’s format some code in version 18.2