SQLcl command FORMAT FILE not work

[AlexandrSimonenko]

SQLcl: Release 20.2 Production on Сб окт 31 15:32:26 2020

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

SQL> host type test.sql
BEGIN
    SELECT
        *
    FROM
        dual;
END;

SQL> FORMAT RULES c:\oracle\format_setting.xml
Formatter rules loaded
SQL> FORMAT file test.sql test6.sql
SQL> host type test6.sql
BEGIN
    SELECT
        *
    FROM
        dual;

END;

No changes after formatting.
Formatting happens but not according to my rules.
It looks like the loaded rules don't affect the result.
But if you do formatting via the buffer without changing the formatting rules ...

SQL> get test.sql
  1  BEGIN
  2      SELECT
  3          *
  4      FROM
  5          dual;
  6* END
SQL> FORMAT buffer
  1  BEGIN
  2    SELECT
  3      *
  4      FROM
  5      dual;
  6* END;

В этом случае похоже правила сработали.

[mickeypearce]

This is seems a sqlcl bug. Have you tried version 20.3?

[AlexandrSimonenko]

SQLcl: Release 20.3 Production on Сб окт  31 18:17:34 2020

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

SQL> FORMAT file test.sql test6.sql
SQL> host type test6.sql
BEGIN
    SELECT
        *
    FROM
        dual;

END;

SQL> get test.sql
  1  BEGIN
  2      SELECT
  3          *
  4      FROM
  5          dual;
  6* END
SQL> FORMAT buffer
  1  BEGIN
  2    SELECT
  3      *
  4      FROM
  5      dual;
  6* END;
SQL>

20.3 didn't change anything.
The file formatting does not use the loaded rules.

[AlexandrSimonenko]

it looks like it stretches from version 19

[steveduffy]

Having the same issue.

Could you load format_temp.sql to the buffer via a get, format it in the buffer, then SAV format_temp.sql REPLACE to work
around the sqlcl issue?

[mickeypearce]

That would great if it could be done. We could make it as a setting option. @steveduffy you are kindly invited to open a PR if you feel like contributing.

[fdawans]

"Fixed" it by downgrading to SQLcl 19.2.1 (included in SQL Developer 19.2.0)
Here is the download link for SQL Developer (need to make an account and start a first download to accept the license terms).
Link

[mickeypearce]

Thanks @fdawans for sharing!

[fdawans]

Well... Actually no, it seemed to work a few times but it's broken again.
In SQLcl 19.2.1, format buffer and format file both seem to work as intended (instead of only format buffer in sqlcl 20.x).
But then the extension still uses the default settings.

Edit: I think it's an issue in the escape characters of the file path for the .xml and/or the sql.exe.
I replaced in settings.json (for both user and workspace, not sure how that works).
C:\\Users\\xxxx\\sql_formatting.xml by C:/Users/xxxx/sql_formatting.xml
I used a path without spaces and it works again.
I am on Windows but it looks like it might be related to #5 .

[philpalnau]

I just wanted to update, I tested the newest version of SQLcl and the format sees to work again (sqlcl-21.2.1.195.1252.zip) . Good Luck!

[fdawans]

I have a new issue with the having + group by keywords within a CTE, for both versions.
It works in sqlcl-21.3.0.278.1045, see below, but not in vscode.

Can someone try to format this on his version ?
WITH test AS ( SELECT 1 FROM dual WHERE 1 = 1 HAVING 1 = 1 GROUP BY 1 ) SELECT * FROM test

[mickeypearce]

With SQLcl: Release 19.2.1 I get "parse error"... haven't tried with 21 yet.

[fdawans]

I went back to SQLcl 19.2.1, I liked that formatting better (I probably need to update my formatting rules for 21)
Yes, I have the same 'parse error' in vscode.