Oracle Execute immediate中JSON路径表达式绑定变量语法错误问题

1. JSON路径语法不支持绑定变量占位：Oracle的JSON_VALUE函数的路径参数要求是静态字符串字面量，你把绑定变量:pos写在路径字符串内部时，Oracle解析JSON路径语法时只会把:pos当做路径字符串的固定内容，而不会识别为需要替换的绑定变量，不符合JSON路径的语法规范，直接触发语法错误。
2. 动态SQL写法错误：EXECUTE IMMEDIATE执行SELECT查询时，直接用INTO子句接收返回结果即可，不需要写RETURNING INTO（该子句仅用于INSERT/UPDATE/DELETE这类DML语句返回结果的场景）。
3. 变量作用域问题：动态SQL执行上下文无法直接识别外层PL/SQL定义的buffer变量，需要通过绑定变量传入。

以下提供两种常用的可运行方案：

方案1：动态拼接JSON路径（适合简单循环遍历场景） #

直接把下标变量的值拼接到动态SQL的路径字符串里，不需要用绑定变量传下标，同时把buffer作为绑定变量传入动态SQL即可。由于pos是数字类型，拼接不会产生SQL注入风险，完全安全。

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

方案2：用JSON_TABLE一次性提取全部数组元素（推荐，性能更好） #

如果需要遍历整个数组，不需要循环执行多次查询，直接用JSON_TABLE把数组所有元素转成行格式，一次性读取即可。这种方式只需要执行一次SQL解析，比循环执行多次动态SQL性能高很多，代码也更简洁。

declare 
  buffer clob;
begin
  buffer := '{"root":[{"theData":[224.08755140452405,124.08755140452405,324.08755140452405]}]}';
  
  -- 遍历数组所有元素
  for rec in (
    select val 
    from json_table(
      buffer,
      '$.root[0].theData[*]' 
      columns val number path '$'
    )
  ) loop
    dbms_output.put_line(rec.val);
  end loop;
end;
/

内容的提问来源于stack exchange，提问作者Eugene Mcgoldrick