Parse json data using APEX_JSON in Oracle PL SQL

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s