For Example :
1) Sample Json:-
{
“department”: {
“department_number”: 10,
“department_name”:
“ACCOUNTING”,
“ACCOUNTING”,
“employees”: [
{
“employee_number”: 7782,
“employee_name”:
“CLARK”
“CLARK”
},
{
“employee_number”: 7839,
“employee_name”:
“KING”
“KING”
},
{
“employee_number”: 7934,
“employee_name”:
“MILLER”
“MILLER”
}
]
}
}
2) Above File Stored in the below table and
this column “order_document”
this column “order_document”
CREATE TABLE
json_order (ID NUMBER NOT NULL,
json_order (ID NUMBER NOT NULL,
order_document CLOB
)
/
3) Above File data load to below table.
create table
Json_emp (E_no number,
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.
insertion on Json_emp Table.
DECLARE
l_json_text
CLOB;
CLOB;
l_count
PLS_INTEGER;
PLS_INTEGER;
l_members
wwv_flow_t_varchar2;
wwv_flow_t_varchar2;
l_paths
apex_t_varchar2;
apex_t_varchar2;
l_exists
BOOLEAN;
BOOLEAN;
BEGIN
SELECT order_document
INTO
l_json_text
l_json_text
FROM
json_order;
json_order;
apex_json.parse
(l_json_text);
(l_json_text);
l_count :=
apex_json.get_count (p_path => ‘department.employees’);
apex_json.get_count (p_path => ‘department.employees’);
FOR
i IN 1 .. l_count
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;
LOOP;
COMMIT;
END;
/
Recommended Posts
