Dear SQLDev team,
I’m using SQLDev 21.2.1 with the default formatter settings (also reproducible with SQLcl 21.3.0).
Calling the formatter for
set serveroutput on
spool install_options.tmp
declare
procedure print (in_line in varchar2) is
begin
dbms_output.put_line(in_line);
end print;
begin
print('Hello World');
end;
/
produces this result:
SET SERVEROUTPUT ON
SPOOL install_options.tmp declare
PROCEDURE print (
in_line IN VARCHAR2
) IS
BEGIN
dbms_output.put_line(in_line);
END print;
BEGIN
print('Hello World');
END;
/
The formatted statement is invalid.
IMO the reason for this behavior is, that the parser does not correctly detect the end of the SPOOL command. You see that in the code outline.
The SPOOL command includes the DECLARE token of the anonymous PL/SQL block.
Here’s another example using the @ command.
define table_folder = 'table'
set define on
@./demo/&&table_folder/drop_demo_tables.sql
set define off
The formatter result is:
DEFINE table_folder = 'table'
SET DEFINE ON
@./ demo / &&table_folder / drop_demo_tables.sql set
DEFINE off
The resulting code is broken. The @ command is invalid. There are whitespaces around the slashes and the line break before the SET token was removed.
The code outline shows the same issue for the @ command as for the SPOOL command:
BTW: The `&&table_folder` substitution variable is a workaround for the parse error caused by the `table` literal.
I’ve found workarounds for these issues (see https://github.com/Trivadis/plsql-formatter-settings/issues/149 and https://github.com/Trivadis/plsql-formatter-settings/issues/150).
However, the workaround requires changes in the code to be formatted, which is not optimal.
I hope you can provide a fix in the next SQLDev version.
Thank you.