Wrong formatting the final procedures of a package body

[adrianboangiu]

Hello,
I am using the current version of the formatter (master downloaded 11.03.2021) with SqlDeveloper 20.4.1.407 on a Windows 10 machine.
Formatting the attached file entirely (corresponding to a database package and package body) results in completely wrong formatting for the last three procedures: regenerateviews, getdataaccessflag and getdataaccessflags.
This is the file:
pack_views.zip

This is how regenerateViews procedure looks like before and after formatting:
before:

after:

However, I did some tests and this is what I found:

1. if I format the procedures individually the format succeeds
2. if I format all 3 of them only (by selecting only the 3 procedures) the format succeeds
3. if I format the last four procedures only then the format succeeds
4. if I format the wrong formatted procedures the format succeeds
5. if I format a second time the entire file the format fails again
   Can you please check what the problem is?
   Thank you

[PhilippSalvisberg]

First of all I tried to format it with the default formatter settings. - The result was similar.
So, this wrong behavior is not related to the formatter settings provided in this repository.

But why is the the result wrong? Because there is a syntax error in the code. Specifically on line 201. I've extracted the relevant select statement:

SELECT
  constraint_name
FROM
  user_cons_columns p
WHERE
  c.table_name = p.table_name
HAVING
  COUNT(1) = 1
GROUP BY CONSTRAINT_NAME 

The culprit is the HAVING condition. According to the Oracle SQL Language Reference 21c the HAVING condition is part of the group_by clause and cannot be placed before the GROUP BY.

Oracle SQL Developer's parser is implemented based on the language reference. Therefore the syntax error

The error is visible in the editor as as pink waved line under GROUP and on the right border.

It does not matter if the Oracle Database can compile the code. In fact placing the HAVING clause at this position works. This is one of the many discrepancies between the implementation and the documentation.

Anyway. The formatter must understand the code to produce a reasonable formatting result. The SQL Developer team decided to format the code even if there are syntax errors. It's a best effort approach which in other situation does not produce such a fatal result. It's a design design decision which I can understand when formatting an editor content in the IDE (you usually see the wrong result and can press Ctrl-Z).

It's important to note, that when you format your code in batch mode using SQLcl than the formatter skips files that cannot be parsed successfully.

So this should explain the behavior.

[PhilippSalvisberg]

I think it would be a good idea to configure if formatting should occur when parse errors are found. Therefore I posted this in the SQLDev forum: https://community.oracle.com/tech/developers/discussion/4481982/enhancement-request-for-sqldev-dont-format-code-with-parse-errors/p1?new=1

[adrianboangiu]

Hello,

You are right about the cause of the problem. The only thing strange is that the syntax error cause some minor formatting problem in the procedure where the syntax error is located and complete mess some 10-12 procedures later. I think that your request on SQLDev forum is correct.
Thank you.