Ugly code, we all write it.

I don’t mean bad code either, just hard to read code. Developers who haven’t figured out how to use the TAB and ENTER keys – what’s up with that? — and why is Jeff talking to himself in his Seinfeld voice again?

So one of the features in SQL Developer is the Formatter. With a simple right-click (or a Ctrl+F7), you can take your code from ugly to beautiful, mostly.

But I Hate How I Lose My New Lines!

That was something I heard several times over while taking your feedback on our formatter.

You put in a bunch of newlines to make your code more readable, you go to format, and you lose all that work.

No good.

Pre-Formatting

My code before I format.
My code before I format.

Post-Formatting in 3.2

So I’m going to format my code now…voila! What you see below is my code in the File History viewer. This is why people were getting upset, I’m guessing.

Side by Side, Before and After Format using v3.2 Formatter
Side by Side, Before and After Format using v3.2 Formatter

Here’s the control you have over new lines, adding or removing, in version 3.2:

Do you want more newlines or less newlines?
Do you want more newlines or less newlines?

So either you can have lines added here and there, or you can allow us to collapse any extra new lines.

What you asked for, is an option that says, LEAVE MY BLANK LINES ALONE!

The Options for Version 4.0

Preserve Existing!
Preserve Existing!

So now you have 3 options:

  1. Minimize – remove blank lines as possible
  2. Add Extra – throw new lines in for readability
  3. Preserve Existing – no touch existing blank lines!

So with ‘Preserve Existing’ selected, when I do a format in 4.0, here’s what I’m left with. Again, showing the File History compare widget to demonstrate the before and after.

Woohoo!
Woohoo!

We’ve implemented a few formatter tweaks in this release. Be sure to check out our release notes once the product if officially launched to get more info.

Our Formatter developer (he does MUCH more than this, but he gets to handle all of the formatting fun), Turloch, has started a blog post going into more detail on documenting each of the formatter preferences. I believe it’s a work in progress, but you can bet I’ll be relying on it as a reference going forward.

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.

17 Comments

  1. Marko Jaric Reply

    Two steps forward, one step back. 🙂

    SQL Developer version Version 18.3. wipes out your new lines when formatting, and there is no option in the settings to fix the.

    • 18.3 is 15 months old, tons of formatter improvements, and there’s an option to preserve blank lines

    • Yes, 20.4 no where I can find the ability to preserve lines which is so annoying!!!

  2. Is there any way I can force SQL developer formatter to ignore the formatting for some lines. So that I dont have to rework again.

    Say for eg some escape character

  3. SQLDeveloper formatter is really really bad.

    E.g,. Doesn’t know how to format a merge, doesn’t know if a minus sign is a unary operator or is a binary one.

    Burn it to the ground and reimplement, please.

  4. ARON, Todd E Reply

    Your advice is welcome. People soooooo enjoy giving me advice. Its good to have some that’s useful.

    We’ve decided to go w/ a version of your suggestion. Code reviews will be had, and offenders will be judged harshly, berated, shunned for ever-increasing periods, buried in sand to their necks and fed lima beans.

    Thanks,
    Todd “Anything But Lima Beans” A

  5. Is there a file that has the formatting definitions?

    We’d like everyone to be on the same page w/formatting; if the project leader can distribute his tweaks to the formatting, we’ll all be in synch.

    I took a quick look thru some of the sql*developer directories and nothing stuck out to me.

    Thanks!

    • There CAN be – use the Import… Export… buttons in the SQL Formatter page in the preferences.

      However, if I could interject a bit of unsolicited advice: don’t do that. Let developer format the code the way it works for them. Then, when you check the code into source control, have a command-line run of SQL Developer format those files as desired.

      You’ll never find a set of global formatting rules that everyone likes – hence you’ll always have ‘offenders’ who refuse to use your template. So just stored it in the database/repository the way you want it.

      Good luck either way!

  6. is there a way ro prevent linebreaks in function/procedure declarations like this:
    FUNCTION testfunction(
    p_column12 IN VARCHAR2)
    RETURN VARCHAR2
    IS

    Regards
    Peter

    • There is NOT. We have an option for linebreaks to leave lines of a certain length untouched that should apply to the header – and I say that logically vs how it’s actually coded – but it doesn’t. Technically this would be an enhancement request.

      Your workaround would be to highlight the entire block of code minus the header and format – it will only format the selected text.

  7. It would be good to have more options in formatter around line breaks. In 4.0, there is broad option ‘After Select, From, Where, …’ under Line Breaks. Personally, I would prefer to have line break after select or insert, and have no line break after drop or create.

    Also, I think there is a bug in how merge statement is formatted. There seems to be line break before ‘when matched’, but not before ‘when not matched ’

    merge into t using
    (select * from t
    )
    s on
    (
    t.c1 = s.c1
    )
    when matched then
    update
    set
    t.c2 = s.c2 when not matched then
    insert
    (
    c1
    , c2
    )
    values
    (
    some_seq.nextval
    , s.c2
    ) ;

  8. Please add more alignment options, (e.g., select columns list). Also please take a look at PL/SQL Developer’s code beautifier. It’s much much better than this one.

    • ‘much better’ – can you be more specific? Folks are always asking us to make the Formatter better, but they rarely share with us specific examples. You’ve asked for more alignment options, so that’s a start. Can you say more or provide an example?

      I’m not going to take another tool’s formatter and just attempt to copy that…

  9. ManishHathi Reply

    How about formatting the aDECODE? It still lives in old code and seldom in new as well. Another option is an option to convert decodes to CASE?

Reply To ManishHathi Cancel Reply