CODEWITHSUNDEEP
oracle-databasejson-value
Eugene Mcgoldrick
Eugene Mcgoldrick

Reputation: 1

Oracle Execute immediate JSON path expression syntax error

I want to loop through a Json string to retrieve values. The following code gives me a JSON path expression error.

declare 
buffer clob;
  pos number;
  Numb number; 
begin
  pos := 1;
  buffer := '{"root":[{"theData":[224.08755140452405,124.08755140452405,324.08755140452405]}]}';
  pos := 0; 
  
  EXECUTE IMMEDIATE 'select json_value(buffer,''$.root[0].theData[:pos]'') from dual'
  using pos
  returning INTO numb;
  dbms_output.put_line(numb);
  
end;

If I replace the execute immediate command with the following statement

select json_value(buffer,'$.root[0].theData[0]') into numb from dual;

it works fine. Any ideas?

Upvotes: 0

Views: 999

Answers (2)

MT0
MT0

Reputation: 168026

Rather than using SQL, you can use PL/SQL Object types for JSON:

declare 
  buffer clob := '{"root":[{"theData":[224.08755140452405,124.08755140452405,324.08755140452405]}]}';
  pos    PLS_INTEGER := 0;
  Numb   number;
  json_arr JSON_ARRAY_T;
begin
  json_arr := TREAT(
                JSON_OBJECT_T( buffer ).get_array('root').get(0)
                AS JSON_OBJECT_T
              ).get_array('theData');

  FOR pos IN 0 .. json_arr.get_size() - 1 LOOP
    numb := json_arr.get_number(pos);
    dbms_output.put_line(numb);
  END LOOP;
end;
/

Which outputs:

224.08755140452405
124.08755140452405
324.08755140452405

db<>fiddle here

Upvotes: 2

kfinity
kfinity

Reputation: 9091

If you check out the docs, they mention that the JSON path expression has to be a string literal - so it can't include a bind variable or substitution variable.

But since you're running it with execute immediate, you can make it appear to be a literal when the string is executed as a query. I think you want something like this:

EXECUTE IMMEDIATE 'select json_value(:buffer,''$.root[0].theData[' || pos || ']'') from dual'
into numb
using buffer;

Edit: also corrected the syntax

Upvotes: 2

Related Questions