oracle PLSQL insert json object values by iterating over the array

Question

I'm trying to insert the json data i.e. by iterating over the records. but at the time insert it is throwing "ORA-40573: Invalid use of PL/SQL JSON object type." Please help

DECLARE
  l_obj         JSON_OBJECT_T;
  l_emp_arr     JSON_ARRAY_T;
  l_emp_obj     JSON_OBJECT_T;
  PROCEDURE display (
            p_obj       IN      JSON_OBJECT_T,
            p_resp  OUT     JSON_OBJECT_T) IS
  BEGIN
    --DBMS_OUTPUT.put_line(p_obj.stringify);
    
    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);
    
    INSERT INTO EMPLOYEE_DETAIL_TBL1 (EMP_ID, NAME, SALARY) VALUES (l_emp_obj.get_number('empid'), l_emp_obj.get_string('name'), l_emp_obj.get_number('salary'));
    DBMS_OUTPUT.put_line('index           : ' || i);
    DBMS_OUTPUT.put_line('department_name : ' || l_emp_obj.get_string('name'));
    DBMS_OUTPUT.put_line('department_no   : ' || l_emp_obj.get_number('salary'));
  END LOOP;
    
    p_resp := p_obj;
  END;
BEGIN
  -- Create a new object.
  display(JSON_OBJECT_T('{"supplier":"name","items":[{"empid":109, "name":"raj","salary":111},{"empid":110, "name":"raj1","salary":222}] }'), l_obj);
  --DBMS_OUTPUT.put_line(l_obj.get_string('supplier'));
END;

below is the create table

CREATE TABLE
        EMPLOYEE_DETAIL_TBL
        (
                EMP_ID NUMBER NOT NULL PRIMARY KEY,
                NAME   VARCHAR2(50)               ,
                SALARY NUMBER
        )

Answer 1

You do not need to use PL/SQL or objects or loops and, instead, can do it in a single INSERT statement using JSON_TABLE:

INSERT INTO employee_detail_tbl (emp_id, name, salary)
SELECT empid, name, salary
FROM   JSON_TABLE(
         '{"supplier":"name","items":[{"empid":109, "name":"raj","salary":111},{"empid":110, "name":"raj1","salary":222}] }',
         '$.items[*]'
         COLUMNS(
           empid  NUMBER       PATH '$.empid',
           name   VARCHAR2(50) PATH '$.name',
           salary NUMBER       PATH '$.salary'
         )
       );

Which, after the INSERT, the table contains:

EMP_ID	NAME	SALARY
109	raj	111
110	raj1	222

fiddle