Calling Web Service From PL/SQL : Temperature Converter Using 1. UTL_HTTP 2. UTL_DBWS

This is part II of my earlier post of Calling Webservice through pl/sql. This post is also based on one of the threads in OTN.
I will demonstrate calling free Web-services available in http://www.webservicex.net/new/Home/Index  using two methods: 1. UTL_HTTP 2. UTL_DBWS and both are working perfectly.

SCOTT@orclSB 27-FEB-17> -- My database version.
SCOTT@orclSB 27-FEB-17> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SCOTT@orclSB 27-FEB-17> ed
Wrote file afiedt.buf

  1  create or replace function ConvertTemperature( Temperature integer, FromUnit varchar2, ToUnit varchar2 ) return integer is
  2              --// URL to call
  3              SOAP_URL        constant varchar2(1000) := 'http://www.webservicex.net/ConvertTemperature.asmx';
  4              --// SOAP envelope template, containing $ substitution variables
  5              SOAP_ENVELOPE   constant varchar2(32767) :=
  6      '
  7  
  8    
  9      
 10        $TEMPERATURE
 11        $FROMUNIT
 12        $TOUNIT
 13      
 14    
 15  ';
 16             --// we ll identify ourselves using an IE9/Windows7 generic browser signature
 17             C_USER_AGENT    constant varchar2(4000) := 'Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0)';
 18             --// these variables need to be set if web access
 19             --// is via a proxy server
 20             proxyServer varchar2(20) default null;
 21             proxyUser varchar2(20) default null;
 22             proxyPass varchar2(20) default null;
 23             --// our local variables
 24             soapEnvelope    varchar2(32767);
 25             proxyURL        varchar2(4000);
 26             request         utl_http.req;
 27             response        utl_http.resp;
 28             buffer          varchar2(32767);
 29             soapResponse    clob;
 30             xmlResponse     XmlType;
 31             eof             boolean;
 32             lResponse integer;
 33     begin
 34             --// create the SOAP envelope
 35             soapEnvelope := replace( SOAP_ENVELOPE, '$TEMPERATURE', Temperature );
 36             soapEnvelope := replace( soapEnvelope, '$FROMUNIT', FromUnit );
 37             soapEnvelope := replace( soapEnvelope, '$TOUNIT', ToUnit );
 38             --// our "browser" settings
 39             utl_http.set_response_error_check( true );
 40             utl_http.set_detailed_excp_support( true );
 41             utl_http.set_cookie_support( true );
 42             utl_http.set_transfer_timeout( 10 );
 43             utl_http.set_follow_redirect( 3 );
 44             utl_http.set_persistent_conn_support( true );
 45             --// configure for web proxy access if applicable
 46             if proxyServer is not null then
 47                     proxyURL := 'http://'||proxyServer;
 48                     if (proxyUser is not null) and (proxyPass is not null) then
 49                             proxyURL := Replace( proxyURL, 'http://',  'http://'||proxyUser||':'||proxyPass||'@' );
 50                     end if;
 51                      utl_http.set_proxy( proxyURL, null );
 52             end if;
 53             --// make the POST call to the web service
 54             request := utl_http.begin_request( SOAP_URL, 'POST', utl_http.HTTP_VERSION_1_1 );
 55             utl_http.set_header( request, 'User-Agent', C_USER_AGENT );
 56             utl_http.set_header( request, 'Content-Type', 'text/xml; charset=utf-8' );
 57             utl_http.set_header( request, 'Content-Length', length(soapEnvelope) );
 58             utl_http.set_header( request, 'SoapAction', 'http://www.webserviceX.NET/ConvertTemp' );
 59             utl_http.write_text( request, soapEnvelope );
 60             --// read the web service HTTP response
 61             response := utl_http.get_response( request );
 62             dbms_lob.CreateTemporary( soapResponse, true );
 63             eof := false;
 64             loop
 65                     exit when eof;
 66                     begin
 67                             utl_http.read_line( response, buffer, true );
 68                             if length(buffer) > 0 then
 69                                     dbms_lob.WriteAppend(
 70                                             soapResponse,
 71                                             length(buffer),
 72                                             buffer
 73                                     );
 74                             end if;
 75                     exception when utl_http.END_OF_BODY then
 76                             eof := true;
 77                     end;
 78             end loop;
 79             utl_http.end_response( response );
 80             --// as the SOAP responds with XML, we convert
 81             --// the response to XML
 82             xmlResponse := XmlType( soapResponse );
 83             dbms_lob.freetemporary( soapresponse );
 84             select extractvalue(xmlResponse,'/soap:Envelope/soap:Body/ns0:ConvertTempResponse/ns0:ConvertTempResult'
 85  ,'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/", xmlns:ns0="http://www.webserviceX.NET/"')
 86   into lResponse
 87   from dual;
 88             return( lResponse );
 89     exception when OTHERS then
 90             if soapResponse is not null then
 91                     dbms_lob.FreeTemporary( soapResponse );
 92             end if;
 93             raise;
 94*   end;
 95  /

Function created.

SCOTT@orclSB 27-FEB-17> select converttemperature(68,'degreeFahrenheit','degreeCelsius') from dual; 

CONVERTTEMPERATURE(68,'DEGREEFAHRENHEIT','DEGREECELSIUS')
---------------------------------------------------------
                                                       20

SCOTT@orclSB 27-FEB-17> ed
Wrote file afiedt.buf

  1  create or replace function ConvertTemperature_dbws( Temperature integer, FromUnit varchar2, ToUnit varchar2 ) return integer is
  2              --// URL to call
  3              SOAP_URL        constant varchar2(1000) := 'http://www.webservicex.net/ConvertTemperature.asmx';
  4              --// SOAP envelope template, containing $ substitution variables
  5  SOAP_ENVELOPE   constant varchar2(32767) :=
  6      '
  7        $TEMPERATURE
  8        $FROMUNIT
  9        $TOUNIT
 10      ';
 11    lservice           sys.utl_dbws.service;
 12    DBWScall             sys.utl_dbws.CALL;
 13    serviceQname      sys.utl_dbws.QNAME;
 14    port_qname         sys.utl_dbws.QNAME;
 15    xmlResponse           sys.xmltype;
 16    request            sys.xmltype;
 17    lResponse integer;
 18    soapEnvelope    varchar2(32767);
 19     begin
 20             --// create the SOAP envelope
 21             soapEnvelope := replace( SOAP_ENVELOPE, '$TEMPERATURE', Temperature );
 22             soapEnvelope := replace( soapEnvelope, '$FROMUNIT', FromUnit );
 23             soapEnvelope := replace( soapEnvelope, '$TOUNIT', ToUnit );
 24             serviceQname := sys.utl_dbws.to_qname(null, 'ConvertTemp');
 25    lservice := sys.utl_dbws.create_service(serviceQname);
 26    DBWScall := sys.utl_dbws.create_call(lservice);
 27    sys.utl_dbws.set_target_endpoint_address(DBWScall, SOAP_URL);
 28    sys.utl_dbws.set_property( DBWScall, 'SOAPACTION_USE', 'TRUE');
 29    sys.utl_dbws.set_property( DBWScall, 'SOAPACTION_URI', 'http://www.webserviceX.NET/ConvertTemp');
 30    xmlResponse :=sys. utl_dbws.invoke(DBWScall, xmltype(soapenvelope));
 31   select extractvalue(xmlresponse,'ConvertTempResponse/ConvertTempResult','xmlns:="http://www.webserviceX.NET/"')
 32   into lresponse
 33  from dual;
 34    return (lresponse);
 35*   end ConvertTemperature_dbws;
 36  /

Function created.

SCOTT@orclSB 27-FEB-17> select converttemperature_dbws(68,'degreeFahrenheit','degreeCelsius') from dual; 

CONVERTTEMPERATURE_DBWS(68,'DEGREEFAHRENHEIT','DEGREECELSIUS')
--------------------------------------------------------------
                                                            20

SCOTT@orclSB 27-FEB-17> l