Important Announcement

This forum will be discontinued.

The creation of new topics is disabled.

Please open GitHub issues for the corresponding topics/products in the following Trivadis repositories:

A lot of user have already a GitHub account and the management of the issues is better than in this forum. For example, closing a forum topic is never related to a product change. Closing a GitHub issue for an accepted bug, means that the bug is fixed. This should simplify the work for all involved parties.

See this blog post for more information.

Thank you for your understanding.

Notifications
Clear all

[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 7 months ago by Vitaly