FB

How To Use the SQL Developer Format SQL OptionsIn 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.

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:

autocommit 01 crop

Mac:Preferences

The Preferences window is then shown.

Preferences

Click on the arrow next to the Code Editor item to expand the Code Editor section.

Click on the Format item.

SQL Developer Format

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.

Advanced Format

You’ll get this screen:

Advanced Format 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.

Format shortcut

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!