In this article, I’ll explain what formatting SQL is, and how to find the SQL Developer Format SQL options.
What Is SQL Formatting?
SQL formatting is the process of neatly arranging your code to make it more readable.
When you write SQL, the format of the code is not relevant to the database. By this, I mean that the SQL statement will execute if the syntax is correct and the formatting is terrible.
Formatting the SQL code will clean up the line breaks, indentation, capitalisation, and overall look of your SQL. Many of the Oracle SQL IDEs have this feature, including SQL Developer.
You can do this manually, or automatically, in SQL Developer. We’ll discuss them in this article.
While you’re here, if you want a helpful list of ways to save time with Oracle SQL Developer, get my PDF guide here:
Why Should I Format SQL Code?
Have you ever had a look at someone else’s code, and tried to work out what it’s doing, but couldn’t because the code was all over the place? Similar statements weren’t grouped, capitalisation wasn’t used, the code was not indented consistently, and it was just a mess to understand?
Well, this is precisely the reason you should format your SQL code.
It makes it easier for you to read in the future, for when you need to make changes.
It makes it easier for you right now, as you’re working on the code.
It also makes it easier for others to read.
It helps with debugging as well because if the code is well formatted, it’s easier to see where any issues are and work on them.
So, there are a few reasons you should format your SQL code. How do you do it in SQL Developer?
How To Change The SQL Developer Format SQL Options
The formatting options inside SQL Developer are some of my favourite options in the program. Without this feature, my SQL writing would be a lot worse.
To find these options, go to Tools > Preferences (on Windows) or Oracle SQL Developer > Preferences (on Mac).
Windows:
Mac:
The Preferences window is then shown.
Click on the arrow next to the Code Editor item to expand the Code Editor section.
Click on the Format item.
Simple Format Options
Now, there are many options here for formatting SQL. The SQL Formatter entry allows you to automatically format the SQL, with the “Autoformat Visible SQL and PL/SQL”. This is turned off by default.
There are several options you can change on this screen, and more advanced options are available. On this screen:
- Autoformat Visible SQL and PL/SQL: If this is checked, the code that is generated by SQL Developer (such as generating DDL) is automatically formatted. It doesn’t “automatically format your SQL code” as you type.
- Indent spaces: this will set the number of spaces that are indented when you press the Tab key. The default is 4.
- Identifiers case: This will set the case of the identifiers in your code (variables, table names, column names, etc). You can change it to Upper, Lower, Init Cap, and Unchanged. Change this to see a preview in the main screen.
- Keywords case: This will set the case of your SQL keywords in your code. You can change it to Upper, Lower, Init Cap, and Unchanged. Change this to see a preview in the main screen.
- Convert case only: This means only the case will be changed when you run the Format SQL command, and none of the other formatting options will be applied.
Advanced Format
The main area where the format SQL options are is under the Advanced Format. So, expand the Format entry on the side panel and select Advanced Format.
You’ll get this screen:
There are many options you can change here.
General:
- Keywords case
- Identifiers case
- 1-line long comments
Alignment:
- Column and Table Aliases
- Type Declarations
- Named Argument Separator =>
- Assignment Operator :=
- Equality Predicate =
- Right-Align Query Keywords
Indentation
- Indent spaces
- Indent with
Line Breaks
- On comma
- On procedure arguments
- On concatenation
- On Boolean connectors
- On ANSI joins
- For compound_condition parenthesis
- On subqueries
- Max char line width
- Before line comments
- After statements
- SELECT/FROM/WHERE
- IF/CASE/WHILE
White Space
- Around operators
- After commas
- Around parenthesis
When you change any of these options, the preview panel on the right will be updated to indicate how the code will look. Feel free to change these options and see how they impact your code.
Also, you can save and exit out of the windows and see how it will directly impact any code you’re working on.
How do you format your SQL code in SQL Developer?
You can press CTRL+F7 (on Windows) to format the SQL code in your current Code Editor window to update the formatting of the code based on any format changes you have made.
There is no shortcut set on Mac for formatting SQL code. You can right-click in the Editor and select Format.
Or, you can open Preferences, click on Shortcut Keys, and search for Format. You can then set a shortcut key.
So, that’s how you can access and change the SQL Developer format SQL options.
If you want to see a full list of the Oracle SQL functions, along with tips and examples, view my Oracle SQL Functions page.
Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!
While you’re here, if you want a helpful list of ways to save time with Oracle SQL Developer, get my PDF guide here:
Thank you for this info.
What version of Oracle SQL Dev is the formatter in? I am running 17.4.0.335 and do not see this option.
This article is out dated, the preferences for SQL developer are now under Code Editor/Format/Advanced Format and I think the options are different/rearranged, or at least I’m not seeing what is referenced in the article.
Thanks Michelle, I’ll have to update the article!
Can you please send me your SQL Dev Formating file.
Hi Slava, I don’t use a formatting file in SQL Developer, I use the default options.