Can execute immediate be used with JSON_TABLE

Question

I need to convert JSON into data table (key value columns) in Oracle 12c v12.1.0.2

So for example there is a JSON string like

{"ID": 10, "Description": "TestJSON", "status":"New"}

I need this converted to :

Column1          Column2
------------------------------------
ID                  10
Description         TestJSON
status              New

Now my JSON string could change the number of attributes and hence I require to keep the conversion dynamic.

I tried using execute immediate :

set serveroutput on;
declare
sqlsmt VARCHAR2(200);
t3 varchar2(50);
begin
sqlsmt := 'SELECT * '||
'FROM  json_table( ( select jsonstr from mytable where ID= 10) , ''$[*]'' '||
                'COLUMNS (  :t1 PATH ''$.''|| '':t2'' ))';
execute immediate sqlsmt into t3 using 'desc' , '$.Description' ;
DBMS_OUTPUT.PUT_LINE( 'Output Variable: ' || t3);
END;

However, I get the following error:

ORA-00904: : invalid identifier
ORA-06512: at line 8
00904. 00000 -  "%s: invalid identifier"

Please help. I have Oracle 12c V1. But I really need to pull columns dynamically from JSON.

Answer 1

There are a couple of things that can help with dynamic SQL (assuming you really need to use it). The first is to use dbms_output to show the generated statement before you try to execute it; so in your case:

...
dbms_output.put_line(sqlsmt);
execute immediate sqlsmt into t3;
--using 'descr' , '$.Description' ;
DBMS_OUTPUT.PUT_LINE( 'Output Variable: ' || t3);
END;
/

with your code that shows:

SELECT * FROM  json_table( ( select jsonstr from mytable where ID= 10) , '$[*]' COLUMNS (  :t1 PATH '$.'|| ':t2' ))

The most obvious issue there is in '$.'|| ':t2', where :t2 shouldn't be in quotes; that isn't causing the error but would stop it being bound to your variable as you expect as it's a literal value. You also have the $. part in that bit and in your variable value, but again it isn't getting that far.

In common with all dynamic SQL, you can only supply values for variables in the using clause. You're trying to pass the column name as a bind variable, which isn't allowed; so it's trying to use :t1 as the output column name, not desc; and :t1 isn't a valid name. (Neither is desc as that's a reserved word - but either gets the same error.) So, you have to concatenate the column name in rather than binding it.

It looks like you would be able to use :t2 for the path though; but you you can't do that either, not as a dynamic SQL restriction but as a SQL/JSON one - if you got that far, with a valid variable value, you'd still get "ORA-40454: path expression not a literal". You have to concatenate the path into the statement too.

Finally the $[*] doesn't allow you to match the Description... which leads to the second hint about dynamic SQL; get a static query working properly first, then make that dynamic.

So putting that together, you could do:

declare
  sqlsmt varchar2(200);
  t1 varchar2(30) := 'descr';
  t2 varchar2(30) := 'Description';
  t3 varchar2(50);
begin
  sqlsmt := 'SELECT * '||
    'FROM  json_table( ( select jsonstr from mytable where ID= 10) , ''$'' '||
    'COLUMNS ( ' || t1 || ' PATH ''$.' || t2 || '''))';
  dbms_output.put_line(sqlsmt);
  execute immediate sqlsmt into t3;
  dbms_output.put_line( 'Output Variable: ' || t3);
end;
/

which with your example data outputs:

SELECT * FROM  json_table( ( select jsonstr from mytable where ID= 10) , '$' COLUMNS ( descr PATH '$.Description'))
Output Variable: TestJSON

It's a bit odd that the only thing you are allowed to pass as a variable, the 10, is hard-coded. But I get this is an experiment.

You could also write the statement as:

select j.*
from mytable t
cross join json_table ( t.jsonstr, '$' columns ( descr path '$.Description' )) j
where t.id = 10;

which you could do dynamically as:

declare
  sqlsmt varchar2(200);
  id number := 10;
  t1 varchar2(30) := 'descr';
  t2 varchar2(30) := 'Description';
  t3 varchar2(50);
begin
  sqlsmt := 'select j.*'
    || ' from mytable t'
    || q'^ cross join json_table ( t.jsonstr, '$' columns ( ^'
    || t1
    || q'^ path '$.^'
    || t2
    || q'^' )) j^'
    || ' where t.id = :id';
  dbms_output.put_line(sqlsmt);
  execute immediate sqlsmt into t3 using id;
  dbms_output.put_line( 'Output Variable: ' || t3);
end;
/

I've used the alternative quoting mechanism to avoid having to double-up the quotes within the statement, but that's optional. With the same data that outputs:

select j.* from mytable t cross join json_table ( t.jsonstr, '$' columns ( descr path '$.Description' )) j where t.id = :id
Output Variable: TestJSON

db<>fiddle