Cannot get it to work

[iarsov]

Hi,

I have sqlcl installed, but on save nothing happens.

I have set "oracle-format.sqlcl": "/Users/iarsov/Workspace/sqlcl/bin/sql".

On save nothing happens. On manual format selection I get message "There is no selection formatter for 'sql'-files installed."

Any idea what might be the case?

[iarsov]

manual execution of "echo format file a.sql a.sql) | sql /nolog" works fine, it formats the content. But I cannot get it to work via VisualStudio Code

[mickeypearce]

Hej @iarsov,

• Formatter is registered for plsql language files. Try installing PL/SQL support. I use extension Language PL/SQL.

If it still wouldn't work , try adding:

"files.associations": {
        "*.sql": "plsql"
    }

to vscode settings. But this is not needed in my case.

• If you want automatically "format on save" you must also add: "editor.formatOnSave": "true" to vscode setting otherwise you have to use "Format" commands.

[iarsov]

I installed it , now when I click save I see in bottom left "Formatting ..." actions, but nothing happens. I have

"files.associations": {
        "*.sql": "plsql"
    },
    "oracle-format.sqlcl": "/Users/iarsov/Workspace/sqlcl/bin/sql",

I even set "oracle-format.sqlcl" to some invalid value to see if an error is reported, but still nothing. It's like the extension is ignored, but if I disable the extension then I don't see the "Formatting ..." in bottom left corner.

Strange... I need to test on Windows, maybe it's something with VS and OSX.

[mickeypearce]

Is there anything useful in Developer tools console ? You should see the output there. (it's in Help menu).

[iarsov]

Looks like it reports something invalid, e.g. like invalid number of parameters are passed to SQLcl.

SQLcl: Release 18.3 Production on Thu Nov 08 10:14:32 2018

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

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.

I tried manually, with (echo format file a.sql a.sql) | sql /nolog and it worked fine. In Developer console I don't see the the command executed, just output. Can it be because it's 18.3, but then why would manual execution work if it's the same command that gets executed from VS Code?

[mickeypearce]

Actually it executes : (echo format file ${file} ${file}) | "${sqlPath}" /nolog (echo format file a.sql a.sql) | "sql" /nolog from your example.

1. "sql" in quotes ". I added them because of posibility of spaces in path.
2. but there are not parenthesis in the file path (a.sql in your example) as I haven't thought about that. If you have a file path with spaces... Could this be a problem?

Thanks for your help.

[mickeypearce]

I should print command string that is executed to console output for cases like this.

[iarsov]

hmm, I think it does not set ${file} correctly. That's my guess.

(echo format file ${file} ${file}) | sql /nolog

SQLcl: Release 18.3 Production on Thu Nov 08 11:14:00 2018

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

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.

If I set it explicitly, it's fine.

export file=a.sql
(echo format file ${file} ${file}) | sql /nolog

SQLcl: Release 18.3 Production on Thu Nov 08 11:13:15 2018

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

[iarsov]

Another thing, you might want to add "Language PL/SQL" as prerequisites or at least as a Note, because it does not work without that extension, at least for me.

[mickeypearce]

Looks like a problem with ${file} I agree.

I published an update (0.0.5) that prints sqlcl command to output. Would you mind trying again and posting results from DevTools Console as I cannot reproduce the bug on my machine.

[iarsov]

OK, now I see where the problem is.

On OSX the changes (prior-save) are on a temp file which is in some directory with spaces.

The quotes " you put does not help because they're not stripped. When echo is processed it prints the path without quotes and the empty spaces in the path breaks the SQLcl.

What you need to generate is (echo format file \"${file}\" \"${file}\") | "${sqlPath}" /nolog

Example:

(echo format file "/some path/a.sql" "/some path/a.sql")
format file /some path/a.sql /some path/a.sql

(echo format file \"/some path/a.sql\" \"/some path/a.sql\")
format file "/some path/a.sql" "/some path/a.sql"

This should do it.

[iarsov]

For better readability I would put whole echo string in quotes as (echo "format file \"${file}\" \"${file}\"") | "${sqlPath}" /nolog

[mickeypearce]

I properly escaped the quotes with \ as you suggested. Hopefully it will work now. :pray:

It didn't work If I put the whole echo string in quotes.. @iarsov , thanks again.

[iarsov]

The extension still generates "${file}" instead of \"${file}\". And when that is processed in terminal it prints without quotes. Not sure how you generate the string, but try to add ${file} in quotes like this "\"${file}\"". The whole point is in the console from VS Code Developer Tools you should see (echo format file \"${file}\" \"${file}\") | "${sqlPath}" /nolog so that when that gets executed in terminal it will print the file path in quotes.

Extensions are written in JavaScript?

[mickeypearce]

On windows is correctly executed with quotes (visible in console). If I add "\"${file}\"" is executed with double quotation and fails. What kind of shell are you using on OSX?

The extension is writen in typescript: extension.ts.

[mickeypearce]

Are you sure you tried with the latest version - 0.0.6 :thinking:

[iarsov]

Yes, I use latest version, 0.0.6.

The problem is that typescript strips the \ character. If you use double strip \ , E.g. \\"${file}\\" then it will be printed as \"${file}\". If that makes problems on Windows, you will have to somehow generate the string based on OS, I guess.

[mickeypearce]

I published a new version (0.0.7) that uses an intermediate script for formattting instead of piping commands from cmd to sqlcl. I think it should work for your case as well. :pray:

Logging is printed to Output channel named "oracle-format".

[iarsov]

It says "WARN Aborted format on save after 750ms" in Developer console. No other information is provided. It's stuck on "Formatting..."

[mickeypearce]

I made a note in readme.md file, but maybe I should catch the error and make the user see it... (If that is even possible).

When using with "formatOnSave" I suggest increasing default timeout as formatting takes quite some time for larger files:

"editor.formatOnSaveTimeout": 10000

The output is now printed to Output channel.

[mickeypearce]

Have you tried it maybe? Thanks for your feedback.