In this blog post I show how you can disable the formatter for some parts of your code. IntelliJ IDEA and the Eclipse IDE use tags in comments to identify sections of code that must not be formatted. By default these tags are @formatter:off and @formatter:on.
Example
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
SET SERVEROUTPUT ON -- begin for rec in(select r .country_region as region ,p . prod_category,sum( s.amount_sold ) as amount_sold from sales s join products p on p . prod_id = s .prod_id join customers cust on cust.cust_id=s.cust_id join times t on t . time_id =s. time_id join countries r on r.country_id = cust.country_id where calendar_year = 2000 group by r.country_region , p.prod_category order by r .country_region, p.prod_category ) loop if rec . region = 'Asia' then if rec.prod_category = 'Hardware' then /* print only one line for demo purposes */sys.dbms_output . put_line ( 'Amount: ' ||rec.amount_sold); end if;end if; end loop; end; / -- SELECT FISCAL_YEAR, COUNT(*) FROM SALES S NATURAL JOIN TIMES T GROUP BY FISCAL_YEAR ORDER BY 1; |
When I format this code with SQL Developer 20.2 and the default Trivadis PL/SQL & SQL Formatter Settings (plus lowercase keywords, lowercase identifiers) the result looks like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
set serveroutput on -- begin for rec in ( select r.country_region as region, p.prod_category, sum(s.amount_sold) as amount_sold from sales s join products p on p.prod_id = s.prod_id join customers cust on cust.cust_id = s.cust_id join times t on t.time_id = s.time_id join countries r on r.country_id = cust.country_id where calendar_year = 2000 group by r.country_region, p.prod_category order by r.country_region, p.prod_category ) loop if rec.region = 'Asia' then if rec.prod_category = 'Hardware' then /* print only one line for demo purposes */ sys.dbms_output.put_line('Amount: ' || rec.amount_sold); end if; end if; end loop; end; / -- select fiscal_year, count(*) from sales s natural join times t group by fiscal_year order by 1; |
Argh, I do not want the PL/SQL block to be formatted. I spent enough time to format it manually and I want to keep it that way. Let’s add @formatter:off and @formatter:on tags to the original code like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
SET SERVEROUTPUT ON -- @formatter:off begin for rec in(select r .country_region as region ,p . prod_category,sum( s.amount_sold ) as amount_sold from sales s join products p on p . prod_id = s .prod_id join customers cust on cust.cust_id=s.cust_id join times t on t . time_id =s. time_id join countries r on r.country_id = cust.country_id where calendar_year = 2000 group by r.country_region , p.prod_category order by r .country_region, p.prod_category ) loop if rec . region = 'Asia' then if rec.prod_category = 'Hardware' then /* print only one line for demo purposes */sys.dbms_output . put_line ( 'Amount: ' ||rec.amount_sold); end if;end if; end loop; end; / -- @formatter:on SELECT FISCAL_YEAR, COUNT(*) FROM SALES S NATURAL JOIN TIMES T GROUP BY FISCAL_YEAR ORDER BY 1; |
Now the formatter keeps the PL/SQL block as it is and formats only the rest.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
set serveroutput on -- @formatter:off begin for rec in(select r .country_region as region ,p . prod_category,sum( s.amount_sold ) as amount_sold from sales s join products p on p . prod_id = s .prod_id join customers cust on cust.cust_id=s.cust_id join times t on t . time_id =s. time_id join countries r on r.country_id = cust.country_id where calendar_year = 2000 group by r.country_region , p.prod_category order by r .country_region, p.prod_category ) loop if rec . region = 'Asia' then if rec.prod_category = 'Hardware' then /* print only one line for demo purposes */sys.dbms_output . put_line ( 'Amount: ' ||rec.amount_sold); end if;end if; end loop; end; / -- @formatter:on select fiscal_year, count(*) from sales s natural join times t group by fiscal_year order by 1; |
This does not work out of the box. Therefore you have to configure SQL Developer accordingly. Either by importing the latest Trivadis PL/SQL & SQL Formatter Settings (as I’ve done) or by adding an Arbori query yourself. I explain the latter in the next section.
Configure SQL Developer
To configure this solution you need SQL Developer 19.2 or later. Open the preferences dialog and go to Code Editor -> Format -> Advanced Format -> Custom Format .
Add the following Arbori query (e.g. after the dontFormatNode query). The position is not that important.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
dontFormatOffOnRanges: runOnce -> { var Integer = Java.type('java.lang.Integer'); var LexerToken = Java.type('oracle.dbtools.parser.LexerToken'); var Token = Java.type('oracle.dbtools.parser.Token'); var tokens = LexerToken.parse(target.input, true); // include hidden tokens not relevant to build a parse tree var hiddenTokenCount = 0; var format = true; for (var i in tokens) { if (tokens[i].type == Token.LINE_COMMENT || tokens[i].type == Token.COMMENT) { if (tokens[i].content.toLowerCase().contains("@formatter:off") || tokens[i].content.toLowerCase().contains("noformat start")) { format = false; } if (tokens[i].content.toLowerCase().contains("@formatter:on") || tokens[i].content.toLowerCase().contains("noformat end")) { format = true; } hiddenTokenCount++; } else if (tokens[i].type == Token.WS || tokens[i].type == Token.MACRO_SKIP || tokens[i].type == Token.SQLPLUSLINECONTINUE_SKIP) { hiddenTokenCount++ } else { /* expected types: QUOTED_STRING, DQUOTED_STRING, BQUOTED_STRING, DIGITS, OPERATION, IDENTIFIER, AUXILIARY, INCOMPLETE */ if (!format) { struct.unformattedPositions.add(new Integer(i-hiddenTokenCount)); } } } } |
Here are some explanations:
SQL Developer’s formatter class has a public field named unformattedPositions of type Set<Integer>. It contains all token positions that must not be formatted. We just have to extend this set. However, the parse tree contains only relevant tokens. Whitespace and comments are not relevant. But we need single-line and multi-line comments to disable and enable the formatter. That’s why we read all tokens on line 5. Now we can determine if a token should be added to the unformattedPositions on line 29. The variable i contains the current token position. The hiddenTokenCount contains the number of preceding tokens that are not part of the parse tree. i-hiddenTokenCount equates to the token position in the parse tree. The rest should be self-explanatory.
Read this post to learn more about Arbori and how the formatter works.