Formatting not applied

[fdawans]

Hello,

I have sqlcl included in SQL Developer. I exported my formatting settings using SQL Developer to an .xml file.

However, when I format the plsql/sql file in VSCode, it formats to a default formatting. How can I apply my custom formatting ? I have also loaded the rules through sqlcl successfully, using FORMAT RULES

Here are my extension settings:

"oracle-format.sqlcl": "C:\\Program Files\\SQL Developer 4.1.5\\sqlcl\\bin\\sql.exe",
"oracle-format.rules": "C:\\Users\\xxxxx\\sql_formatting2.xml"
"oracle-format.languages": ["plsql","oraclesql"]

Any help ?

Edit: I am using SQL Developer 4.1.5 ... It seems very old, so that might be an issue. Also, the xml file seems to contain all formatting options, how can I select one?

Thank you,

[mickeypearce]

Hey. In the "Output" section of VSCode you should select "oracle-format" where you can see logging information.

have you tried formatting without "rules" setting?

[fdawans]

How do I format without rules ? By deleting the xml filepath in the extension setting? It's the same thing. But the xml file has multiple formatting options, so which one is supposed to be used by sqlcl ?

I am using SQLcl: Release 4.2.0, it seems that formatting options have changed in the meantime, see this link and comment thread from Mikhail Orlov (link). I guess I need to install a newer version and try again.

That's the output, no error:

SQL> format rules C:\Users\xxxxx\sql_formatting.xml
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> format file "c:\Users\xxxxx\AppData\Roaming\Code\User\workspaceStorage\3212a92428858e1584f78e0e02eb923b\mp.vscode-oracle-format\format_temp.sql" "c:\Users\xxxxx\AppData\Roaming\Code\User\workspaceStorage\3212a9242
```8858e1584f78e0e02eb923b\mp.vscode-oracle-format\format_temp.sql"
SQL> exit

[mickeypearce]

Yes, if you comment out the line in your settings: "oracle-format.rules": "C:\\Users\\xxxxx\\sql_formatting2.xml" it should use default formating options...

[mickeypearce]

If you generate a xml formatting file with SqlDeveloper, then it should be usable by SQLcl that comes with SQLDeveloper. At least in theory, :)

[fdawans]

I commented the line. I think that that's the issue, it only uses default formatting but not mine.

Yes, right, the xml should work in theory as SQLcl and SQLDeveloper are packaged together.

[fdawans]

Couldn't make it work with that version but it is working with sqldeveloper-20.4.1.407.0006-x64. The .xml file has a different layout vs SQL Developer 4.1.5.

[fdawans]

So, it works using format buffer in sqlcl. But it does not work using format file in sqlcl. So it obviously does not work in vscode.

Writing file to buffer then back to file works (see link)

format rules format.xml
get test.sql nolist
format buffer
save test.sql replace

[fdawans]

23 Same issue.