Do Not Format Invalid Code in SQL Developer

Introduction

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 hover 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 the 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 favourite formatter settings for this code

select
   constraint_name
from
   user_cons_columns c
where
   c.table_name = 'EMP'
having
   count(1) = 1
group by
   constraint_name;

then I get the following result:

select constraint_name
  from user_cons_columns c
 where c.table_name = 'EMP'
having count(1) = 1
group by
   constraint_name;

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

select
   constraint_name
from
   user_cons_columns c
where
   c.table_name = 'EMP'
group by
   constraint_name
having
   count(1) = 1;

then the formatter result is:

select constraint_name
  from user_cons_columns c
 where c.table_name = 'EMP'
 group by constraint_name
having count(1) = 1;

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:

<options><adjustCaseOnly>false</adjustCaseOnly>
<alignTabColAliases>true</alignTabColAliases>
<breakOnSubqueries>true</breakOnSubqueries>
<alignEquality>false</alignEquality>
<formatWhenSyntaxError>true</formatWhenSyntaxError>
<singleLineComments>oracle.dbtools.app.Format.InlineComments.CommentsUnchanged</singleLineComments>
<breakAnsiiJoin>false</breakAnsiiJoin>
<maxCharLineSize>128</maxCharLineSize>
<alignAssignments>false</alignAssignments>
<breaksProcArgs>false</breaksProcArgs>
<alignRight>false</alignRight>
<breaksComma>oracle.dbtools.app.Format.Breaks.After</breaksComma>
<breaksAroundLogicalConjunctions>oracle.dbtools.app.Format.Breaks.Before</breaksAroundLogicalConjunctions>
<alignNamedArgs>true</alignNamedArgs>
<formatProgramURL>default</formatProgramURL>
<formatThreshold>1</formatThreshold>
<spaceAroundOperators>true</spaceAroundOperators>
<useTab>false</useTab>
<idCase>oracle.dbtools.app.Format.Case.lower</idCase>
<extraLinesAfterSignificantStatements>oracle.dbtools.app.Format.BreaksX2.X2</extraLinesAfterSignificantStatements>
<breaksConcat>oracle.dbtools.app.Format.Breaks.Before</breaksConcat>
<spaceAroundBrackets>oracle.dbtools.app.Format.Space.Default</spaceAroundBrackets>
<flowControl>oracle.dbtools.app.Format.FlowControl.IndentedActions</flowControl>
<commasPerLine>5</commasPerLine>
<forceLinebreaksBeforeComment>false</forceLinebreaksBeforeComment>
<alignTypeDecl>true</alignTypeDecl>
<breakParenCondition>false</breakParenCondition>
<parseForwardAndBackward>true</parseForwardAndBackward>
<identSpaces>4</identSpaces>
<breaksAfterSelect>true</breaksAfterSelect>
<spaceAfterCommas>true</spaceAfterCommas>
<kwCase>oracle.dbtools.app.Format.Case.UPPER</kwCase>
<formatWhenSyntaxError>false</formatWhenSyntaxError>
</options>

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

Summary

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.