Syntax check error when formatting json_table with integer

Toad for Oracle - Beta
1

Hi!
I'm getting a syntax check error when formatting this query:

select jt.*
  from dual l, json_table('[{"rn":1},{"rn":2}]', '$[*]' columns(rn  integer path '$.rn')) jt;

[Error] Syntax check (2: 69): ERROR: line 2, column 69, ending line 2, column 75: Found 'integer': Expecting: )  ,   -or-   EXISTS  FORMAT  PATH   -or-   BLOB  CLOB  DATE  FOR  identifier  NUMBER  SDO_GEOMETRY  substitution_variable  TIMESTAMP  VARCHAR  VARCHAR2
[Error] Syntax check (2: 69): ERROR: line 2, column 69, ending line 2, column 75: Found 'integer': A reserved word cannot be used as an identifier

And this is seams to be correct according to the documentation:

20.2 COLUMNS Clause of SQL/JSON Function JSON_TABLE
A regular column specification consists of a column name followed by an optional scalar data type for the column, which can be SQL data type VARCHAR2 , NUMBER , DATE , TIMESTAMP , TIMESTAMP WITH TIME ZONE , or SDO_GEOMETRY (the same as for the RETURNING clause of json_value ), followed by an optional value clause and an optional PATH clause. The default data type is VARCHAR2(4000) .

Anyway the query executes without error, but we must make sure that the json field contains only integers or we may get a very different result, as in this example:

select jt.*
  from dual l, json_table('[{"rn":1.6},{"rn":2.3}]', '$[*]' columns(rn_int integer path '$.rn', rn_num number path '$.rn')) jt;

image

So i think this is not toad problem at all.
Thanks!

2

Hello Tenorio,

You write "seems to be correct according to the documentation" but neither in your doc snippet nor in the Oracle 23c JSON_TABLE I could find a mention to INTEGER as a choice for the column data type. I know that INTEGER is converted to NUMBER but I had hoped Oracle to document it more explicitly.

I could confirm that your query executes without error, so for now I'll consider the use of INTEGER as an undocumented feature, and I already amended the parser to accept it. (QP-4135, will show up in component QP5.dll version 5.405)

Thanks for reporting it!
Andre