A follower reached out to me late yesterday with this request:

From meh to wow – can we do it?

And the answer is yes, we can pretty much get there.

I’ve noticed a few things by looking at his hand-formatted code on the bottom section.

He prefers:

  • everything lowercase
  • right-aligned keywords
  • no line breaks for the sub query ()’s
  • no line breaks after SELECT/FROM/WHERE keywords

So let’s go tell the formatter to follow these same rules.

Note that all other formatter preferences are left to DEFAULT values.

That’s about 5 clicks, not counting the APPLY.

Now, let’s format the code.

For those of you following along, here’s the code so you can try to replicate what we’re showing:

SELECT owner, TABLE_NAME FROM all_tab_columns WHERE column_name       = 'EMPLID' AND TABLE_NAME IN ( SELECT DISTINCT referenced_name FROM all_dependencies
WHERE name IN ( SELECT object_name FROM all_objects WHERE object_type   = 'FUNCTION' AND owner         = 'OBI_DW' ) AND referenced_type   = 'TABLE'

A Couple of Parting Notes

I’m happy to help you get your formatting preferences, just right. But, YOU can also do most of this on your own.

Let’s go back into the formatter preferences for a second.

Put your own code in, and start toggling switches.

This was done with version 18.2. There is a wee bug with the RIGHT ALIGN KEYWORDS preference. It MOSTLY works, but it’s off a few characters. This is fixed for version 18.3.

So, did we get to where our friend wanted? I think we’re VERY close.

I’m going to say, ‘Yes!’

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


  1. Hi Jeff,
    How about implementing a bit if AI into the autoformatting tool. Teaching it “how I like things” is a bit tedious. What if it could learn from some manually edited code examples and configure itself. Just an idea…

    • Have you tried using the Auto detection feature? Pretty sure we’re the only ones out there that even let you do this…

      Your AI idea is an interesting one. But I wonder how many users would hand format their code enough to make that a viable avenue.

    • That’s why we need AI, to identify patterns within unorganized data. Oracle recently started pushing AI solutions, this could be a good showroom.

Write A Comment