Do Not Format Invalid Code in SQL Developer



What happens when you call the formatter in SQL Developer for invalid code? Until recently SQL Developer tried to format it anyway. This produced strange results in some cases. Starting with SQL Developer version 21.2.0 there is a hidden option to suppress formatting when the code is invalid.

What Is Valid Code?

If the code can be compiled and executed, it is valid. Right? – Well, SQL Developer uses a parser written in Java. The resulting lexer token stream and the parse tree are essential inputs for the formatter. If the parser does not understand the code then it produces a partial parse tree. This means the parse tree is incomplete. In such cases the formatting result is unreliable. And it does not matter if the code can be compiled and executed.

Here’s an example of a valid SQL statement that produces a query result but still reports a syntax error.


There are three options in SQL Developer to spot a syntax error in an editor.

  1. A pinkish wavy line below the token (group) in the editor that is responsible for the syntax error. When you hoover over it a pop-up window with additional information appears.
  2. A pink area on the right border of the editor. When you hover over it a pop-up window with the code excerpt appears. When you click on it the cursor is positioned on the token with the pinkish wavy line (group).
  3. Syntax error; Partial parse tree: is shown as first line in the code outline window

According to the SQL Language Reference 21c this syntax is not allowed.

However, Oracle’s implementation allows to write the HAVING condition before the GROUP BY. That’s a fact.

As you can see, it is not so easy to write a complete parser based only on the documentation.

The Problem

When I call the formatter in SQL Developer with my favorite formatter settings for this code

then I get the following result:

When I fix the syntax error (from a SQL Developer’s parser perspective) in the original code like this:

then the formatter result is:

In this example the difference is small. Just the group by clause which could not be formatted with the syntax error. However, in other cases the formatter result might be really weird. So in my opinion it is better to not format invalid code.

The Solution

Open the preferences in SQL Developer and export the Advanced Format settings as shown in this screenshot.


Then open the exported XML file in an editor and add the highlighted line:

Save the file and import it into the preferences of SQL Developer. It’s the same screen as before, but this time use the Import button.

Afterwards, SQL Developer will format valid code only.

The latest Trivadis PL/SQL & SQL Formatter Settings also use <formatWhenSyntaxError>false</formatWhenSyntaxError>.


Now you can decide whether you want to format code with syntax errors in SQL Developer. I recommend not to format invalid code. In most cases you will not be satisfied with the result anyway. And with larger files, you may not realize until much later that undo is no longer a simple keyboard shortcut.

Many thanks to the SQL Developer team and especially to Vadim Tropashko for implementing this enhancement request.


  1. blank Yvo Breuer says:

    Hello Philipp,

    I’ve been trying to add the line “true” to my Advanced Format XML-file, but it doesn’t work. The code compiles fine, but the “Syntax error; Partial parse tree:” remains.

    When I import my XML-file, which includes the line “false” and after that I export the Advanced Format options to XML, I would expect it to still contain this line. But it doens’t, the whole line has disappeared. (So it also doens’t occur that the formatWhenSyntaxError option is set to true.)

    When I do the same and change other options, then these options are indeed changed after importing the XML but the formatWhenSyntaxError option is again removed.

    Is there any other solution for this? Or do you know to whom this can be addressed?

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.