Oracle 23ai: ORA-40490 COLUMNS clause required

Problem:

While working with Oracle’s native JSON data type, I ran into a bit of a hiccup while trying to insert JSON data into a table:

SQL> insert into json_table(data) 
values(
      json_object(
        'name' value 'Mari',
        'age' value 34,
        'city' value 'Tbilisi'));

I get the following error:

ERROR at line 1:
ORA-40490: COLUMNS clause required

And similarly, for creating a JSON Search Index (this is the syntax that is provided by Oracle documentation by now)

CREATE SEARCH INDEX mk_search_idx ON json_table(data) FOR JSON;

It returns the same error:

ERROR at line 1:
ORA-40490: COLUMNS clause required

At this point, I figured the issue had to be about the “COLUMNS clause”, which the error clearly mentions – but where exactly is it supposed to go?

Solution:

Turns out, Oracle wants you to explicitly declare the COLUMNS clause right after the table name. Here’s the syntax that finally worked for me:

SQL> insert into json_table columns(data)
values(
          json_object(
              'name' value 'Salome',
              'age' value 12,
              'city' value 'Tbilisi'));
1 row created.

SQL> commit;

And similarly, for creating a JSON Search Index:

SQL> CREATE SEARCH INDEX mk_search_idx ON json_table columns(data) FOR JSON;

Index created.