Monday 8 January 2018

Apex - Load Data from Json File to Table

     This blog provides an example of loading data from Json file into Table    
     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"
      }
    ]
  }

}

This file is stored in the below table in the column “order_document”

CREATE TABLE json_order (ID NUMBER NOT NULL,
                         order_document CLOB
                        )
Data loaded to table:

create table Json_emp (E_no      number,
     E_name varchar2(100),
     D_no      number,
     d_name varchar2(100)
 )

Sample code:
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;
/

3 comments: