Oracle ORDS parse

CONN testuser1/testuser1@pdb1

CREATE OR REPLACE PROCEDURE create_departments (p_data  IN  BLOB)
AS
  TYPE t_dept_tab IS TABLE OF dept%ROWTYPE;
  TYPE t_emp_tab  IS TABLE OF emp%ROWTYPE;

  l_dept_tab     t_dept_tab := t_dept_tab();
  l_emp_tab      t_emp_tab  := t_emp_tab();

  l_clob         CLOB;
  l_dest_offset  PLS_INTEGER := 1;
  l_src_offset   PLS_INTEGER := 1;
  l_lang_context PLS_INTEGER := DBMS_LOB.default_lang_ctx;
  l_warning      PLS_INTEGER;

  l_dept_count   PLS_INTEGER;
  l_emp_count    PLS_INTEGER;
BEGIN

  -- Convert the BLOB to a CLOB.
  DBMS_LOB.createtemporary(
    lob_loc => l_clob,
    cache   => FALSE,
    dur     => DBMS_LOB.call);

  DBMS_LOB.converttoclob(
   dest_lob      => l_clob,
   src_blob      => p_data,
   amount        => DBMS_LOB.lobmaxsize,
   dest_offset   => l_dest_offset,
   src_offset    => l_src_offset, 
   blob_csid     => DBMS_LOB.default_csid,
   lang_context  => l_lang_context,
   warning       => l_warning);
   
  APEX_JSON.parse(l_clob);

  -- Loop through all the departments.
  l_dept_count := APEX_JSON.get_count(p_path => 'departments');
  FOR i IN 1 .. l_dept_count LOOP
    l_dept_tab.extend;
    l_dept_tab(l_dept_tab.last).deptno := APEX_JSON.get_number(p_path => 'departments[%d].department.department_no', p0 => i);
    l_dept_tab(l_dept_tab.last).dname  := APEX_JSON.get_varchar2(p_path => 'departments[%d].department.department_name', p0 => i);
    l_emp_count   := APEX_JSON.get_count(p_path => 'departments[%d].department.employees', p0 => i);

    -- Loop through all the employees for the current department.
    FOR j IN 1 .. l_emp_count LOOP
      l_emp_tab.extend;
      l_emp_tab(l_emp_tab.last).deptno   := l_dept_tab(l_dept_tab.last).deptno;
      l_emp_tab(l_emp_tab.last).empno    := APEX_JSON.get_number(p_path => 'departments[%d].department.employees[%d].employee_number', p0 => i, p1 => j);
      l_emp_tab(l_emp_tab.last).ename    := APEX_JSON.get_varchar2(p_path => 'departments[%d].department.employees[%d].employee_name', p0 => i, p1 => j);
      l_emp_tab(l_emp_tab.last).sal      := APEX_JSON.get_number(p_path => 'departments[%d].department.employees[%d].salary', p0 => i, p1 => j);
      l_emp_tab(l_emp_tab.last).hiredate := SYSDATE;
    END LOOP;
  END LOOP;

  -- Populate the tables.
  FORALL i IN l_dept_tab.first .. l_dept_tab.last
    INSERT INTO dept VALUES l_dept_tab(i);

  FORALL i IN l_emp_tab.first .. l_emp_tab.last
    INSERT INTO emp VALUES l_emp_tab(i);

  COMMIT;

  DBMS_LOB.freetemporary(lob_loc => l_clob);
EXCEPTION
  WHEN OTHERS THEN
    HTP.print(SQLERRM);
END;
/