Apex and RESTful web services

You always read that it is so easy to consume RESTful or SOAP web services in Apex. Well, yes, when you know how it works. It took me some time to figure it out, googling for bits of information on every step. If you are struggling too, this may help you to get started.

The start lies in the database, and has nothing to do with Apex. You must make sure first that the web service you need can be reached from the database. For this, you have to configure a proper ACL (Access Control List). How to do this can be found in the Apex Application Builder User's Guide, Enabling Network Services in Oracle Database 11g. If you experience problems later on, read this blog by Joel Kallman for a possible explanation why it doesn't work.

To check if you can call the web service, first call it from within the database, before going to Apex. To test if it really works in Apex, you should probably run this logged in as the Apex schema owner (e.g. APEX_040100 for apex 4.1). Since that is usually not possible for developers, we will just assume (hope) it will work later on.
I have no idea what this web service at weather.gov does, but you can use it as a demo. It is just a good example of a GET type web service with 5 parameters.

In the simplest test, you call the url directly using utl_http.request, as you would in your browser:

set scan off
select utl_http.request( 'http://graphical.weather.gov/xml/sample_products/browser_interface/ndfdXMLclient.php?lat=38.99&lon=-77.01&product=time-series&begin=2004-01-01T00:00:00&end=2013-04-20T00:00:00&maxt=maxt&mint=mint') weather
from dual;

Using Apex functionality, the URL, parameters and parameter values are split in different input parameters for procedure apex_web_service.make_rest_request:

set scan off
declare
  v_result clob;
begin
  v_result := apex_web_service.make_rest_request(
    p_url            => 'http://graphical.weather.gov/xml/sample_products/browser_interface/ndfdXMLclient.php'
   ,p_http_method    => 'GET'
   ,p_parm_name => apex_util.string_to_table('lat:lon:product:maxt:mint')
   ,p_parm_value => apex_util.string_to_table('39:-77:glance:maxt:mint')
    );
  dbms_output.put_line(v_result);  
end;

This should return a big xml document. Later on, in Apex, I will extract the maximum temperature from this xml document. The maximum temperature can be found in this node:

<temperature time-layout="k-p24h-n7-1" type="maximum" units="Fahrenheit">
  <name>Daily Maximum Temperature</name>
  <value>34</value>
  ... etc.
</temperature>

Now to Apex. I won't describe the basic steps that can be found in the User's Guide (like the fact that you create a web service reference in the Shared Components of the application).
You can use the url and parameters that you used when testing the web service in the database

The tricky part, for me anyway, was what to put in "Response XPath". This was new to me, so I had to take a crash course at w3schools.com XPath.Tutorial.
This is where the XML response you got when testing the web service in the database comes in. In this example, the XPath is:

//temperature[@type="maximum"]/value

which (sort of) means: select all value elements that are children of temperature elements that have an attribute named type with a value 'maximum'.

The REST output parameter path is now just "/*", since the XPath already goes down to the value element.

So, here is a trick to play with the XPath. You copy the part of the XML response you need and put that part as text in this query:

with c as
  (select xmltype('
                    
                       Daily Maximum Temperature
                       50
                       50
                       62
                       68
                       61
                       53
                       52
                    
                   ') xmltype001
   from dual               
  )
select extractValue(value(t),'/*') "value"
from c
   , table(xmlsequence(extract(c.xmltype001,'//temperature[@type="maximum"]/value'))) t 

Now you can easily play around with the Response XPath ('//temperature[@type="maximum"]/value') and Output parameter Path ('/*') until it works.

Once this is all done it is easy to create a page for this web service.
Create a new page and select Form, Form and Reports on Web Service. For the rest, you just follow the wizard.

There is another version of RESTful web services that doesn't use parameters (or it uses a mixed version). An example is this URL:

http://services.faa.gov/airport/status/SFO?format=xml

This uses the airport code ('SFO') as part of the URL, and format as a parameter. So, how do you put the airport code in the Apex web service reference? This is simply done by substituting a page item in the URL, e.g.

http://services.faa.gov/airport/status/&P5_AIRPORT_CODE.

In this case, you need to create a page item P5_AIRPORT_CODE manually. The wizard won't do this for you.