[Solved] Issues with JSON_TABLE  

   RSS

0

Hello, Philipp.

I've been trial "Trivadis PL/SQL Cop Version 2.3.0.20190929.113939" and stuck on

  E-0002: Syntax error. Please contact the author if the code compiles successfully in your environment.

in case of:

BEGIN
FOR rec IN (
SELECT
why
FROM
JSON_TABLE ( (
SELECT
'{ "why":"why?" }' doc
FROM
dual
), '$[*]'
COLUMNS (
why PATH '$.why'
)
)
) LOOP
NULL;
END LOOP;
END;

--NOSONAR switch doesn't workaround also. 

WBR,

Vitaly

 

Topic Tags
5 Answers
1

Thank you for these examples. 

I've opened a ticket for the first example to make the json_query_return_type optional. In the most recent Oracle documenation it is optional. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html#GUID-3C8E63B5-0B94-4E86-A2D3-3D4831B67C62 . Hence, we need to support that anyway.

Regarding the second example. Yes, our parser is a bit picky when using keywords. See also "Use of Keywords" in https://www.salvis.com/blog/faq/what-are-the-limitations/  

0

Hi Vitaly,

The json_query_return_type is missing in your code. Hence the code is wrong, will not run and throws also an error when doing the code analysis. The following code should work:

BEGIN
FOR rec IN (
SELECT why
FROM
JSON_TABLE ((
SELECT '{ "why":"why?" }' doc
FROM dual
), '$[*]'
COLUMNS (
why varchar2(100) PATH '$.why'
)
)
) LOOP
NULL;
END LOOP;
END;
/

Regards,

Philipp

0

Nice shoot, Philipp. You are right in sense of publicly available syntax for JSON_TABLE function: https://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56973
, but reality is more complicated.

create view v1
as
SELECT why
FROM
JSON_TABLE ((
SELECT '{ "why":"'|| banner ||'" }' doc
FROM V$VERSION
WHERE banner LIKE 'PL/SQL%'
), '$[*]'
COLUMNS (
why PATH '$.why'
)
);

desc v1;

Name Null? Type
---- ----- --------------
WHY VARCHAR2(4000)

select * from v1 ;

WHY
-------------------------------------------------
PL/SQL Release 12.1.0.2.0 - Production

Anyway, thanks for solution and have a nice day.

Vitaly

0

much more interesting sample:

with json as
( select '{"bla_bla_bla":"Why we have ten E-0002 issues now?"}' doc
from dual
)
SELECT *
FROM json_table( (select * from json) , '$[*]'
COLUMNS ( c1 varchar2(100) PATH '$.bla_bla_bla'
)
);

Issue Overview

# % Severity Characteristics Message
10 100.0% Blocker Reliability
E-0002: Syntax error. Please contact the author if the code compiles successfully in your environment.

Issues

Issue# Line Severity Message Code Excerpt
1 1 Blocker E-0002: Syntax error. Please contact the author if the code compiles successfully in your environment. no viable alternative at input 'json'
2 2 Blocker E-0002: Syntax error. Please contact the author if the code compiles successfully in your environment. no viable alternative at input '('
3 5 Blocker E-0002: Syntax error. Please contact the author if the code compiles successfully in your environment. no viable alternative at input 'SELECT'
4 6 Blocker E-0002: Syntax error. Please contact the author if the code compiles successfully in your environment. no viable alternative at input '('
5 6 Blocker E-0002: Syntax error. Please contact the author if the code compiles successfully in your environment. missing ',' at 'select'
6 6 Blocker E-0002: Syntax error. Please contact the author if the code compiles successfully in your environment. mismatched input '*' expecting 'columns'
7 6 Blocker E-0002: Syntax error. Please contact the author if the code compiles successfully in your environment. no viable alternative at input ''$[*]''
8 7 Blocker E-0002: Syntax error. Please contact the author if the code compiles successfully in your environment. no viable alternative at input '('
9 7 Blocker E-0002: Syntax error. Please contact the author if the code compiles successfully in your environment. no viable alternative at input '100'
10 7 Blocker E-0002: Syntax error. Please contact the author if the code compiles successfully in your environment. no viable alternative at input 'PATH'
0

Aha, indeed objects with name "json" broke the COP's haven.

with ajson as
( select '{"bla_bla_bla":"Why we have ten E-0002 issues now?"}' doc
from dual
)
SELECT *
FROM json_table( (select * from ajson) , '$[*]'
COLUMNS ( c1 varchar2(100) PATH '$.bla_bla_bla'
)
);

Issue Overview

# % Severity Characteristics Message
3 75.0% Minor Changeability
G-1050: Avoid using literals in your code.
1 25.0% Minor Reliability
G-2340: Always define your VARCHAR2 variables using CHAR SEMANTIC (if not defined anchored).

Issues

Issue# Line Severity Message Code Excerpt
1 2 Minor G-1050: Avoid using literals in your code. '{"bla_bla_bla":"Why we have ten E-0002 issues now?"}'
2 6 Minor G-1050: Avoid using literals in your code. '$[*]'
3 7 Minor G-1050: Avoid using literals in your code. '$.bla_bla_bla'
4 7 Minor G-2340: Always define your VARCHAR2 variables using CHAR SEMANTIC (if not defined anchored). varchar2(100)
This post was modified 3 weeks ago by Vitaly

Please Login or Register