如何通过ORDS REST API批量加载JSON数据至Oracle？解决存储过程绑定变量BODY_TEXT错误

看起来你遇到的核心问题是存储过程中引用了未声明的绑定变量:body_text，同时你的JSON是数组格式，当前的JSON_TABLE路径也没法正确解析多条记录。咱们一步步来修正：

问题分析 #

• 绑定变量未声明：存储过程的参数列表里没有定义body_text，PL/SQL编译器找不到这个变量，所以抛出PLS-00049错误。
• JSON数组解析错误：你的输入是JSON数组（[]包裹），但当前JSON_TABLE用的路径是'$'，只会解析整个数组对象，而不是逐个元素。需要改成'$[*]'来遍历数组里的每个对象。
• 数据类型不匹配：你把hiredate、sal等字段定义为VARCHAR2，但目标表emp里这些字段是DATE、NUMBER类型，需要做类型转换，避免插入报错。
• 事务处理不当：异常块里的COMMIT应该移到正常执行的逻辑里，异常时不应该提交，还要确保错误信息能正确返回。

修正后的存储过程代码 #

create or replace PROCEDURE create_employee (p_body_text IN CLOB) 
AS
BEGIN
  INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
  SELECT 
    empno,
    ename,
    job,
    mgr,
    TO_DATE(hiredate, 'DD-MON-YYYY') AS hiredate, -- 转换为DATE类型匹配表字段
    sal,
    comm,
    deptno
  FROM JSON_TABLE(
    p_body_text, 
    '$[*]' COLUMNS ( -- 用$[*]遍历JSON数组中的每个元素
      empno NUMBER PATH '$.empno',
      ename VARCHAR2(50) PATH '$.ename',
      job VARCHAR2(50) PATH '$.job', -- 修正字段名，与JSON中的key保持一致
      mgr NUMBER PATH '$.mgr',
      hiredate VARCHAR2(50) PATH '$.hiredate',
      sal NUMBER PATH '$.sal',
      comm NUMBER PATH '$.comm',
      deptno NUMBER PATH '$.deptno'
    )
  );
  
  COMMIT; -- 成功插入后提交事务
EXCEPTION
  WHEN OTHERS THEN
    HTP.print('Error: ' || SQLERRM);
    ROLLBACK; -- 异常时回滚事务，保证数据一致性
END;
/

关键修正点说明 #

1. 添加输入参数：新增p_body_text参数（类型用CLOB支持大型JSON文件），替代原来未声明的:body_text绑定变量。
2. 修正JSON解析路径：用'$[*]'遍历数组内的每个对象，实现批量插入所有员工记录。
3. 字段名与类型匹配：把之前错误的ejob修正为job（和JSON中的key一致），同时将数值、日期字段做类型转换，匹配目标表的字段类型。
4. 优化事务逻辑：正常插入完成后提交，异常触发时回滚，避免脏数据。

ORDS处理器配置提示 #

在配置ORDS REST处理器时需要注意：

• 选择POST请求方法
• 将请求体内容绑定到存储过程的p_body_text参数
• 设置请求的Content-Type为application/json

这样你就可以提交大型JSON数组文件，通过这个存储过程批量插入到emp表中了。

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