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;

/
  • January 8, 2018 | 21 views