[Solved] Issues with JSON_TABLE

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/  

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

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

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

Issues

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

Issues