07/11/2012

[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;

4 comments:

  1. Hi,
    thanks for nice blog.

    while executing am getting below error .. can you please help.
    SQL> SELECT GetTemp('30', 'degreeCelsius', 'degreeFahrenheit') FROM dual;
    ERROR:
    ORA-24247: network access denied by access control list (ACL)
    ORA-06512: at "GEMB.GETTEMP", line 103

    ReplyDelete
    Replies
    1. Cheers,

      if you're using Oracle 10 it should be sufficient to grant execute privileges on the UTL_HTTP package to the user invoking it.

      If you're on Oracle 11 you must also configure the access list for your user, read here: http://www.oracleflash.com/36/Oracle-11g-Access-Control-List-for-External-Network-Services.html

      Delete
  2. thanks for prompt response,

    I am getting below error ( oracle 11g)

    SQL> SELECT GetTemp('30', 'degreeCelsius', 'degreeFahrenheit') from dual;
    ERROR:
    ORA-24247: network access denied by access control list (ACL)
    ORA-06512: at "GEMB.GETTEMP", line 104
    no rows selected


    --can add successfully -*.webservicex.net

    SQL> BEGIN
    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl => 'oracleflash.xml',
    host => '*.webservicex.net');
    COMMIT;
    END; 2 3 4 5 6
    7 /

    PL/SQL procedure successfully completed.

    -- there it is showing error in adding '*.webserviceX.NET/ConvertTemp

    SQL> BEGIN
    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl => 'oracleflash.xml',
    host => '*.webserviceX.NET/ConvertTemp');
    COMMIT;
    END; 2 3 4 5 6
    7 /
    BEGIN
    *
    ERROR at line 1:
    ORA-24244: invalid host or port for access control list (ACL) assignment
    ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 164
    ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 485
    ORA-06512: at line 2


    ReplyDelete
    Replies
    1. Hi,

      why would you add *.webserviceX.NET/ConvertTemp too if you have already added *.webservicex.net ?

      Doesn't it work after you successfully add the main domain? I'm not very practical with Oracle's ACL since I always used the 10g version but the error you got suggests that you specified a bad host, in this case *.webserviceX.NET/ConvertTemp

      Delete

With great power comes great responsibility.

Da grandi poteri derivano grandi responsabilità.