Command failed on "format document"

[Danieleeee]

Command failed: (echo format file c:\Sviluppi\SVILDIS\src\PACKAGE_BODIES\CARICA_CURVE.sql c:\Sviluppi\SVILDIS\src\PACKAGE_BODIES\CARICA_CURVE.sql) | "sql" /nolog ""sql"" it is not recognized as an internal or external command, an executable program or a batch file.

[mickeypearce]

Try adding "oracle-format.sqlcl": "/absolute/path/to/sql.exe"to settings.json with path to sqlcl exec.

SQLcl is not part of this extension and should be downloaded separately or as part of SQL Developer.

[mickeypearce]

@Danieleeee Any luck?

[Danieleeee]

Thanks, now it works. I used this formatter.xml file, but nothing happens when I format document.

format.txt

[mickeypearce]

I had a problem with 18.2 version of Sql developer. Then I downgraded to 17.3 and it worked. I would also suggest using sqlcl that comes with sql developer that you use for creating formatting rules.

[mickeypearce]

I tried your format.txt with SQLcl: Release 17.3.0 and it works.

[stuartisaac]

It sure would be nice for this to work with current versions of SQLcl. I played around with it a bit, and it seems like sqlcl needs an intermediate script file to be launched to run the format file command. These intermediate file contents worked for me when I ran sqlcl from the command line.

format file '&1' '&2' quit

The command line arguments:

/path/to/sql /nolog @/path/to/formatter.sql "/path/to/file.sql" "/path/to/file.sql"

How one puts that handling into a vscode extension is beyond my current rudimentary understanding of vscode extensions.

[mickeypearce]

Have you tried formatting with default formatter rules? (without "rules" setting) #3

But yes, I could make the intermediate file that includes loading formatting rules. Have you maybe tried that?

[stuartisaac]

No, I have not tried anything with formatting rules. I was just trying to get it working with the defaults.

[mickeypearce]

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

Try maybe with a simple PL/SQL file as it doesn't format invalid syntax.

[stuartisaac]

I was using a trivial and valid SQL query. It didn't work using VS code extension, and it also didn't work when executing the sql command directly from the command line, as seemingly the newer versions of sqlcl require that intermediate file to execute the format file command.

[mickeypearce]

Could you please post your OS, sqlcl version and command line. That would be really useful.

[stuartisaac]

Whaaa....? It seems to be working now, and I have no idea what I'm doing differently. It seems to work only when the file has been saved (which makes sense when you understand how it's implemented), but I was sure I'd tested with a saved file before. I think it would be helpful for the README to note that. (On the other hand, if the extension could (for unsaved files, anyway) copy the contents to a temporary file, do the operation on the temp file, then copy it back to the actual file, that would be pretty slick!)

At any rate, I guess this is working for me now. ¯_(ツ)_/¯ Thanks for making this available.

[mickeypearce]

Great idea! This solution for unsaved files would also work for "format selection" that is currently not possible.

[stuartisaac]

Heh. I don't think I would use Format Selection much anyway, as it would seem like the formatting (particularly indentation level) is very dependent on the context, and ignoring the context would result in weird formatting. For example, if you have several nested PL/SQL blocks and you format lines within the innermost block, SQL Developer will push them to the left margin, which seems very unhelpful to me. (At least, that's how it behaves for me.)

Anyway, if somebody would find it useful, cool, I guess, but I personally wouldn't bend over backwards making it happen.

[mickeypearce]

Cool, I will make it work. Would you mind opening another task for this request as this one was opened for a different reason. :pray: Thanks.