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;
/