Consuming RESTful services in Oracle APEX

Let's see how we can consume REST webservices in Oracle APEX. Before we proceed, the assumption is that you are aware of REST concepts or you can refer my blogpost that explains basics of REST architecture - Check it here

We are going to use an example of Bing maps REST API where we will pass various parameters such as from and to addresses and will try to derive and consume all possible mileage values returned by REST API.

First thing first. Let’s understand structure of Bing Maps REST services.

The base URL Structure is as follows –

https://dev.virtualearth.net/REST/version/restApi/resourcePath?queryParameters&key=BingMapsKey

Here's a sample REST call using this API –

https://dev.virtualearth.net/REST/V1/Routes/Driving?&waypoint.0=9410 Webb Chapel Rd, Dallas, TX 75220&waypoint.1=5959 Royal Ln, Dallas, TX 75230&maxSolutions=2&distanceUnit=mi&key=h2d5aewr6_i8768a-9ftw54js7-876asdfasf

Now let’s see how we can consume the REST webservice in Oracle APEX -

1. Make the webservice call using APEX_WEB_SERVICE.make_rest_request API

lc_clob := APEX_WEB_SERVICE.make_rest_request (
            p_url => 'http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml'||'&'||'wp.0=4122%20Avondale%20Ave,%20Dallas,%20TX%2075219'||'&'||'wp.1=3837%20Caruth%20Blvd,%20Dallas,%20TX%2075225'||'&'||'maxSolns=3'||'&'||'du=mi'||'&'||'key= h2d5aewr6_i8768a-9ftw54js7-876asdfasf',p_http_method => 'GET'
          );

2. Next, we'll use APEX_WEB_SERVICE.parse_xml API to extract a specific node of the XML document stored in the XMLTYPE, and we can store it in a locally declared VARCHAR2 variable.

But before that, let's check if Status Code returned by REST API was 200 (i.e. OK) by making the call below -

lv_rest_status :=
          APEX_WEB_SERVICE.parse_xml (
            p_xml   => XMLTYPE(lc_clob),
            p_xpath => '//Response/StatusCode'
          );

3. Now let’s make the final API call to retrieve actual mileages returned by the REST webservice -

lv_mileages := 
          APEX_WEB_SERVICE.parse_xml (
            p_xml   => XMLTYPE(lc_clob),
            p_xpath => '//Response/ResourceSets/ResourceSet/Resources/Route/TravelDistance'
          );

This will return all the requested mileage values indicated by TravelDistance nodes as shown below.

<TravelDistance>3.375288</TravelDistance><TravelDistance>5.857666</TravelDistance><TravelDistance>8.010096</TravelDistance>

This is a simple example of how we can consume simple REST webservices in Oracle APEX. The complication of this process depends on the type of REST webservice we are consuming and complexity of data set we are retrieving and parsing, but the basic process follows the aforementioned flow.

Read More