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’
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.
But I Don’t Want to Use the Formatter, I Want it to Change as I Type!
This ‘feature’ no longer exists as of version 18.1 — you’ll need to follow the steps above. Why did we remove it? It was causing problems, and we already had a feature that handled this requirement. The formatter also has a ‘dead simple mode in version 18.2 – you can tell the formatter to ONLY CASE Your keywords And IDENTIFIERS.
A legacy feature allowed the parser/code insight code base to automatically CASE your code as you typed. Select ‘Completion Insight.’ Look at the bottom of the preferences page now:
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.
32 Comments
Hi, thanks for your article.
What’s the name of this font?
Of the blog or the code as shown in the pictures?
I’d like to know the font of the code as shown in the pictures. I use Consolas but I’m still looking for a better mono font. When you lppk at it all day, it actually matters 🙂
Thanks in advance.
We now SHIP and recommend you use the HACK font.
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.
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?
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”.
That must be a later version. I have 4.0.3.16 and Under Case change:
– Keywords uppercase
– No change
– Whole SQL uppercase
– Whole SQL lowercase
@Rob – that is already one of the options for completion insight – “Lower Keywords, Upper Identifiers.”
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
🙂
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…
I put in a feature request for this, feel free to vote for it if you want it…
https://apex.oracle.com/pls/apex/f?p=43135:7:13878556445763::NO:RP,7:P7_ID:41521
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
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
Yeah, that used to be there – I doubt it was removed intentionally. I’ll go pester the developer and see what’s what.
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
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?
Yes, it would be really handy if SQLDeveloper would recognise all keywords.
Is there a way of editing the keyword list?
Thank you so much. It is really useful. Appreciate your help
You’re welcome Meddy!
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
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!
Is this the only preference that keeps changing on you? Maybe you don’t have write permissions on your user directory where the preferences are stored?
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? 🙂
That particular uppercase preference is still lacking Rinie. I think it’s something we could probably add though. Would you mind submitting it to our Exchange?
@Jeff: Done! Now see how many people are willing to vote 🙂 Thanks for your reply!
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.
I’m with you mostly, although I will Initcap somethings…
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?
I don’t think you’re missing anything, I think the tool’s behavior isn’t living up to the promise of that preference. Let me investigate…
I really wish I could understand half of these posts, but I am at least going to be here in support 🙂
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 🙂