Introduction
This blog provides an example of loading data from Json file into Table
Procedures
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;
/
Summary:
This Process helps the user to extract the data from JSON file and load into respective tables.
