Formatter fail

[jianghoy]

After using the formatter for a couple of times, it started to return parse error for every SQL script( even those successes before so it cannot be malformed SQL issue). My guess is it might have something to do with oracle sqlcl connection.

Some info that might help:
VS Code version: 1.44.2
Oracle sqlcl version: 19.4.0
OS version: macOS Mojave 10.14.6

[mickeypearce]

Can you please post error message from the output window (oracle-format tab)?

[jianghoy]

Sure.

""Users/jianghoy/Downloads/sqlcl/bin/sql"" /nolog @"/Users/jianghoy/Library/Application Support/Code/User/workspaceStorage/1832d6d8dc4b67509a2f38f4066fe80c/mp.vscode-oracle-format/format.sql"

SQLcl: Release 19.4 Production on Mon May 04 23:19:46 2020

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

SQL> format file "/Users/jianghoy/Library/Application Support/Code/User/workspaceStorage/1832d6d8dc4b67509a2f38f4066fe80c/mp.vscode-oracle-format/format_temp.sql" "/Users/jianghoy/Library/Application Support/Code/User/workspaceStorage/1832d6d8dc4b67509a2f38f4066fe80c/mp.vscode-oracle-format/format_temp.sql"
Skipped formatting /Users/jianghoy/Library/Application Support/Code/User/workspaceStorage/1832d6d8dc4b67509a2f38f4066fe80c/mp.vscode-oracle-format/format_temp.sql -- parse error
FORMAT
---------

FORMAT BUFFER - formats the script in the SQLcl Buffer
FORMAT RULES <filename> - Loads SQLDeveloper Formatter rules file to formatter.
FORMAT FILE <input_file> <output_file> 

Format used is default or for SQLcl can be chosen by setting an environmental variable
pointing to a SQLDeveloper export (.xml) of formatter options.
The variable is called SQLFORMATPATH
In SQLDeveloper the format options are the default chosen in the preferences.
SQL> exit

[mickeypearce]

Looks like there is something wrong with syntax of file being formatted, or not? Can you please try with a simple bullet proof sql like "select 1 from dual;"

Did you use this version of sqlcl 19.4 even before or you upgraded recently?

[jianghoy]

Looks like wrong syntax issue. Simple query works fine.
I just started using formatted so it's always 19.4.
Do you think setting up formatted options would help?

[mickeypearce]

No, I don't think it would.. You are trying to format an invalid plsql block. Try to compile it and you will find an error...

[jianghoy]

In fact it's not pure plsql per se. It's an in-house ETL platform for more than 10 yrs. At the beginning, it was backed by Oracle DB, later on, the company decided to eat its own dog food so swapped out the backing DB, but the platform remains to use something plsql compatible. At this point, people no longer want to add more features onto it, so there's no formatter available.