Oracle PL/SQL无法从JSON对象取数组赋值表对象类型报错求助

错误信息 #

ORA-06550: line 14, column 12:
PL/SQL: ORA-00904: "L_EMP_ARR": invalid identifier
ORA-06550: line 7, column 3:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

用户代码 #

自定义类型声明 #

CREATE OR REPLACE TYPE XX_OUTPUT_OBJ 
AS
OBJECT(
    EMP_ID      NUMBER,
    NAME        VARCHAR2(50),
    SALARY      NUMBER
);

CREATE OR REPLACE TYPE XX_INPUT_TYPE
IS TABLE OF XX_OUTPUT_OBJ;

PL/SQL执行块 #

DECLARE
  input_type    XX_INPUT_TYPE;
  l_obj JSON_OBJECT_T := JSON_OBJECT_T('{"supplier":"name","items":[{"empid":109, "name":"raj","salary":111},{"empid":110, "name":"raj1","salary":222}] }');
  l_emp_arr     JSON_ARRAY_T;
BEGIN
    l_emp_arr := l_obj.get_array('items');
  SELECT XX_OUTPUT_OBJ(
           emp_id,
           name,
           salary
         )
  BULK COLLECT INTO input_type
  FROM   JSON_TABLE(
           l_emp_arr.stringify,
           '$[*]'
           COLUMNS (
             emp_id NUMBER       PATH '$.empid',
             name   VARCHAR2(50) PATH '$.name',
             salary NUMBER       PATH '$.salary'
           )
         );

  FOR i IN 1 .. input_type.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(
      input_type(i).emp_id || ', '
      || input_type(i).name || ','
      || input_type(i).salary
    );
  END LOOP;
END;

错误原因 #

SQL语句中的JSON_TABLE无法直接引用PL/SQL变量l_emp_arr——SQL引擎与PL/SQL引擎相互独立，必须通过绑定变量传递PL/SQL变量到SQL上下文，或者直接在JSON_TABLE中使用原始JSON对象的路径，跳过中间变量传递环节。

解决方案 #

方案1：直接引用原始JSON对象的路径 #

无需提前提取JSON_ARRAY_T，直接在JSON_TABLE中指定数组路径'$.items[*]'：

DECLARE
  input_type    XX_INPUT_TYPE;
  l_obj JSON_OBJECT_T := JSON_OBJECT_T('{"supplier":"name","items":[{"empid":109, "name":"raj","salary":111},{"empid":110, "name":"raj1","salary":222}] }');
BEGIN
  SELECT XX_OUTPUT_OBJ(
           emp_id,
           name,
           salary
         )
  BULK COLLECT INTO input_type
  FROM   JSON_TABLE(
           l_obj.stringify,
           '$.items[*]'
           COLUMNS (
             emp_id NUMBER       PATH '$.empid',
             name   VARCHAR2(50) PATH '$.name',
             salary NUMBER       PATH '$.salary'
           )
         );

  FOR i IN 1 .. input_type.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(
      input_type(i).emp_id || ', '
      || input_type(i).name || ','
      || input_type(i).salary
    );
  END LOOP;
END;
/

方案2：使用绑定变量传递PL/SQL变量 #

先将l_emp_arr转为字符串变量，再通过绑定变量:l_arr_str传递给JSON_TABLE：

DECLARE
  input_type    XX_INPUT_TYPE;
  l_obj JSON_OBJECT_T := JSON_OBJECT_T('{"supplier":"name","items":[{"empid":109, "name":"raj","salary":111},{"empid":110, "name":"raj1","salary":222}] }');
  l_emp_arr     JSON_ARRAY_T;
  l_arr_str     VARCHAR2(4000);
BEGIN
    l_emp_arr := l_obj.get_array('items');
    l_arr_str := l_emp_arr.stringify;

  SELECT XX_OUTPUT_OBJ(
           emp_id,
           name,
           salary
         )
  BULK COLLECT INTO input_type
  FROM   JSON_TABLE(
           :l_arr_str,
           '$[*]'
           COLUMNS (
             emp_id NUMBER       PATH '$.empid',
             name   VARCHAR2(50) PATH '$.name',
             salary NUMBER       PATH '$.salary'
           )
         );

  FOR i IN 1 .. input_type.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(
      input_type(i).emp_id || ', '
      || input_type(i).name || ','
      || input_type(i).salary
    );
  END LOOP;
END;
/

说明 #

方案1更简洁，减少中间变量；方案2保留了先提取JSON数组的逻辑，适合需要对数组进行额外预处理的场景。

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