[prettier-plugin-sql] PL-SQL code from Oracle Apex causing error

[FerreiraM12]

I have valid PL-SQL code and I am getting an error when trying to format it.

This is the code I used:

BEGIN
    IF :APEX$ROW_STATUS = 'C' THEN
        -- Call the CreateProfessor procedure to insert the new professor
        CreateProfessor(
            p_first_name       => :FIRST_NAME,
            p_last_name        => :LAST_NAME,
            p_user_name        => :USER_NAME,
            p_email            => :EMAIL,
            p_department_id    => :DEPARTMENT_ID,
            p_salary           => :SALARY,
            p_work_hours       => :WORK_HOURS,
            p_title            => :TITLE
        );

    ELSIF :APEX$ROW_STATUS = 'U' THEN
        -- Call the UpdateProfessor procedure to update the professor
        UpdateProfessor(
            p_user_id          => :USER_ID,
            p_first_name       => :FIRST_NAME,
            p_last_name        => :LAST_NAME,
            p_user_name        => :USER_NAME,
            p_email            => :EMAIL,
            p_department_id    => :DEPARTMENT_ID,
            p_salary           => :SALARY,
            p_work_hours       => :WORK_HOURS,
            p_title            => :TITLE
        );

    ELSIF :APEX$ROW_STATUS = 'D' THEN
        -- Call the DeleteProfessor procedure to delete the professor
        DeleteProfessor(p_user_id => :USER_ID);
    END IF;
END;

I am getting the following error:

npx prettier --write teste.sql
teste.sql
[error] teste.sql: Error: Parse error: Unexpected "$ROW_STATU" at line 2 column 13
[error]     at TokenizerEngine.createParseError (file:///Users/marlonferreira/Documentos%20Local/AeC/E-learning/node_modules/sql-formatter/lib/lexer/TokenizerEngine.js:53:12)
[error]     at TokenizerEngine.tokenize (file:///Users/marlonferreira/Documentos%20Local/AeC/E-learning/node_modules/sql-formatter/lib/lexer/TokenizerEngine.js:35:22)
[error]     at Tokenizer.tokenize (file:///Users/marlonferreira/Documentos%20Local/AeC/E-learning/node_modules/sql-formatter/lib/lexer/Tokenizer.js:16:47)
[error]     at LexerAdapter.tokenize (file:///Users/marlonferreira/Documentos%20Local/AeC/E-learning/node_modules/sql-formatter/lib/parser/createParser.js:16:76)
[error]     at LexerAdapter.reset (file:///Users/marlonferreira/Documentos%20Local/AeC/E-learning/node_modules/sql-formatter/lib/parser/LexerAdapter.js:17:24)
[error]     at Parser.feed (/Users/marlonferreira/Documentos Local/AeC/E-learning/node_modules/nearley/lib/nearley.js:281:15)
[error]     at Object.parse (file:///Users/marlonferreira/Documentos%20Local/AeC/E-learning/node_modules/sql-formatter/lib/parser/createParser.js:26:18)
[error]     at Formatter.parse (file:///Users/marlonferreira/Documentos%20Local/AeC/E-learning/node_modules/sql-formatter/lib/formatter/Formatter.js:32:49)
[error]     at Formatter.format (file:///Users/marlonferreira/Documentos%20Local/AeC/E-learning/node_modules/sql-formatter/lib/formatter/Formatter.js:25:22)
[error]     at formatDialect (file:///Users/marlonferreira/Documentos%20Local/AeC/E-learning/node_modules/sql-formatter/lib/sqlFormatter.js:77:57)

If I replace all occurrences of APEX$ROW_STATUS with some other word the formatting works as expected.

[JounQin]

This should be reported to https://github.com/sql-formatter-org/sql-formatter instead.

cc @nene

[JounQin]

https://github.com/un-ts/prettier/blob/a8a0ff495b4984928e69f317616196fc21808f2f/packages/sql/package.json#L48

And it seems we are outdated now, sql-formatter v13 has been released, PR welcome to upgrade it and test your failing case.

[nene]

This seems like a bug in SQL Formatter. However I'd like to see a reference for this syntax in Oracle documentation. For example this page, describing the syntax of placeholders, does not mention $ as a valid character.

At the moment one can pick the PostgreSQL dialect, which supports $ characters, to see how the output from SQL Formatter would look like for this SQL. I can say up front that it doesn't look pretty. That's because SQL Formatter doesn't really support formatting of procedural SQL. It does its best to not crash, but that's pretty much all. There's no plan to support it either as the current architecture of SQL Formatter is not fit for the task. That said, I've been working on a new SQL formatting approach in the form of a Prettier plugin which addresses lots of the shortcomings of SQL Formatter including proper support of procedural SQL. It's still in the early stages of development though, only supporting SQLite and BigQuery.

[JounQin]

I'll add a new formatter option cst to portal https://github.com/nene/prettier-plugin-sql-cst inside.

[nene]

I'll add a new formatter option cst to portal nene/prettier-plugin-sql-cst inside.

Hmm... wouldn't it be kinda strange to package one Prettier plugin inside another one? Just saying... I don't mind. It'll probably bring more exposure to the -cst plugin.

[JounQin]

wouldn't it be kinda strange to package one Prettier plugin inside another one?

I rethink about it and it's really weird, so I'll not try it for now.

[JounQin]

The following setting will also do the trick.

{
  "paramTypes": "{custom:[{regex:':\\w+(\\$\\w+)?'}]}"
}

[karlhorky]

Hmm... wouldn't it be kinda strange to package one Prettier plugin inside another one? Just saying... I don't mind. It'll probably bring more exposure to the -cst plugin.

I rethink about it and it's really weird, so I'll not try it for now.

Yeah it does seem a bit strange to call a different Prettier plugin from within a plugin, but would not be so strange if the prettier-plugin-sql-cst project changed to a more generic formatting library (not sure if that's wanted though):

• https://github.com/nene/prettier-plugin-sql-cst/pull/11#issuecomment-1837466328

And then if this idea would be acceptable to everyone, prettier-plugin-sql could maintain the Prettier-specific parts.

[nene]

Well, the prettier-plugin-sql-cst is actually more tightly coupled to Prettier than this plugin. It uses the Prettier layout engine to perform the formatting. It can't really be separated from Prettier.