Introduction
This Post illustrates steps required to Load JSON File to Oracle table in Oracle EBS R12.
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 the 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;/
Summary
This Post Described Load JSON File to Oracle table in Oracle EBS R12.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
