Parse failing on JSON_TABLE without columns wrapped in parentheses

[OlafurTr]

Was running into strange parse issues running the cli at one of my clients. Strange "No viable alternative".
After a few hours of debugging (character set issues, etc...), I managed to reproduce the error with a minimal example.

create or replace package body test as 
    procedure test1(json in varchar2) is
    begin
        for i in (select * from json_table(json, '$' columns c1 number path '.id', c2 varchar2(200) path '.name'))  loop
          sys.dbms_output.put_line( i.c1 || ' ' || i.c2);
        end loop;
    end test1;
end test;

This will parse and and run on Oracle 19c even though its missing the parentheses after columns (XMLTYPE syntax)

{
  "issues": [
    {
      "engineId": "db* CODECOP",
      "ruleId": "E-0002",
      "severity": "BLOCKER",
      "type": "CODE_SMELL",
      "primaryLocation": {
        "message": "E-0002: Syntax error. Please check the limitations and contact the author if the code can be compiled successfully in your environment.",
        "filePath": "code/test.sql",
        "textRange": {
          "startLine": 3,
          "endLine": 3,
          "startColumn": 20,
          "endColumn": 24
        }
      },
      "effortMinutes": 13
    },
    {
      "engineId": "db* CODECOP",
      "ruleId": "E-0002",
      "severity": "BLOCKER",
      "type": "CODE_SMELL",
      "primaryLocation": {
        "message": "E-0002: Syntax error. Please check the limitations and contact the author if the code can be compiled successfully in your environment.",
        "filePath": "code/test.sql",
        "textRange": {
          "startLine": 3,
          "endLine": 3,
          "startColumn": 25,
          "endColumn": 27
        }
      },
      "effortMinutes": 13
    },
    {
      "engineId": "db* CODECOP",
      "ruleId": "E-0002",
      "severity": "BLOCKER",
      "type": "CODE_SMELL",
      "primaryLocation": {
        "message": "E-0002: Syntax error. Please check the limitations and contact the author if the code can be compiled successfully in your environment.",
        "filePath": "code/test.sql",
        "textRange": {
          "startLine": 3,
          "endLine": 3,
          "startColumn": 28,
          "endColumn": 36
        }
      },
      "effortMinutes": 13
    },
    {
      "engineId": "db* CODECOP",
      "ruleId": "E-0002",
      "severity": "BLOCKER",
      "type": "CODE_SMELL",
      "primaryLocation": {
        "message": "E-0002: Syntax error. Please check the limitations and contact the author if the code can be compiled successfully in your environment.",
        "filePath": "code/test.sql",
        "textRange": {
          "startLine": 3,
          "endLine": 3,
          "startColumn": 38,
          "endColumn": 40
        }
      },
      "effortMinutes": 13
    },
    {
      "engineId": "db* CODECOP",
      "ruleId": "E-0002",
      "severity": "BLOCKER",
      "type": "CODE_SMELL",
      "primaryLocation": {
        "message": "E-0002: Syntax error. Please check the limitations and contact the author if the code can be compiled successfully in your environment.",
        "filePath": "code/test.sql",
        "textRange": {
          "startLine": 5,
          "endLine": 5,
          "startColumn": 17,
          "endColumn": 18
        }
      },
      "effortMinutes": 13
    },
    {
      "engineId": "db* CODECOP",
      "ruleId": "E-0002",
      "severity": "BLOCKER",
      "type": "CODE_SMELL",
      "primaryLocation": {
        "message": "E-0002: Syntax error. Please check the limitations and contact the author if the code can be compiled successfully in your environment.",
        "filePath": "code/test.sql",
        "textRange": {
          "startLine": 5,
          "endLine": 5,
          "startColumn": 61,
          "endColumn": 63
        }
      },
      "effortMinutes": 13
    },
    {
      "engineId": "db* CODECOP",
      "ruleId": "E-0002",
      "severity": "BLOCKER",
      "type": "CODE_SMELL",
      "primaryLocation": {
        "message": "E-0002: Syntax error. Please check the limitations and contact the author if the code can be compiled successfully in your environment.",
        "filePath": "code/test.sql",
        "textRange": {
          "startLine": 6,
          "endLine": 6,
          "startColumn": 10,
          "endColumn": 13
        }
      },
      "effortMinutes": 13
    },
    {
      "engineId": "db* CODECOP",
      "ruleId": "E-0002",
      "severity": "BLOCKER",
      "type": "CODE_SMELL",
      "primaryLocation": {
        "message": "E-0002: Syntax error. Please check the limitations and contact the author if the code can be compiled successfully in your environment.",
        "filePath": "code/test.sql",
        "textRange": {
          "startLine": 11,
          "endLine": 11,
          "startColumn": 0,
          "endColumn": 3
        }
      },
      "effortMinutes": 13
    }
  ]
}

[PhilippSalvisberg]

Thanks @OlafurTr for reporting this issue.

The following code runs in Oracle Database 23ai (23.4)

create or replace procedure test1(json in varchar2) is
begin
   for i in (select * from json_table(json, '$[*]' columns c1 number path '$.id', c2 varchar2(200) path '$.name'))  loop
      sys.dbms_output.put_line( i.c1 || ' ' || i.c2);
   end loop;
end test1;
/

set serveroutput on size unlimited
exec test1('[{"id": 1, "name": "Foo"}, {"id": 2, "name": "Bar"}]');

and produces this result:

Procedure TEST1 compiled

1 Foo
2 Bar


PL/SQL procedure successfully completed.

The code is valid. However, the JSON_columns_clause requires parentheses according to the documentation.

We were not aware of this syntax variant. It should be possible to support that. However, using unquoted keywords as identifiers is a documented restriction. See here.

So, the following code can be parsed successfully (with quoted identifier "json" instead of json and parentheses in the JSON_columns_clause):

create or replace procedure test1("json" in varchar2) is
begin
   for i in (select * from json_table("json", '$[*]' columns (c1 number path '$.id', c2 varchar2(200) path '$.name')))  loop
      sys.dbms_output.put_line( i.c1 || ' ' || i.c2);
   end loop;
end test1;
/

That's the workaround for the current version.

[OlafurTr]

Ok, I can't change the code at the clients. So I will wait for a fix. But the example was a quick check to see if my hunch was right about using the XMLTABLE syntax instead of the newer JSON_TABLE syntax.

Ran this on 23ai and had the same errors stopping the check.

declare
  l_json varchar2(32767) := '[{"id":1, "name":"Test"},{"id":2, "name":"codecop cli"}]';
begin
  begin
    for i in (select * from json_table(l_json, '$[*]' columns c1 number path '$.id', c2 varchar2(200) path '$.name')) loop
      sys.dbms_output.put_line(i.c1 || ' ' || i.c2);
    end loop;
  end test1;
end;

This runs fine on 23ai

[PhilippSalvisberg]

Yes, parse errors are expected without parentheses. It's an undocumented syntax variant after all.

Thanks for letting us know. It's very much appreciated.

[OlafurTr]

No problem, it was exactly this, that I noticed. The xmltable syntax in json_table that made me check if this was the culprit. I had no idea it was valid before seeing it there.