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
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:
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:
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.
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.
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.
2 Comments
FYI – Following this post instructions, I copy / paste the SQLDEV ‘Custom Format’ – Arbori – into editor and search for ‘dontFormatOffOnRanges’ which is not found (possibly due to previously patching this content based on github post applied weeks ago. https://github.com/Trivadis/plsql-formatter-settings)
However, I quickly tested these `@formatter:off` and `@formatter: on` comments they appear to work as described. Thank you.
The Trivadis PL/SQL & SQL Formatter Settings contains in the A3 section the code for disabling/enabling the formatter. The code snippets in this blog post are applicable for the default Arbori code shipped by the SQL Developer Team.