Formatting Keywords to UPPERCASE In Oracle SQL Developer

thatjeffsmith SQL Developer 28 Comments

Tell Others About This Story:

I received this question from a customer today, and it took me more than a few minutes to remember where this preference was located in SQL Developer. This tells me that the topic is ripe for blogging 🙂

How do I go FROM:

SELECT *
FROM scott.emp
WHERE ename LIKE '%JEFF%'

TO

SELECT      *
     FROM scott.emp
     WHERE ename LIKE '%JEFF%'

It’s all in the formatting

You need to access the formatting preferences under the Tools menu. It takes a bit of navigating to get there, so bear with me:

  • Tools
    • Database
      • SQL Formatter
        • Oracle Formatting
        • Click ‘Edit’ on the profile
        • Other
          • Case change: ‘Keywords Uppercase’

It’s easy to find once you know where to look?

You can tell it to leave the case alone, upper everything, upper only the keywords, lower everything.

Accessing the Formatter Options

We allow separate formatting options for different RDBMS. You need to make sure you’re accessing the ‘Oracle Formatting’ page in the preferences. You can then choose to edit the default options OR you can do what I have done – save the defaults as a new set of options. I’ve called my profile ‘JeffCustom.’ I can now switch back and forth now through different sets of formatting options.

You need to hit the ‘Edit’ button to get to the formatting options editor. A good number of people seem to miss this.

Select your profile, then hit the ‘Edit’ button

But I Don’t Want to Use the Formatter, I Want it to Change as I Type!

I do enjoy a demanding user 🙂 Especially when I have a good answer for them! Open your preferences again, and this time go to the Code Editor page. Select ‘Completion Insight.’ Look at the bottom of the preferences page now:

Change the case of the keywords as you type…presto changeo!

We’re relying on our parser to correctly identify the keywords here, so the words will change AS the parser recognizes them. This means they might not change right way, but rather as the SQL statement you’re working on gets closer to completion.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 28

  1. Thank you for writing this blog! SQL developer making things lowercase every time I start writing on a new line was really annoying. I am glad I found this page. uppercase or lowercase matters o me when writing case when statements. Thanks a lot again.

  2. Hi Jeff,

    I have enabled both settings to change the Keywords to UPPERCASE and Identifiers to LOWERCASE.

    However, when I type keywords like ‘EXIT’ or ‘FETCH’ or ‘EXCEPTION’ or ‘LOOP’ or ‘COUNT’ or ‘FIRST’ or ‘LAST’ (without quotes obviously!) and hit enter as I type, it changes them to lowercase. I’m assuming that these words are not keywords for some reason?

    How can I make them stick to UPPERCASE?

  3. Is it possible to format the plsql with table name and column name in upper case. I would like to format the code at the end, not using “completion insight”.

  4. @Rob – that is already one of the options for completion insight – “Lower Keywords, Upper Identifiers.”

  5. I would like it the other way around.. I want the keywords in lower case but all my table names, columns etc in uppercase so that it is my data standing out from the keywords. Can that be done?

    Rob
    🙂

  6. Hi, thanks for you blog! It is very, very helpfull!

    Some news about the “Upper Keywords, Lower Identifiers” option? It is still lacking in 4.0.3…

  7. Ok, that makes me feel better – I swear it was there in v2 or v3. I am so used to having everything formatted that way and I couldn’t figure out why it wasn’t allowing it anymore.

    Thanks a ton.
    Dan

  8. Thanks for the post – it was so close to getting me what I was looking for. In the completion insight preferences, why isn’t there an option for “Upper Keywords, Lower Identifiers” like the original example you gave in the post? Something like…

    SELECT t.bleh
    FROM table t
    WHERE t.bleh = ‘This would be awesome’;

    How can we make that happen?

    Thanks,
    Dan

    1. thatjeffsmith Post
      Author
  9. Jeff,

    SQL Developer is not recognizing all keywords (e.g. EXCEPTION, ELSIF, RAISE, LOOP, OUT, NOCOPY) for formatting. It’s showing them in blue, but is not capitalizing them. Is there a way to fix this manually?

    Also, would it be possible to not show column names colored/capitalized if they match a keyword and they are prefixed with a table (e.g. mytab.value ). I understand it could be hard if the column names are not prefixed, but I think for prefixed columns it should be possible.

    Thanks,

    Arnold van Koppen

    1. Any more word on correcting the recognition and formatting of keywords by SQL Formatter that Arnold points out?

      Is there alternatively a workaround in a configuration file?

    1. thatjeffsmith Post
      Author
  10. I have SQL Developer 4.0.0.13 (build 30). I have choose the option of UpperCase but I see this happens only after I press Enter (but not if I press enter twice) but not as I type.

    Please help. Thanks
    Bala

  11. The setting for “Change case as you type” keep changing automatically even though I set it to “Upper Keywords” option. I’m not sure why it is changing and I wish there is an option to keep it permanent. Any help is appreciated!

    1. thatjeffsmith Post
      Author
  12. Nice posts Jeff!
    I’m missing more options in this formatter tool comparing this to Toad. For example I want also capitals for variable names. The option “Whole SQL Uppercase” is not a solution, this option corrupts our SQL.
    Will the upcoming new releases satisfy our needs? 🙂

    1. thatjeffsmith Post
      Author
  13. I know a lot of people like upper-cased SQL and PL;/SQL, but to me it’s always looked like somebody’s SHOUTING all the time. Much prefer lower-cased code, and upper-cased tables and columns. Anyway, thanks for the tip.

    1. thatjeffsmith Post
      Author
  14. I followed the instructions for changing keywords to uppercase as I type (Database->SQL Formatter->Oracle Formatting->Edit->Other->Case change->Keywords Uppercase; then Code Editor->Completion Insight->Change case as you type->Upper Keywords), but the case never changes until I hit Enter for a new line. Did I miss something?

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author

      And I do appreciate your support Amber! I’d write more personal tech based posts, but my core audience are the software and database geeks.

      Maybe I’ll try writing more personal based stuff on the weekends before the kids drive me crazy 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *