Reviewing my timeline, I came across this gem from Franc.

Franck isn’t alone – a lot of people hate it when their code doesn’t look the way they want it to.

Many folks know about our formatter, and how to use it to get their IDENTIFIERS UPPER-CASED.

But did you know you can have SQL Developer change the case of your code as you type it?

Here’s a 10 minute video where I show how to setup the formatter and the other code editor preferences to get your code, the way you want it.

The Video Demo

thatjeffsmith
Author

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

26 Comments

  1. When I go to Preferences -> Code Editor -> Format, I see the following option with a checkbox next to it “Autoformat Visible SQL and PL/SQL”.

    My assumption would be that checking this option would force SQL to automatically format code for you as you type it out. Pressing CTRL-F7 every time is a manual process, and therefore NOT “auto” anything.

    However, even though I have this “Autoformat Visible SQL and PL/SQL” option selected/enabled, none of my code ever formats unless I manually execute the format command.

    Am I misunderstanding this option in preferences? If so, could someone please explain the correct functionality? And if this option doesn’t actually auto-format code as you type it, can we please get an option to do that? It would be extremely nice to have the program automatically apply the format as I type everything out.

    • thatjeffsmith

      What does the help page say?

      It definitely isn’t for formatting as you type. It’s for formatting code thats generated, like getting the ddl for an object in an editor.

    • Mitchell

      This is what I found in the Help Center:

      “Autoformat PL/SQL in Procedures, Packages, Views, and Triggers: If this option is checked, the Format options are applied automatically as you enter and modify PL/SQL code in procedures, packages, views, and triggers; if this option is not checked, the Format options are applied only when you so request.”

      Maybe this is something different? But regardless, it sounds like formatting as you type isn’t an option. This is a bummer. It would certainly be a nice option to have.

      Thank you for taking the time to reply! I appreciate it.

  2. What happend to the feature “change case as you type” in version 18.1?

    Best regards,
    Matt

    • thatjeffsmith

      We removed it as the formatter offers the same functionality and having 2 ways to change the case for keywords/identifiers was causing problems.

    • Thank you for your real quick reply!

      With the “change case as you type” option, I was able to just type and all the keywords were automatically uppercase. This allowed to type quite fast and produced a nice formatted code.

      Who can I achieve the same with the formatter?

      Thank you,
      Matt

    • thatjeffsmith

      the formatter has an keywords uppercase pref, you an invoke the formatter with ctrl+F7

    • This is definitely NOT the same thing.
      It will format everything, also unwanted parts and must be called extra.

      Could you please give us the “change case as you type” option back?

      Thank you,
      Matt

    • thatjeffsmith

      In the sense that you’re asking the tool to change the way your code looks, it’s kind of the same thing.

      But, I’ll ask for a plain formatter option that ONLY does this…and doesnt touch the rest of your code.

    • Any chance to get the “localized” changes to the current line of code back? I find, I’ve become quite dependent on this feature and am constantly missing it, especially, when the formatter would spread my squeeze-into-a-single-page layout over two or more pages.

    • thatjeffsmith

      18.2 will have a formatter option to ONLY do the upper/lower for your keywords and identifiers. So you can choose to have your code touched on-demand, just in the way you were accustomed to before.

    • Great to hear! 🙂

      Will it be invoked by Enter at the end of the line or will it eat up the Ctrl+F7 shortcut, thereby diminishing the real formatter?
      Will it require an extra selection to limit it to the current line?

      I fear the usability might degrade from what we had before …

  3. Jeff,

    I experience inconsistent Completion Insight behavior. I prefer “Upper Keywords, Lower Identifiers” and set Completion Insight “Change case as you type” to that value. As I type and hit enter, the preceding line is correctly formatted, for a while. Then it changes to another random setting. One time it can be “Upper Case” while another time might be “Lower Case”. After each ghost change, I go back and reset it to my preference, only to have it change again some time later. I’m using Version 4.1.3.20 but this has been going on for a number of releases.

    Any suggestions for what I may be doing wrong or overlooking?

  4. Why do I get different results when I format a block of text, as opposed to writing a line of code and hitting enter to go to a new line? It seems that my settings should be universal over these two methods of operation?
    john3

    • thatjeffsmith

      bc those are two completely different mechanisms – just synch your change as you type case and formatter settings, or if you always format anyway, disable code as you type in insight

    • Since I prefer the way code-as-you-type caps elements, but I really like to format all as well, I guess I just have to do BOTH all of the time?

      john3

  5. Any way to get boolean connectors under “where”s to indent?

    where
    IDX.TABLE_OWNER = ‘TABLE_OWNER’
    and IDX.OWNER = SEG.OWNER
    and IDX.INDEX_NAME = SEG.SEGMENT_NAME
    group by
    IDX.INDEX_NAME,
    IDX.INDEX_TYPE

    • dang it, the indents were removed in my sample.

      After formatting in SQL Developer, the first predicate after the WHERE is indented, but subsequent ones (with AND or OR) are not. Would be nice if they would all line up below the first one.

  6. Stephen Miller Reply

    Since when is anything case sensitive in ORACLE SQL?

    Keywords are completely case insensitive, and all identifiers are automatically UPPER CASED unless they are surrounded by double quotes. When creating code, it is important to be able to easily differentiate between keywords and identifiers. Most formatters that do that differentiation allow either for keywords to be all upper case, and identifiers to be all lower case, or vice versa.

    Identifiers have the potential to be case sensitive, but any coder who takes advantage of that possibility, is not someone that I would want creating code for me.

    • thatjeffsmith

      That’s not what this post is about. Feel free to talk to Franck about it – click on the picture/twitter link. The conversation went on awhile and touched on what you’re talking about.

      I hate case sensitive object names in the database, for what it’s worth.

Write A Comment