oracle ords parse

1CONN testuser1/testuser1@pdb1
2
3CREATE OR REPLACE PROCEDURE create_departments (p_data  IN  BLOB)
4AS
5  TYPE t_dept_tab IS TABLE OF dept%ROWTYPE;
6  TYPE t_emp_tab  IS TABLE OF emp%ROWTYPE;
7
8  l_dept_tab     t_dept_tab := t_dept_tab();
9  l_emp_tab      t_emp_tab  := t_emp_tab();
10
11  l_clob         CLOB;
12  l_dest_offset  PLS_INTEGER := 1;
13  l_src_offset   PLS_INTEGER := 1;
14  l_lang_context PLS_INTEGER := DBMS_LOB.default_lang_ctx;
15  l_warning      PLS_INTEGER;
16
17  l_dept_count   PLS_INTEGER;
18  l_emp_count    PLS_INTEGER;
19BEGIN
20
21  -- Convert the BLOB to a CLOB.
22  DBMS_LOB.createtemporary(
23    lob_loc => l_clob,
24    cache   => FALSE,
25    dur     => DBMS_LOB.call);
26
27  DBMS_LOB.converttoclob(
28   dest_lob      => l_clob,
29   src_blob      => p_data,
30   amount        => DBMS_LOB.lobmaxsize,
31   dest_offset   => l_dest_offset,
32   src_offset    => l_src_offset, 
33   blob_csid     => DBMS_LOB.default_csid,
34   lang_context  => l_lang_context,
35   warning       => l_warning);
36   
37  APEX_JSON.parse(l_clob);
38
39  -- Loop through all the departments.
40  l_dept_count := APEX_JSON.get_count(p_path => 'departments');
41  FOR i IN 1 .. l_dept_count LOOP
42    l_dept_tab.extend;
43    l_dept_tab(l_dept_tab.last).deptno := APEX_JSON.get_number(p_path => 'departments[%d].department.department_no', p0 => i);
44    l_dept_tab(l_dept_tab.last).dname  := APEX_JSON.get_varchar2(p_path => 'departments[%d].department.department_name', p0 => i);
45    l_emp_count   := APEX_JSON.get_count(p_path => 'departments[%d].department.employees', p0 => i);
46
47    -- Loop through all the employees for the current department.
48    FOR j IN 1 .. l_emp_count LOOP
49      l_emp_tab.extend;
50      l_emp_tab(l_emp_tab.last).deptno   := l_dept_tab(l_dept_tab.last).deptno;
51      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);
52      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);
53      l_emp_tab(l_emp_tab.last).sal      := APEX_JSON.get_number(p_path => 'departments[%d].department.employees[%d].salary', p0 => i, p1 => j);
54      l_emp_tab(l_emp_tab.last).hiredate := SYSDATE;
55    END LOOP;
56  END LOOP;
57
58  -- Populate the tables.
59  FORALL i IN l_dept_tab.first .. l_dept_tab.last
60    INSERT INTO dept VALUES l_dept_tab(i);
61
62  FORALL i IN l_emp_tab.first .. l_emp_tab.last
63    INSERT INTO emp VALUES l_emp_tab(i);
64
65  COMMIT;
66
67  DBMS_LOB.freetemporary(lob_loc => l_clob);
68EXCEPTION
69  WHEN OTHERS THEN
70    HTP.print(SQLERRM);
71END;
72/