Thursday, July 14, 2016

Calling Web Service From PL/SQL : Temperature Converter.

This is also based on a post from OTN.
I have used WebserviceX.NET :: XML Web Services solution provider. This has many webservices that you can use for your practice (I do).
Here is one demo of converting temperature. The code, coding standard and comments are signature of Billy~Verreynne. This is build on function called GetCityWeather.
Now, the question comes where did I get this SOAP envelop. If you go to that WebService detail page and click on the name then you can see various envelops.

    [oracle@localhost ~]$ sqlplus scott/tiger  
      
    SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 12 12:04:34 2016  
      
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.  
      
      
    Connected to:  
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  
    With the Partitioning, OLAP, Data Mining and Real Application Testing options  
      
    SQL> -- My dtabase version.  
    SQL> 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  
      
    SQL> ed  
    Wrote file afiedt.buf  
      
      1  create or replace function ConvertTemperature( Temperature integer, FromUnit varchar2, ToUnit varchar2 ) return XmlType 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      '<?xml version="1.0" encoding="utf-8"?>  
      7  <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/">  
      8    <soap:Body>  
      9      <ConvertTemp xmlns="http://www.webserviceX.NET/">  
     10        <Temperature>$TEMPERATURE</Temperature>  
     11        <FromUnit>$FROMUNIT</FromUnit>  
     12        <ToUnit>$TOUNIT</ToUnit>  
     13      </ConvertTemp>  
     14    </soap:Body>  
     15  </soap:Envelope>';  
     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     begin  
     33             --// create the SOAP envelope  
     34             soapEnvelope := replace( SOAP_ENVELOPE, '$TEMPERATURE', Temperature );  
     35             soapEnvelope := replace( soapEnvelope, '$FROMUNIT', FromUnit );  
     36             soapEnvelope := replace( soapEnvelope, '$TOUNIT', ToUnit );  
     37             --// our "browser" settings  
     38             utl_http.set_response_error_check( true );  
     39             utl_http.set_detailed_excp_support( true );  
     40             utl_http.set_cookie_support( true );  
     41             utl_http.set_transfer_timeout( 10 );  
     42             utl_http.set_follow_redirect( 3 );  
     43             utl_http.set_persistent_conn_support( true );  
     44             --// configure for web proxy access if applicable  
     45             if proxyServer is not null then  
     46                     proxyURL := 'http://'||proxyServer;  
     47                     if (proxyUser is not null) and (proxyPass is not null) then  
     48                             proxyURL := Replace( proxyURL, 'http://',  'http://'||proxyUser||':'||proxyPass||'@' );  
     49                     end if;  
     50                      utl_http.set_proxy( proxyURL, null );  
     51             end if;  
     52             --// make the POST call to the web service  
     53             request := utl_http.begin_request( SOAP_URL, 'POST', utl_http.HTTP_VERSION_1_1 );  
     54             utl_http.set_header( request, 'User-Agent', C_USER_AGENT );  
     55             utl_http.set_header( request, 'Content-Type', 'text/xml; charset=utf-8' );  
     56             utl_http.set_header( request, 'Content-Length', length(soapEnvelope) );  
     57             utl_http.set_header( request, 'SoapAction', 'http://www.webserviceX.NET/ConvertTemp' );  
     58             utl_http.write_text( request, soapEnvelope );  
     59             --// read the web service HTTP response  
     60             response := utl_http.get_response( request );  
     61             dbms_lob.CreateTemporary( soapResponse, true );  
     62             eof := false;  
     63             loop  
     64                     exit when eof;  
     65                     begin  
     66                             utl_http.read_line( response, buffer, true );  
     67                             if length(buffer) > 0 then  
     68                                     dbms_lob.WriteAppend(  
     69                                             soapResponse,  
     70                                             length(buffer),  
     71                                             buffer  
     72                                     );  
     73                             end if;  
     74                     exception when utl_http.END_OF_BODY then  
     75                             eof := true;  
     76                     end;  
     77             end loop;  
     78             utl_http.end_response( response );  
     79             --// as the SOAP responds with XML, we convert  
     80             --// the response to XML  
     81             xmlResponse := XmlType( soapResponse );  
     82             dbms_lob.FreeTemporary( soapResponse );  
     83             return( xmlResponse );  
     84     exception when OTHERS then  
     85             if soapResponse is not null then  
     86                     dbms_lob.FreeTemporary( soapResponse );  
     87             end if;  
     88             raise;  
     89*   end;  
     90  /  
      
    Function created.  
      
    SQL> exit  
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  
    With the Partitioning, OLAP, Data Mining and Real Application Testing options  
    [oracle@localhost ~]$ sqlplus / as sysdba  
      
    SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 12 12:05:45 2016  
      
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.  
      
      
    Connected to:  
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  
    With the Partitioning, OLAP, Data Mining and Real Application Testing options  
      
    SQL> BEGIN  
      DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'ConverTemperature.xml',  
                                        description => 'ConverTemperature ACL',  
                                        principal   => 'SCOTT',  
                                        is_grant    => true,  
                                        privilege   => 'connect');  
      
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'ConverTemperature.xml',  
                                           principal => 'SCOTT',  
                                           is_grant  => true,  
                                           privilege => 'resolve');  
      
      DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'ConverTemperature.xml',  
                                        host => 'www.webservicex.net');  
     commit;                                      
    END;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16    
     17  /  
      
    PL/SQL procedure successfully completed.  
      
    SQL> conn scott/tiger  
    Connected.  
    SQL> set line 1000  
    SQL> set long 100000  
    SQL> select ConvertTemperature(20,'degreeCelsius','degreeFahrenheit') from dual;  
      
    CONVERTTEMPERATURE(20,'DEGREECELSIUS','DEGREEFAHRENHEIT')  
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
    <?xml version="1.0" encoding="US-ASCII"?>  
    <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>68</ConvertTempResult>  
        </ConvertTempResponse>  
      </soap:Body>  
    </soap:Envelope>  
      
      
    SQL> select ConvertTemperature(68,'degreeFahrenheit','degreeCelsius') from dual;  
      
    CONVERTTEMPERATURE(68,'DEGREEFAHRENHEIT','DEGREECELSIUS')  
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
    <?xml version="1.0" encoding="US-ASCII"?>  
    <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>20</ConvertTempResult>  
        </ConvertTempResponse>  
      </soap:Body>  
    </soap:Envelope>  
      
      
    SQL>   

No comments :

Post a Comment