[SQL] Oracle call web service and read response

In Oracle, you can invoke a web service directly from a PL/SQL function/procedure and parse the response using the UTL_HTTP module.

It should be included with every standard installation, and it's owned by SYS; you may need to run:

GRANT EXECUTE ON UTL_HTTP TO [user]

 as SYS (usually user: SYS password: manager) to allow other users to execute that package's procedures.



For this example, we will be calling the Temperature Unit Convertor SOAP 1.1 web service which is freely accessible thanks to WebserviceX.NET with a POST request.

It converts a given temperature between:

• Celsius: degreeCelsius
• Fahrenheit: degreeFahrenheit
• Rankine: degreeRankine
• Reaumur: degreeReaumur
• Kelvin: kelvin

The XML request has to be formed as:

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<ConvertTemp xmlns="http://www.webserviceX.NET/">
<Temperature>double</Temperature>
<FromUnit>from</FromUnit>
<ToUnit>to</ToUnit>
</ConvertTemp>
</soap:Body>
</soap:Envelope>

And, if everything has been set correctly, will produce the following response:

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<ConvertTempResponse xmlns="http://www.webserviceX.NET/">
<ConvertTempResult>double</ConvertTempResult>
</ConvertTempResponse>
</soap:Body>
</soap:Envelope>

Now, our function (copied from Billy Verreynne's reply on the Oracle OTN discussion forums) which has in-line comments:

 CREATE OR REPLACE   
 FUNCTION GetTemp( temp VARCHAR2, fromdeg VARCHAR2, todeg VARCHAR2 ) RETURN XmlType IS  
      --// URL to call  
      SOAP_URL    CONSTANT VARCHAR2(1000) := 'http://www.webservicex.net/ConvertTemperature.asmx';  
    
      --// SOAP envelope template, containing $ substitution variables  
      SOAP_ENVELOPE  CONSTANT VARCHAR2(32767) :=  
  '<?xml version="1.0" encoding="utf-8"?>  
 <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">  
  <soap:Body>  
   <ConvertTemp xmlns="http://www.webserviceX.NET/">  
    <Temperature>$temp</Temperature>  
    <FromUnit>$fromdeg</FromUnit>  
    <ToUnit>$todeg</ToUnit>  
   </ConvertTemp>  
  </soap:Body>  
 </soap:Envelope>';  
    
      --// we'll identify ourselves using an IE9/Windows7 generic browser signature  
      C_USER_AGENT  CONSTANT VARCHAR2(4000) := 'Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0)';  
    
      --// these variables need to be set if web access  
      --// is via a proxy server  
      proxyServer VARCHAR2(20) :='yourproxy:yourport';  
      proxyUser VARCHAR2(20):='youruser';  
      proxyPass VARCHAR2(20):='yourpass';  
    
      --// our local variables  
      soapEnvelope  VARCHAR2(32767);  
      proxyURL    VARCHAR2(4000);  
      request     utl_http.req;  
      response    utl_http.resp;  
      buffer     VARCHAR2(32767);  
      soapResponse  CLOB;  
      xmlResponse   XmlType;  
      eof       BOOLEAN;  
  BEGIN  
      --// create the SOAP envelope (XML request)  
      soapEnvelope := REPLACE(SOAP_ENVELOPE, '$temp', temp );  
      soapEnvelope := REPLACE(soapEnvelope, '$fromdeg', fromdeg );  
      soapEnvelope := REPLACE(soapEnvelope, '$todeg', todeg );  
        
            --// show the request (for debug)  
      --DBMS_OUTPUT.PUT_LINE(soapEnvelope);  
    
      --// our "browser" settings  
      utl_http.set_response_error_check( true );  
      utl_http.set_detailed_excp_support( true );  
      utl_http.set_cookie_support( true );  
      utl_http.set_transfer_timeout( 10 );  
      utl_http.set_follow_redirect( 3 );  
      utl_http.set_persistent_conn_support( true );  
    
      --// configure for web proxy access if applicable  
      IF proxyServer IS NOT NULL THEN  
          proxyURL := 'http://'||proxyServer;  
          IF (proxyUser IS NOT NULL) AND (proxyPass IS NOT NULL) THEN  
              proxyURL := REPLACE( proxyURL, 'http://', 'http://'||proxyUser||':'||proxyPass||'@' );  
          END IF;  
          utl_http.set_proxy(proxyURL, null);  
      END IF;  
    
      --// make the POST call to the web service  
      request := utl_http.begin_request( SOAP_URL, 'POST', utl_http.HTTP_VERSION_1_1 );  
      utl_http.set_header( request, 'User-Agent', C_USER_AGENT );  
      utl_http.set_header( request, 'Content-Type', 'text/xml; charset=utf-8' );  
      utl_http.set_header( request, 'Content-Length', LENGTH(soapEnvelope) );  
      utl_http.set_header( request, 'SoapAction', 'http://www.webserviceX.NET/ConvertTemp' );  
      utl_http.write_text( request, soapEnvelope );  
    
      --// read the web service HTTP response  
      response := utl_http.get_response( request );  
      dbms_lob.CreateTemporary( soapResponse, true );  
      eof := false;  
      LOOP  
          EXIT WHEN eof;  
          BEGIN  
              utl_http.read_line( response, buffer, true );  
              IF LENGTH(buffer) > 0 THEN  
                  dbms_lob.WriteAppend(  
                      soapResponse,  
                      LENGTH(buffer),  
                      buffer  
                  );  
              END IF;  
    
          EXCEPTION WHEN utl_http.END_OF_BODY THEN  
              eof := true;  
          END;  
    
      END LOOP;  
      utl_http.end_response( response );  
    
      --// as the SOAP responds with XML, we convert  
      --// the response to XML  
      xmlResponse := XmlType( soapResponse );  
      dbms_lob.FreeTemporary( soapResponse );  
    
      RETURN( xmlResponse );  
    
  EXCEPTION WHEN OTHERS THEN  
      IF soapResponse IS NOT NULL THEN  
          dbms_lob.FreeTemporary( soapResponse );  
      END IF;  
      RAISE;  
  END;  

To call it, returning the XML response:

SELECT GetTemp('30', 'degreeCelsius', 'degreeFahrenheit')
FROM dual;

And to extract the numeric result from the response:

SELECT EXTRACTVALUE(
       GetTemp('30', 'degreeCelsius', 'degreeFahrenheit'),
        '//soap:Envelope/soap:Body/ConvertTempResponse/ConvertTempResult/text()',
        'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns="http://www.webserviceX.NET/"') Temperature
FROM dual;