Oracle PL/SQL遍历JSON数组插入数据遇ORA-40573错误求助

错误原因 #

• 在SQL的INSERT语句中直接调用PL/SQL JSON对象（JSON_OBJECT_T）的成员方法（如get_number、get_string），Oracle不允许在SQL执行上下文里直接调用PL/SQL对象的方法，这会触发ORA-40573错误。
• 额外问题：代码中插入的表名是EMPLOYEE_DETAIL_TBL1，但实际创建的表是EMPLOYEE_DETAIL_TBL，表名不匹配会导致后续表不存在的错误。

解决方案 #

先将JSON对象中的字段值提取到PL/SQL本地变量中，再使用这些变量执行INSERT操作，避免在SQL语句中直接调用PL/SQL对象方法。同时修正表名匹配问题。

修改后的PL/SQL代码 #

DECLARE
  l_obj         JSON_OBJECT_T;
  l_emp_arr     JSON_ARRAY_T;
  l_emp_obj     JSON_OBJECT_T;
  -- 新增本地变量存储提取的字段值
  v_emp_id      NUMBER;
  v_name        VARCHAR2(50);
  v_salary      NUMBER;
  
  PROCEDURE display (
            p_obj       IN      JSON_OBJECT_T,
            p_resp  OUT     JSON_OBJECT_T) IS
  BEGIN
    l_emp_arr := p_obj.get_array('items');
    FOR i IN 0 .. l_emp_arr.get_size - 1 LOOP
      DBMS_OUTPUT.put_line('-------------------------------------------');
      l_emp_obj := TREAT(l_emp_arr.get(i) AS JSON_OBJECT_T);
      
      -- 先将JSON字段值提取到本地变量
      v_emp_id := l_emp_obj.get_number('empid');
      v_name := l_emp_obj.get_string('name');
      v_salary := l_emp_obj.get_number('salary');
      
      -- 使用本地变量执行INSERT，修正表名
      INSERT INTO EMPLOYEE_DETAIL_TBL (EMP_ID, NAME, SALARY) 
      VALUES (v_emp_id, v_name, v_salary);
      
      DBMS_OUTPUT.put_line('index           : ' || i);
      DBMS_OUTPUT.put_line('employee name   : ' || v_name);
      DBMS_OUTPUT.put_line('employee salary : ' || v_salary);
    END LOOP;
    
    p_resp := p_obj;
  END;
BEGIN
  display(JSON_OBJECT_T('{"supplier":"name","items":[{"empid":109, "name":"raj","salary":111},{"empid":110, "name":"raj1","salary":222}] }'), l_obj);
END;

验证说明 #

• 本地变量的作用域在PL/SQL块内，提取JSON值后再传入SQL语句，符合Oracle的执行上下文规则，不会触发ORA-40573错误。
• 修正了表名不匹配的问题，确保插入操作指向正确的表。

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