Rapid PL/SQL Web Service Client development (using soapUI and utl_http)

I have written about similar topics on several occasions. But now I feel an urgent need to do so again.  As with the use of the wonderful soapUI tool and a fairly well refactored piece of PL/SQL, you can create PL/SQL code calling into SOAP WebServices in no time at all. They can be external, maybe even public web service, or the front ends of internal services deployed to an Enterprise Service Bus or a BPEL engine. Thanks to standards such as WSDL, SOAP and XML it all works in the same way.

In this article I will show how I:

• locate a temperature conversion service in the public domain
• use soapUI to rapidly test call this service, meanwhile getting examples of the required request message structure as well as the returned response message structure
• extend a piece of reusable PL/SQL code to call the service and process the response

This allows me to create a PL/SQL Function convertFahrenheitToCelcius( p_fahrenheit_temperature in number) without knowing the first thing about adding/subtracting 32 and multiplying/dividing by 9 and 5. In less than 7 minutes. And being in New Orleans where the weather report speaks of temperatures in the 80s and 90s, I have a serious need to do this conversion before picking my garb for the day.

1. locate a temperature conversion service in the public domain

I frequently have a problem to find a publicly available web service for any demo or article. Today, I came across a web site called http://seekda.com/. This site offers pointers to many different web services, including the one I needed: Convert Temperature (from Celsius to Fahrenheit and back). Its description is at: http://seekda.com/service_details?uri=http%3A%2F%2Fwww.webservicex.net%2FConvertTemperature.asmx%3Fwsdl .

With the WSDL location in my pocket, I can find out how to invoke this service. However, that can be quite a tricky job (for someone as unstructured as me). And there is an easier way to find out how to call this service:

2. use soapUI to rapidly test call this service, meanwhile getting examples of the required request message structure as well as the returned response message structure

I downloaded soapUI, an open source Web Service Testing tool. It can do quite a bit more than just testing by the way – or it has a pretty broad definition of the term testing. Anyway, after downloading soapUI, I ran it. I create a new WSDL project and feed it the URL of the WSDL for the ConvertTemperature Service:

Now even though soapUI can create the outline for my SOAP requests into the service, I still need to provide some of the values:

The question is: what are the values I can fill in, especially from the fromUnit and toUnit items. In the WSDL for the service, the document that I preferred not to look at myself, I quickly located the information needed:

The values for fromUnit and toUnit are degreeCelsius and degreeFahrenheit. Now I can complete the SOAP request message:

and press the run button to perform a call to the service. The response comes in looking like this:

So now I know exactly what SOAP message to send to the service:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
        xmlns:web="http://www.webserviceX.NET/">
   <soapenv:Header/>
   <soapenv:Body>
      <web:ConvertTemp>
         <web:Temperature>80</web:Temperature>
         <web:FromUnit>degreeFahrenheit</web:FromUnit>
         <web:ToUnit>degreeCelsius</web:ToUnit>
      </web:ConvertTemp>
   </soapenv:Body>
</soapenv:Envelope>

and what response message to expect in return.

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <soap:Body>
      <ConvertTempResponse xmlns="http://www.webserviceX.NET/">
         <ConvertTempResult>26.666666666666686</ConvertTempResult>
      </ConvertTempResponse>
   </soap:Body>
</soap:Envelope>

Time to create the PL/SQL code that will perform the call and expose the functionality as a ‘native’ PL/SQL function.

3. extend a piece of reusable PL/SQL code to call the service and process the response

We can first set up a nice generic PL/SQL function that will handle all the SOAP plumbing for us.

  create or replace function soap_call
  ( p_payload    in varchar2
  , p_target_url in varchar2
  , p_soap_action in varchar2 default 'process'
  ) return xmltype
  is
    c_soap_envelope varchar2(250):= '<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
                                       <soap:Body>**payload**</soap:Body>
                                     </soap:Envelope>';
    l_soap_request varchar2(30000);
    l_soap_response varchar2(30000);
    http_req utl_http.req;
    http_resp utl_http.resp;
  begin
    l_soap_request := replace(c_soap_envelope, '**payload**', p_payload);
    http_req:= utl_http.begin_request
               ( p_target_url
               , 'POST'
               , 'HTTP/1.1'
               );
    utl_http.set_header(http_req, 'Content-Type', 'text/xml');
    utl_http.set_header(http_req, 'Content-Length', length(l_soap_request));
    utl_http.set_header(http_req, 'SOAPAction', p_soap_action);
    utl_http.write_text(http_req, l_soap_request);
    -- the actual call to the service is made here
    http_resp:= utl_http.get_response(http_req);
    utl_http.read_text(http_resp, l_soap_response);
    utl_http.end_response(http_resp);

    -- only return the payload from the soap response - that is: the content of the body element in the SOAP envelope
    return XMLType.createXML( l_soap_response).extract( '/soap:Envelope/soap:Body/child::node()'
                     , 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'
                     );
  end;

Note how this function has now Temperature Service specific elements – it is completely generic and can be used for calling any SOAP based Web Service.

Then we write the specific function for converting the temperature. This function sends the proper payload for the service we want to call and processes the returned response:

create or replace function convertFahrenheitToCelcius
( p_fahrenheit_temperature in number
) return number
is
  l_response_payload  XMLType;
  l_payload           varchar2(2000);
  l_payload_namespace varchar2(200);
  l_target_url        varchar2(200);
BEGIN
  l_payload_namespace := 'http://www.webserviceX.NET/';
  l_target_url        := 'http://www.webservicex.net/ConvertTemperature.asmx';
  l_payload          :=
    '  <web:ConvertTemp xmlns:web="http://www.webserviceX.NET/">
         <web:Temperature>'
         ||to_char(p_fahrenheit_temperature)||
         '</web:Temperature>
         <web:FromUnit>degreeFahrenheit</web:FromUnit>
         <web:ToUnit>degreeCelsius</web:ToUnit>
      </web:ConvertTemp>';
  l_response_payload := soap_call(l_payload, l_target_url, 'http://www.webserviceX.NET/ConvertTemp');
  return to_number(l_response_payload.extract('/ConvertTempResponse/ConvertTempResult/text()', 'xmlns="'|| l_payload_namespace||'"').getStringVal());
END ;

With the new function in place, we can write queries like this one – and get the temperature converted. Mind you – the performance of this particular function is not wildly terrific; we could certainly benefit from the 11g Result Cache feature for this one. Or of course learn how to do this calculation ourselves.

Resources

Search Engine for publicly available Web Services: http://seekda.com/

soapUI – Web Service Testing tool – http://www.soapui.org/