Teams and developers will frequently argue about code being ‘ugly’ or ‘hard to read.’ While we can say these are subjective points or opinions – at a certain point, ugly code is ugly code.

I found this tweet to be quite funny the other day. Do you imagine your code SHOUTING every time you ask for your data?

Get it? SELECT ‘WHY ARE WE SHOUTING’ FROM DUAL;

Anyway, even though ‘the beauty of code’ might be subjective, it doesn’t mean we can’t find a set of guidelines that most of us can agree with. And with this good base to start from, you can always change things up here and there to meet your needs.

Trivadis has put together a set of these guidelines. And they’ve been through many iterations and improvements – the current version is 3.6!

Their current rules, as of today, are –

Nothing too crazy, although number 5 will be controversial!

Trying these rules in SQL Developer is EASY – thanks to Philipp!

Philipp Salvisberg put in the time to implement these rules using Oracle SQL Developer, and has put them on Github for anyone to use.

People are frequently telling me, ‘hey I hate the way SQL Developer formats code,’ but I don’t always have a great answer other than to say, hey, you can change that!

With this set of rules, I can now share this confidently knowing that at least a good portion of the Oracle developer community agrees ๐Ÿ™‚

Note: You must be on AT LEAST version 20.4 of SQL Developer or SQLcl for these to work.

The Before

Stock SQL Developer, default formatting rules.

I’m also using the ‘Hack’ font, which is included with version 20.4 now. You’ll want to use a FIXED WIDTH font for reliable display of code!

I’ve enabled display of control characters, that’s not ‘normal’

So I have UPPER keywords, lower identifiers, indent is set to 4, we have trailing commas…that much is clear from this screenshot.

Now, let’s see what happens when we apply the Trivadis coding standard rules for SQL Developer.

Trivadis PL/SQL & SQL Coding Guidelines v3.6

Downloading the zip, and grabbing the XML, and then pointing that to the ‘Import’ button on your advanced format rules page in SQL Developer –

Almost there…

There’s one more file (arbori), and this is where I imagine Philipp put in a majority of the work. Our formatting engine allows for customizations ABOVE what the preferences show in the GUI. Philipp did just that, and we can just take advantage of it.

Now we’re ready!

Ok, let’s take a FULL look at the code sample, now with all 77 lines of it in full view.

ANSI JOINs are aligned, value assignments are aligned…CASE and IFs are pretty!

I think this will work for me ๐Ÿ™‚

But wait…did you notice? One of the rules has been…broken ๐Ÿ™‚

Commas. People have opinions. It’s up to you, choose wisely!

In case @oraclenerd is looking.

“Enforcing” these rules

What I’ve found to work best –

  • set your rules
  • tell developers they have to follow said rules
  • be prepared for developers to ignore said rules
  • developers check code into source control (you DO control your source code, yes?)
  • system admins batch format all of the code in the repo
  • as soon as someone pulls their file, they’ll format it back the way they like it

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.

2 Comments

  1. This seems like a really useful bit of work. It seems to format my own code fairly well compared with the default settings. Thanks Philipp for your efforts on this. I vote to make this the default!

    One area that could still do with some improvement in Sql Developer would be to have an option to add line breaks only if the line would exceed the maximum line width.

    For example, I generally want concatenations to stay on the same line so I set the option to ‘No breaks’. The other options ‘Before/After’ generate code that is often harder to read with too many breaks where it just isn’t needed.

    Unfortunately, the ‘no breaks’ option often leads to code that exceeds the max line width. Sql Dev then wraps the line but doesn’t attempt to indent it intelligently so you get lines that align with the SELECT keyword instead of the columns for example.

    • There’s ALWAYS room for improvement. Chasing perfection, we’ll never get there. But always forward, always better.

Write A Comment