Oracle Execute immediate JSON path expression syntax error

Question

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?

What is buffer supposed to be in the dynamic version? Did you mean to bind that and pass the real value via using too? (Presumably you do actually have a reason to do this dynamically...?) — Alex Poole
– Alex Poole, Commented Sep 1, 2021 at 19:19

kfinity · Accepted Answer · 2021-09-01 19:41:18Z

2

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

edited Sep 1, 2021 at 19:41

answered Sep 1, 2021 at 19:36

kfinity

9,1411 gold badge15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MT0 · Accepted Answer · 2021-09-01 22:43:54Z

2

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

edited Sep 1, 2021 at 22:43

answered Sep 1, 2021 at 22:32

MT0

173k12 gold badges70 silver badges136 bronze badges

Collectives™ on Stack Overflow

Oracle Execute immediate JSON path expression syntax error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related