Trivadis
Parse failing on JSON_TABLE without columns wrapped in parentheses
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
}
]
}
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.
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
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.
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.