How to parse JSON data in oracle apex ?
Yes, We can parse json data with the help of apex_json and apex_json.t_values.
[
{
“ID”: “13”,
“EMAIL”: “thamaraiapex@gmail.com”,
“AVATAR”: “www.gmail.com/profile2”
},
{
“AVATAR”: “www.gmail.com/profile1”,
“EMAIL”: “thamaraiselva1054@gmail.com”,
“ID”: “14”
},
{
“AVATAR”: “www.gmail.com/profile3”,
“ID”: “12”,
“EMAIL”: “abc@gmail.com”
}
]
- User apex_web_service.make_rest_request
- Authorization method is Bearer Token
- Store the response in CLOB variable
- Parse the response json content with apex_json.t_values
- Count no of rows in json content
- Create for loop and get the object values using apex_json.get_varchar2
- Insert / Update object value variables to table.
DECLARE
v_url VARCHAR2(1000);
v_clob CLOB;
v_json_content VARCHAR2(4000);
v_access_token VARCHAR2(500);
v_id VARCHAR2(500);
v_avatar VARCHAR2(4000);
v_email VARCHAR2(500);
v_member_id VARCHAR2(200);
row_count NUMBER;
json_content APEX_JSON.t_values;
BEGIN
v_url := ‘https://api.abc.com/organization/members’; /* Example URL */
apex_web_service.g_request_headers.DELETE();
apex_web_service.g_request_headers(1).name := ‘Authorization’;
apex_web_service.g_request_headers(1).value := ‘Bearer ‘ ||v_access_token;
v_clob := apex_web_service.make_rest_request(p_url => v_url,
p_http_method => ‘GET’
);
APEX_JSON.parse(json_content, v_clob);
row_count := APEX_JSON.get_count(p_path => ‘.’ ,
p_values => json_content
);
IF row_count > 0 THEN
FOR i in 1 .. row_count LOOP
v_member_id := APEX_JSON.get_varchar2(p_path => ‘[‘|| i ||’].id’, p_values => json_content);
v_avatar := APEX_JSON.get_varchar2(p_path => ‘[‘|| i ||’].avatar’, p_values => json_content);
v_email := APEX_JSON.get_varchar2(p_path => ‘[‘|| i ||’].email’, p_values => json_content);
/* Here you can Insert or Update into your table */
END LOOP;
END IF;
That’s it you parses the json content and store the values in a table.
