Creating a Dynamic JSON_TABLE Query with a Variable in PL/SQL

Question

I have a PL/SQL procedure that contains the following static SQL code, which works as expected:

SELECT LISTAGG(value, ',') WITHIN GROUP (ORDER BY NULL) 
INTO SELECTED_GEO 
FROM JSON_TABLE(v_json_clob, '$.GEO.GEO20[*]' COLUMNS value PATH '$');

Now, I'm trying to make the code dynamic by replacing the fixed value '20' with a variable P_X. The variable SELECTED_GEO is an OUT parameter that will be read by a web application. Here's what I've tried:

EXECUTE IMMEDIATE 'SELECT LISTAGG(value, '','') WITHIN GROUP (ORDER BY NULL) INTO SELECTED_GEO 
FROM JSON_TABLE(' || v_json_clob || ', ''$.GEO.GEO' || P_X || '[*]'' COLUMNS value PATH ''$'')';

When I print the dynamically generated SQL, it appears to be correct, and it matches the static version. However, when I execute the procedure, the dynamic SQL version doesn't work as expected.

I've verified that the values of v_json_clob and P_GEO_TYPE are correct at runtime. Despite this, the dynamic version fails to retrieve the expected data, while the static version works perfectly.

I get this error:

ORA-00936: missing expression

Am I missing something in my approach to dynamic SQL in this scenario? Any insights into why the dynamic version might not be functioning as intended would be greatly appreciated.

Answer 1

Use a bind variables to pass the value into and out of the dynamic query:

DECLARE
  v_json_clob  CLOB         := '{"GEO":{"GEO20": "abc"}}';
  p_x          VARCHAR2(20) := '20';
  selected_geo VARCHAR2(20);
BEGIN
  EXECUTE IMMEDIATE q'{SELECT LISTAGG(value, ',') WITHIN GROUP (ORDER BY NULL) FROM JSON_TABLE(:1, '$.GEO.GEO}' || P_X || q'{[*]' COLUMNS value PATH '$')}'
    INTO  SELECTED_GEO
    USING v_json_clob;
  DBMS_OUTPUT.PUT_LINE(SELECTED_GEO);
END;
/

Which outputs:

abc

fiddle