Oracle PLSQL unable to fetch the json array from json object and assign to the table object type

Question

I'm trying to get the json array from json object and assign to table object type in Oracle PLSQL but the execution is failing with below error, please help me

Error report -
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:

My code is as below

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;

Type and object declaration

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;

Answer 1

You can do it without the PL/SQL JSON types:

DECLARE
  input_type  XX_INPUT_TYPE;
  l_json      CLOB := '{"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_json,
           '$.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;
/

or, if you did want to use the PL/SQL JSON types:

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_items    JSON_ARRAY_T  := l_obj.GET_ARRAY('items');
  l_item     JSON_OBJECT_T;
BEGIN
  FOR i IN 0 .. l_items.GET_SIZE - 1 LOOP
    l_item := TREAT(l_items.GET(i) AS JSON_OBJECT_T);
    DBMS_OUTPUT.PUT_LINE(
      l_item.get_number('empid') || ', '
      || l_item.get_string('name') || ', '
      || l_item.get_number('salary')
    );
  END LOOP;
END;
/

Which both output:

109, raj, 111
110, raj1, 222

fiddle