noformat not working

[jcarey9149]

I've been trying to handle a couple of cases where I don't want the formatter to do anything. First one is this:

DECLARE
/* @formatter:off */
   full_dnames CLOB := 
   '[{"NAME":"ORDERNUMBER","TYPE":"NUMBER","DESIGNATION":"DIMENSION","GRADE":1},
   {"NAME":"ORDERNUMBER","TYPE":"VARCHAR","DESIGNATION":"DIMENSION","GRADE":1},
   {"NAME":"FIRSTNAME","TYPE":"VARCHAR","DESIGNATION":"ATTRIBUTE","GRADE":1}';
/* @formatter:on */
BEGIN
   NULL;
END;

Formatter result:

DECLARE
/* @formatter:off */
   full_dnames CLOB :=  '[{"NAME":"ORDERNUMBER","TYPE":"NUMBER","DESIGNATION":"DIMENSION","GRADE":1},
   {"NAME":"ORDERNUMBER","TYPE":"VARCHAR","DESIGNATION":"DIMENSION","GRADE":1},
   {"NAME":"FIRSTNAME","TYPE":"VARCHAR","DESIGNATION":"ATTRIBUTE","GRADE":1}';
/* @formatter:on */
BEGIN
   NULL;
END;

My intent is to override the formatter's desire to put the first line of the assignment on the same line as the declaration to keep the data lined up. I've tried both the @formatter:off and noformat start versions in both single line and multiline comments.

I'm using the files in settings/sql_developer with sqldeveloper v22.2.1.

[PhilippSalvisberg]

Hi,

I cannot reproduce the problem. When I format this:

DECLARE
/* @formatter:off */
   full_dnames CLOB := 
   '[{"NAME":"ORDERNUMBER","TYPE":"NUMBER","DESIGNATION":"DIMENSION","GRADE":1},
   {"NAME":"ORDERNUMBER","TYPE":"VARCHAR","DESIGNATION":"DIMENSION","GRADE":1},
   {"NAME":"FIRSTNAME","TYPE":"VARCHAR","DESIGNATION":"ATTRIBUTE","GRADE":1}';
/* @formatter:on */
BEGIN
   NULL;
END; 

Then I get this formatted result:

declare
   /* @formatter:off */
   full_dnames CLOB := 
   '[{"NAME":"ORDERNUMBER","TYPE":"NUMBER","DESIGNATION":"DIMENSION","GRADE":1},
   {"NAME":"ORDERNUMBER","TYPE":"VARCHAR","DESIGNATION":"DIMENSION","GRADE":1},
   {"NAME":"FIRSTNAME","TYPE":"VARCHAR","DESIGNATION":"ATTRIBUTE","GRADE":1}';
/* @formatter:on */
begin
   null;
end;

I suspect that you do not use the settings provided in this repository. Another Arbori program. Maybe the default provided by SQLDev?

Could you please provide screenshots of your settings? They should look similar to this:

[jcarey9149]

I should mention that I can work around this with a strategically placed comment. However in the full case the assignment had to be broken up into a couple of strings, and I don't want the default '||' operator alignment (with the ':=') for the same reason as the first line break.

[jcarey9149]

I tried reloading the settings from the repository, and all my formatting went away. I'll look into this.

[jcarey9149]

I think you were right, but having reloaded the files there is no formatting. Looks like when I load the advanced xml file it resets the custom code. How can I debug this?

[jcarey9149]

Looks like the files as downloaded a couple of days ago just don't work with sqldeveloper 22.2.1. I reinstalled v 21.4.3 and it seems to be working.

[PhilippSalvisberg]

I think you were right

Thanks for confirming it.

having reloaded the files there is no formatting. Looks like when I load the advanced xml file it resets the custom code.

Correct. Just follow the installation instructions. ;-)

How can I debug this?

Debugging in SQLDev is a bit tricky. The simplest approach is to enable logging. You have to change the logging.conf in SQLDev. This comment explains how to do it.

However, you can also check the version of the Arbori program in the Setting under "Code Editor -> Format -> Advanced Format -> Custom Format". As shown in the screenshot above. Here are some links based on released versions:

• For SQLDev 22.2.1 (with dbtools-common.jar from SQLcl 22.3.0): trivadis_custom_format.arbori
• For SQLDev 22.2.0: trivadis_custom_format.arbori
• For SQLDev 21.4.3: trivadis_custom_format.arbori

Looks like the files as downloaded a couple of days ago just don't work with sqldeveloper 22.2.1.

The current version in the main branch is 5 months old and works with a manually patched version of SQLDev 22.2.1 and SQLcl 22.3.0 and SQLcl 22.4.0.

The latest release is from 2022-10-09.

So, which version did you download? Actually, one that does not match the product version you are using.

The XML configuration file trivadis_advanced_format.xmlis more or less version independent. However, the Arbor file trivadis_custom_format.arbori is very much dependent on the version of SQLDev and SQLcl. Unfortunately, the release cycles of SQLDev and SQLDev are independent now. This leads to the situation where sometimes no configuration exists that works in the latest versions of both products. The dirty workaround is to manually patch SQLDev with the libraries from SQLcl.

The reason for the version-dependency is that the Arbori program queries the parse-tree and uses specific symbol names expected in the parse tree. When new SQL features are supported in the product or parser bugs are fixed then the parse-tree structure and the used symbol names change as well. This leads to runtime errors in the Arbori program. This is why you really need to check that you install the configuration for the correct product version.

If you are interested in the details you can look at the differences between version sqldev-21.4.3 and sqlcl-22.3.0. There you see that some symbol names in trivadis_custom_format.arbori have changed, e.g.

• column[4,12) to column___0
• create_view#[114,130) to create_view#___0
• create_materialized_view[33,79) to create_materialized_view2___0
• "inner_cross_join_clause"[26,55) to "inner_cross_join_clause"___0
• in_condition[24,35) to in_condition___0
• simple_case_expression[4,27)# to simple_case_expression___0#
• create_materialized_view[91,136) to create_materialized_view3___2
• create_materialized_view[136,174) to create_materialized_view3___3
• create_materialized_view[200,210) to create_materialized_view3___0
• insert_into_clause[13,25) to insert_into_clause___0
• merge_insert_clause[23,35) to merge_insert_clause___0
• merge_insert_clause[43,50) to merge_insert_clause___2
• ...

In other words, a lot changed between these versions making them completely incompatible from a SQL grammar point of view.

In https://github.com/Trivadis/plsql-formatter-settings/releases you find all released versions for the formatter settings. There is also a section that defines the compatibility with SQLDev and SQLcl. In some cases, you have to manually patch SQLDev to make it work (copy a JAR file from the SQLcl installation). If you do not want to do that, you have to use a different version.