Disable Formatter for Code Sections in SQL Developer

blank

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

Example without tags (before)
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:

Example without tags (after)
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:

Example with tags (before)
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.

Example with tags (after)
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.

Arbori query ‘dontFormatOffOnRanges’
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. The 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.

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.