Load JSON File to Oracle table

For Example :
1)  Sample Json:-
{
  “department”: {
    “department_number”: 10,
    “department_name”:
“ACCOUNTING”,
    “employees”: [
      {
        “employee_number”: 7782,
        “employee_name”:
“CLARK”
      },
      {
        “employee_number”: 7839,
        “employee_name”:
“KING”
      },
      {
        “employee_number”: 7934,
        “employee_name”:
“MILLER”
      }
    ]
  }
}
2)  Above File Stored in the below table and
this column “order_document”
CREATE TABLE
json_order (ID NUMBER NOT NULL,
                         order_document CLOB
                        )
/
3)  Above File data load to below table.
create table
Json_emp (E_no      number,
     E_name varchar2(100),
     D_no      number,
     d_name varchar2(100)
)
/
4)  Below coding for above file to data
insertion on Json_emp Table.
DECLARE
   l_json_text  
CLOB;
   l_count      
PLS_INTEGER;
   l_members    
wwv_flow_t_varchar2
;
   l_paths      
apex_t_varchar2
;
   l_exists     
BOOLEAN;
BEGIN
   SELECT order_document
     INTO
l_json_text
     FROM
json_order
;
   apex_json.parse
(l_json_text);
   l_count :=
apex_json
.get_count (p_path => ‘department.employees’);
   FOR
i
IN 1 .. l_count
   LOOP
      INSERT INTO json_emp
                  (e_no,
                   e_name,
                   d_no,
                   d_name
                  )
           VALUES (apex_json.get_number
                        (p_path      => ‘department.employees[%d].employee_number’,
                         p0          => i
                        ),
                   apex_json.get_varchar2
                          (p_path      => ‘department.employees[%d].employee_name’,
                           p0          => i
                          ),
                   apex_json.get_number
                                     (p_path      => ‘department.department_number’),
                   apex_json.get_varchar2
                                       (p_path      => ‘department.department_name’)
                  );
   END
LOOP;
   COMMIT;
END;
/

  • July 4, 2018 | 14 views
  • Comments