Customize PLSQL dialect to support bind variables with array notation

[raffian]

We're using the plsql formatter for parsing Pro*C SQL. It's proprietary to Oracle but it's basically PL/SQL with some minor C-based spiciness sprinkled on top. The plsql formatter handles 99% of our needs except for statements containing array-based bind variables:

SELECT col1 from table1 where col1 = :param[i];


Error: Parse error: Unexpected "[i];" at line 1 column 44.
SQL dialect used: "plsql".
    at T.default.createParseError (https://unpkg.com/sql-formatter@latest/dist/sql-formatter.min.js:1:233841)
    at T.default.tokenize (https://unpkg.com/sql-formatter@latest/dist/sql-formatter.min.js:1:233607)
    at T.default.tokenize (https://unpkg.com/sql-formatter@latest/dist/sql-formatter.min.js:1:228909)

The same error is not raised, however, using TSQL formatter. After comparing the two formatters we believe identTypes parameter in <dialect>.formatter.ts might be the cause as plsql does not have '[]' like tsql does .

# plsql
identTypes: [`""-qq`],

# tsql
identTypes: [`""-qq`, '[]'],

We have not yet tested this solution - are we at least on the right path?

[nene]

This might work, but what you'd be saying to the formatter to treat [...] syntax as identifiers, which is kinda odd. It will also likely result in space between :param and [i].

I think a better way would be to use the paramTypes config to define a custom rule to recognize parameters like that. Something like:

paramTypes: {
  custom: [{ regex: ':\\w+\\[\\w+\\]' }],
}

Or alternatively you could supply the extraParens option to recognize [,] parenthesis pairs:

extraParens: ['[]'],

[raffian]

I will try these, thank you!

[raffian]

@nene

Your suggestion to use extraParens: ['[]'] worked perfectly - thank you!

BTW, had a minor issue building/running the project on nodejs 22.15.0

sql-formatter/bin$   npm run build 
sql-formatter/bin$   echo "SQL HERE" | ./sql-formatter-cli.cjs -l plsql 

Dist/cjs/package.json

# '{"type": "commonjs"}'

node:internal/modules/package_json_reader:145
const result = modulesBinding.getNearestParentPackageJSON(checkPath);
^
Error: Invalid package config \\?\sql-formatter\dist\cjs\package.json.
at Object.getNearestParentPackageJSON (node:internal/modules/package_json_reader:145:33)
at Object..js (node:internal/modules/cjs/loader:1883:29)
at Module.load (node:internal/modules/cjs/loader:1465:32)
at Function.\_load (node:internal/modules/cjs/loader:1282:12)
at TracingChannel.traceSync (node:diagnostics_channel:322:14)
at wrapModuleLoad (node:internal/modules/cjs/loader:235:24)
at Module.require (node:internal/modules/cjs/loader:1487:12)
at require (node:internal/modules/helpers:135:16)
at Object.<anonymous> (\sql-formatter\bin\sql-formatter-cli.cjs:5:39)
at Module.\_compile (node:internal/modules/cjs/loader:1730:14) {
code: 'ERR_INVALID_PACKAGE_CONFIG'
}

After each build, dist/cjs/package.json looked like this:
'{"type": "commonjs"}'

...so based on the error we removed the enclosing '', saved, ran again and it worked - weird.

Thank you again for your assistance!

[nene]

I guess these extra quotes resulted from this build script in package.json:

    "build:cjs": "tsc -p tsconfig.cjs.json && echo '{\"type\": \"commonjs\"}' > dist/cjs/package.json",

But I don't know why that would happen. For me it works just fine. I tested with sh, bash, zsh, fish. Works the same in all.

[raffian]

Probably environment-related on my side.