Oracle Apex 5.0 and APEX_JSON

How many lines of code does it take to make a web service call? Answer: 39

That is how many lines of PL/SQL I had to write in Oracle Apex 5.0 to make a web service call to an external API.

I used Adzuna's REST API to retrieve the latitude and longitude and the price of 2 bed properties for rent in a specific location in UK. The API returns JSON which the APEX_JSON package is able to parse easily. Adzuna is a property search engine which also provides aggregate data for properties in various countries around the world.

I think the native APEX_JSON package in Oracle Apex 5.0 is very useful and makes integrating web services to your Oracle Apex applications very easy. Here is application I have created in matter of hours which shows you average rent properties in a location of your choice in UK.

Here is the link for the app: http://enciva-uk15.com/ords/f?p=322:1

And here is the code:

If you want to run this as is in your SQL Workshop, make sure you replace {myadzunaid:myadzunakey} in the code with your adzuna app_id and app_key which you can obtain from the Adzuna website https://developer.adzuna.com/ as I have removed mine from the code. They also have a very good interactive api documentation here http://api.adzuna.com/static/swagger-ui/index.html#!/adzuna

create or replace procedure get_rent_data(p_where in varchar2, p_radius in number, p_room in number)
is
v_resp_r clob;
j apex_json.t_values;
l_paths apex_t_varchar2;
v_id varchar(50);
v_lat decimal(9,6);
v_lon decimal(9,6);
v_rent number(10);

begin
-- http housekeeping
apex_web_service.g_request_headers(1).name  := 'Accept'; 
apex_web_service.g_request_headers(1).value := 'application/json; charset=utf-8'; 
apex_web_service.g_request_headers(2).name  := 'Content-Type'; 
apex_web_service.g_request_headers(2).value := 'application/json; charset=utf-8';

v_resp_r := apex_web_service.make_rest_request 
      ( p_url => 'http://api.adzuna.com:80/v1/api/property/gb/search/1' 
      , p_http_method => 'GET' 
      , p_parm_name => apex_util.string_to_table('app_id:app_key:where:max_days_old:sort_by:category:distance:results_per_page:beds') 
      , p_parm_value => apex_util.string_to_table('{myadzunaid:myadzunakey}:'||p_where||':90:date:to-rent:'||p_radius||':100:'||p_room||'') 
      );
-- parse json
apex_json.parse(j, v_resp_r);


-- start looping on json
l_paths := apex_json.find_paths_like (
        p_values         => j,
        p_return_path => 'results[%]',
        p_subpath       => '.beds',
        p_value           => '2' );
        
for i in 1 .. l_paths.count loop
       v_id := apex_json.get_varchar2(p_values => j, p_path => l_paths(i)||'.id'); 
       v_rent := apex_json.get_varchar2(p_values => j, p_path => l_paths(i)||'.price_per_month'); 
       v_lat := apex_json.get_varchar2(p_values => j, p_path => l_paths(i)||'.latitude');
       v_lon := apex_json.get_varchar2(p_values => j, p_path => l_paths(i)||'.longitude');

-- debug print values to page
 htp.p(v_id||'-'||v_lat||','||v_lon||'Rents : £'||v_rent);

end loop;

END;