# Syntax check error when formatting json\_table with integer

**URL:** <https://forums.toadworld.com/t/syntax-check-error-when-formatting-json-table-with-integer/58680>\
**Category:** Toad for Oracle - Beta\
**Created:** [December 15, 2023, 9:31am UTC](https://forums.toadworld.com/t/syntax-check-error-when-formatting-json-table-with-integer/58680 "2023-12-15T09:31:54Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![tenorio](https://avatars.discourse-cdn.com/v4/letter/t/ee59a6/32.png) [@tenorio](https://forums.toadworld.com/u/tenorio)\
**Post date:** [December 15, 2023, 9:31am UTC](https://forums.toadworld.com/t/syntax-check-error-when-formatting-json-table-with-integer/58680/1 "2023-12-15T09:31:54Z")

</div>

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

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

```

```auto
[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:

```auto
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](https://global.discourse-cdn.com/quest/original/3X/2/4/244232d2c5a1d4ade619aff06f3c7417bd529925.png)

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

---

<div class="post-metadata">

**Author:** ![avergison](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/avergison/32/66_2.png) [@avergison](https://forums.toadworld.com/u/avergison)\
**Post date:** [December 15, 2023, 7:27pm UTC](https://forums.toadworld.com/t/syntax-check-error-when-formatting-json-table-with-integer/58680/2 "2023-12-15T19:27:53Z")

</div>

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](https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/JSON_TABLE.html#GUID-3C8E63B5-0B94-4E86-A2D3-3D4831B67C62) 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