Syntax check error when formatting json_table with integer

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;


So i think this is not toad problem at all.

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!