11 Replies Latest reply: May 1, 2013 8:38 AM by AlbertoFaenza RSS

    Calling webservice from PLSQL


      I am having a scenario where .Net web service will need be called from the PLSQL. Data will pass from Oracle e business to .Net application.
      I have made the following code ready by taking help from google, other OTN forums, Metalink.

      In this there are 2 inputs, employee number as varchar and amount as number and output it should return is the approver employee number (varchar2).

      The function code is mentioned below :

      CREATE OR REPLACE FUNCTION apps.xx_createadv_fnc (
      empnumber IN VARCHAR2,
      amount IN NUMBER
      l_service SYS.UTL_DBWS.service;
      l_call SYS.UTL_DBWS.CALL;
      l_wsdl_url VARCHAR2 (32767);
      l_namespace VARCHAR2 (32767);
      l_service_qname SYS.UTL_DBWS.qname;
      l_port_qname SYS.UTL_DBWS.qname;
      l_operation_qname SYS.UTL_DBWS.qname;
      l_xmltype_in SYS.XMLTYPE;
      l_xmltype_out SYS.XMLTYPE;
      l_return VARCHAR2 (32767);
      l_wsdl_url := '';
      l_namespace := 'http://tempuri.org';
      l_service_qname :=
      SYS.UTL_DBWS.to_qname (l_namespace, 'CashAdvanceDataService');
      l_port_qname := SYS.UTL_DBWS.to_qname (l_namespace, '4949');
      l_operation_qname :=
      SYS.UTL_DBWS.to_qname (l_namespace, 'GetEmployeeApprover()');
      l_service :=
      (wsdl_document_location => urifactory.geturi
      service_name => l_service_qname
      l_call :=
      SYS.UTL_DBWS.create_call (service_handle => l_service,
      port_name => l_port_qname,
      operation_name => l_operation_qname
      l_xmltype_in :=
      ( '<?xml version="1.0" encoding="utf-8"?>
      <ws_add xmlns="'
      || l_namespace
      || '">
      || empnumber
      || '</int1>
      || amount
      || '</int2>
      l_xmltype_out :=
      SYS.UTL_DBWS.invoke (call_handle => l_call,
      request => l_xmltype_in);
      SYS.UTL_DBWS.release_call (call_handle => l_call);
      SYS.UTL_DBWS.release_service (service_handle => l_service);
      l_return := l_xmltype_out.EXTRACT ('//return/text()').getstringval ();
      RETURN l_return;

      This function compiled sucessfully. But while executing this function its showing some error related to UTL_DBWS package but this actually is not part of actual question. Since this is something which DBA will get it installed.

      My actual questions is that if my code is correct with respect to my requirement where input is passed as employee number as varchar and amount as number and output will be the approver employee number (varchar). This logic is written in a plsql procedure GetEmployeeApprover(This name is mentioned in the above code also

      l_operation_qname := SYS.UTL_DBWS.to_qname (l_namespace, 'GetEmployeeApprover()');

      My actual questions are:
      1) Does my code looks fine with respect to my requirement (2 inputs and 1 OUT parameter)

      2) If say I have another procedure where I am having 2 inputs and 2 ouputs (as OUT Parameter)
      say employee - number type , amount type are inputs
      and out parameters are assignment number(varchar2), last name(varchar2). what changes will be there in the existing code

      3) If there is 1 OUT parameter as Ref cursor. how the above code will change ?

      4) If there is 1OUT parameter as object type. how the above code will change ?

      This is little urgent. I would appreciate any suggestions on this post.


        • 1. Re: Calling webservice from PLSQL
          I use PL/SQL extensively, but not for WS. For WS I mostly use Java.
          So I will skip 1 for now.

          Q2. onwards

          I would rather stick to Function and not get into Procedure. A function can return only one variable, but you can return multiple values from the function if you use collections. E.g.
          CREATE OR REPLACE FUNCTION apps.xx_createadv_fnc (
          empnumber IN VARCHAR2,
          amount IN NUMBER
          RETURN OWA_UTIL.VC_ARR  -- this is a Associative array def in the OWA_UTIL Pkg. TYPE VC_ARR IS ARRAY OF VARCHAR2(2000)....
          RET_ARR   OWA_UTIL.VC_ARR;
          In the Begin section populate the values to return in to the RET_ARR
              RET_ARR(1) := va11 ; -- index starts from 1, so this is 1 st element
              RET_ARR(2) := val2; 
          And then finally return the array
             RETURN RET_ARR;
          Just like the array was passed the Ref cursor may also be passed, though I am not sure you need it for consuming WS.
          If you want to pass the entire Response or the Payload in the Response (XML without SOAP header/footer) then you should perhaps be returning XMLType. Or serialize (GETCLOBVALUE) and return the resulting CLOB.

          When you try to extract more than one values from the Response you will have more statements to extract like the one below (from your code).
          l_return := l_xmltype_out.EXTRACT ('//return/text()').getstringval ();
          In the above you are getting in value in the return tag. And it is type String.

          Just like getstringval () you have getnumberval()

          Many WS use only string return types, so you need to check the Response Payload / WSDL for which types you really need.

          The timestamp as well as number data types in XML can be tricky for mapping to native data types of the language. E.g. ISO format timestamp is easier to extract as string and then convert to timestamp with a format mask in a 2 step operation rather than 1 because you may have to tweak it little to be able to convert. At least , that has been my experience.

          • 2. Re: Calling webservice from PLSQL

            Just check this link...


            • 3. Re: Calling webservice from PLSQL
              Tanks for the information, but I have a doubt. I've executed some examples and It was succesfull, however when I excute my web service I've got some errors. I think, The problem is the way I use the library.

              If have the next link

              That is my web service developed in Visual Studio .NET Framework 4.0

              How must reference the web service and the method <ws_add> with 2 parameters.

              Thanks for you help.
              • 4. Re: Calling webservice from PLSQL

                If your web serivce returns a value, just create it as function.

                Pass the input parameters to your Web Service through the SOAP-request.

                • 5. Re: Calling webservice from PLSQL

                  Thanks a lot for all your responses.


                  I have reviewed the link which you provided. It mentions about using Soap api. In the lower end of the link you can see the hyperlink showing use of UTL_DBWS. The code mentioned there is same as the code which I posted (using UTL_DBWS).

                  Presently the DBA is working on installing DBWS, I believe once that is done my code should work fine returning one varchar2 output.

                  just let me know your suggestions on the following:

                  In the code mentioned in http://www.oracle-base.com/articles/10g/utl_dbws-10g.php , I believe ws_add seems to be a plsql stored procedure. Even if its not consider that ws_add is procedure taking 2 inputs and returning 1 number (as OUT parameter).

                  1) Now first question is will the code in http://www.oracle-base.com/articles/10g/utl_dbws-10g.php get change based upon whether (ws_add is a procedure returning output as OUT parameter) or (a function returning output). If so let me know what are the changes in details.

                  2) Now say ws_add procedure has a 2nd OUT parameter which is to give a difference between two input numbers. How to incorporate the same in the code mentioned in http://www.oracle-base.com/articles/10g/utl_dbws-10g.php.

                  3) In some situation , the output parameter may be a ref cursor type. I need to know what will be the changes in the code mentioned in http://www.oracle-base.com/articles/10g/utl_dbws-10g.php in that situaution

                  4) Also what will be the change in the code if output parameter is of object type.

                  Appreciate your response on this.


                  • 6. Re: Calling webservice from PLSQL

                    Any updates in regards to my queries. I have tried doing some R&D in both google and metalink but couldn't get anything which is of good help. I will keep on doing the same. Incase you have any updates, please post.


                    • 7. Re: Calling webservice from PLSQL
                      Check this.

                      • 8. Re: Calling webservice from PLSQL
                        CGMS wrote:
                        Check this.

                        DO NOT drag up old threads to post links to articles that are easily found via google and that have already been provided by other members.

                        If you continue to do this, the moderators may remove your posts.
                        • 9. Re: Calling webservice from PLSQL
                          I do not much like the UTL_DBWS interface. Primary reasons are that the interface is not (IMO) well designed and it does not provide sufficient technical details on the SOAP envelope constructed.

                          So I use UTL_HTTP - as a SOAP web service call is just a plain HTTP POST call, where the data posted is called a SOAP envelope and is just plain XML.

                          The complex part is creating the SOAP envelope - as reading WSDL is not something that is easy or intuitive.

                          I use SOAPUI client for Linux to do that for me. Create a new project, specify the WSDL URL, and have it build the SOAP envelope (and make test calls).

                          Simple example. Using SOAPUI and WDSL URL http://www.webservicex.net/globalweather.asmx?WSDL, I created a new project for getting city weather via PL/SQL web service interface.

                          I selected GetWeather as the web service to call and created a new request for it. The following SOAP envelope is displayed:
                          <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:web="http://www.webserviceX.NET">
                          And this is all that is basically needed for PL/SQL. You can update the envelope in the SOAPUI window and make a call to see what the response from the web service is. But the key is having the SOAP envelope for your PL/SQL code. Thus:
                          SQL> create or replace function GetCityWeather( cityName varchar2, country varchar2 ) return XmlType is
                            2          --// URL to call
                            3          SOAP_URL        constant varchar2(1000) := 'http://www.webservicex.net/globalweather.asmx';
                            5          --// SOAP envelope template, containing $ substitution variables
                            6          SOAP_ENVELOPE   constant varchar2(32767) :=
                            7  '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:web="http://www.webserviceX.NET">
                            8     <soapenv:Header/>
                            9     <soapenv:Body>
                           10        <web:GetWeather>
                           11           <web:CityName>$CITY</web:CityName>
                           12           <web:CountryName>$COUNTRY</web:CountryName>
                           13        </web:GetWeather>
                           14     </soapenv:Body>
                           15  </soapenv:Envelope>';
                           17          --// we'll identify ourselves using an IE9/Windows7 generic browser signature
                           18          C_USER_AGENT    constant varchar2(4000) := 'Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0)';
                           20          --// these variables need to be set if web access
                           21          --// is via a proxy server
                           22          proxyServer varchar2(20) default null;
                           23          proxyUser varchar2(20) default null;
                           24          proxyPass varchar2(20) default null;
                           26          --// our local variables
                           27          soapEnvelope    varchar2(32767);
                           28          proxyURL        varchar2(4000);
                           29          request         utl_http.req;
                           30          response        utl_http.resp;
                           31          buffer          varchar2(32767);
                           32          soapResponse    clob;
                           33          xmlResponse     XmlType;
                           34          eof             boolean;
                           35  begin
                           36          --// create the SOAP envelope
                           37          soapEnvelope := replace( SOAP_ENVELOPE, '$CITY', cityName );
                           38          soapEnvelope := replace( soapEnvelope, '$COUNTRY', country );
                           40          --// our "browser" settings
                           41          utl_http.set_response_error_check( true );
                           42          utl_http.set_detailed_excp_support( true );
                           43          utl_http.set_cookie_support( true );
                           44          utl_http.set_transfer_timeout( 10 );
                           45          utl_http.set_follow_redirect( 3 );
                           46          utl_http.set_persistent_conn_support( true );
                           48          --// configure for web proxy access if applicable
                           49          if proxyServer is not null then
                           50                  proxyURL := 'http://'||proxyServer;
                           51                  if (proxyUser is not null) and (proxyPass is not null) then
                           52                          proxyURL := Replace( proxyURL, 'http://',  'http://'||proxyUser||':'||proxyPass||'@' );
                           53                  end if;
                           54                   utl_http.set_proxy( proxyURL, null );
                           55          end if;
                           57          --// make the POST call to the web service
                           58          request := utl_http.begin_request( SOAP_URL, 'POST', utl_http.HTTP_VERSION_1_1 );
                           59          utl_http.set_header( request, 'User-Agent', C_USER_AGENT );
                           60          utl_http.set_header( request, 'Content-Type', 'text/xml; charset=utf-8' );
                           61          utl_http.set_header( request, 'Content-Length', length(soapEnvelope) );
                           62          utl_http.set_header( request, 'SoapAction', 'http://www.webserviceX.NET/GetWeather' );
                           63          utl_http.write_text( request, soapEnvelope );
                           65          --// read the web service HTTP response
                           66          response := utl_http.get_response( request );
                           67          dbms_lob.CreateTemporary( soapResponse, true );
                           68          eof := false;
                           69          loop
                           70                  exit when eof;
                           71                  begin
                           72                          utl_http.read_line( response, buffer, true );
                           73                          if length(buffer) > 0 then
                           74                                  dbms_lob.WriteAppend(
                           75                                          soapResponse,
                           76                                          length(buffer),
                           77                                          buffer
                           78                                  );
                           79                          end if;
                           81                  exception when utl_http.END_OF_BODY then
                           82                          eof := true;
                           83                  end;
                           85          end loop;
                           86          utl_http.end_response( response );
                           88          --// as the SOAP responds with XML, we convert
                           89          --// the response to XML
                           90          xmlResponse := XmlType( soapResponse );
                           91          dbms_lob.FreeTemporary( soapResponse );
                           93          return( xmlResponse );
                           95  exception when OTHERS then
                           96          if soapResponse is not null then
                           97                  dbms_lob.FreeTemporary( soapResponse );
                           98          end if;
                           99          raise;
                          100  end;
                          101  /
                          Function created.
                          The function is not properly modularised - for a proper production implementation you would have a procedure/function that receives a SOAP envelope and web service details and makes the actual HTTP POST call. Other functions can then create their specific SOAP envelopes and use this procedure/function to make the actual HTTP call.

                          Here's a couple of examples of how this weather function works. Of course, a proper production function should parse the XML and return that as rows and columns - and not as a XML data type.
                          SQL> select GetCityWeather( 'Cape Town', 'South Africa' ) as WEATHER from dual;
                          <?xml version="1.0" encoding="UTF-8"?>
                          <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">
                              <GetWeatherResponse xmlns="http://www.webserviceX.NET">
                                <GetWeatherResult>&lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-16&quot;?&gt;&lt;CurrentWeather&gt;  &lt;Location&gt;Cape Town, Cape Town International Airport, South Africa (FACT) 33-59S
                          018-36E 0M&lt;/Location&gt;  &lt;Time&gt;Jul 11, 2012 - 07:00 AM EDT / 2012.07.11 1100 UTC&lt;/Time&gt;  &lt;Wind&gt; from the SSE (150 degrees) at 16 MPH (14 KT) gusting to 28 MPH (24 KT) (direction
                          variable):0&lt;/Wind&gt;  &lt;Visibility&gt; greater than 7 mile(s):0&lt;/Visibility&gt;  &lt;SkyConditions&gt; mostly cloudy&lt;/SkyConditions&gt;  &lt;Temperature&gt; 59 F (15 C)&lt;/Temperature&gt;
                            &lt;DewPoint&gt; 41 F (5 C)&lt;/DewPoint&gt;  &lt;RelativeHumidity&gt; 51%&lt;/RelativeHumidity&gt;  &lt;Pressure&gt; 30.36 in. Hg (1028 hPa)&lt;/Pressure&gt;  &lt;Status&gt;Success&lt;/Status&gt;&l
                          SQL> select GetCityWeather( 'New York', 'USA' ) as WEATHER from dual;
                          <?xml version="1.0" encoding="UTF-8"?>
                          <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">
                              <GetWeatherResponse xmlns="http://www.webserviceX.NET">
                                <GetWeatherResult>Data Not Found</GetWeatherResult>
                          SQL> select GetCityWeather( 'New York', 'United States' ) as WEATHER from dual;
                          <?xml version="1.0" encoding="UTF-8"?>
                          <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">
                              <GetWeatherResponse xmlns="http://www.webserviceX.NET">
                                <GetWeatherResult>&lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-16&quot;?&gt;&lt;CurrentWeather&gt;  &lt;Location&gt;NEW YORK LA GUARDIA AIRPORT , NY, United States (KLGA) 40-47N 73-53W 11
                          M&lt;/Location&gt;  &lt;Time&gt;Jul 11, 2012 - 06:51 AM EDT / 2012.07.11 1051 UTC&lt;/Time&gt;  &lt;Wind&gt; from the NE (050 degrees) at 8 MPH (7 KT):0&lt;/Wind&gt;  &lt;Visibility&gt; 10 mile(s):0&l
                          t;/Visibility&gt;  &lt;SkyConditions&gt; mostly cloudy&lt;/SkyConditions&gt;  &lt;Temperature&gt; 72.0 F (22.2 C)&lt;/Temperature&gt;  &lt;DewPoint&gt; 69.1 F (20.6 C)&lt;/DewPoint&gt;  &lt;RelativeHu
                          midity&gt; 90%&lt;/RelativeHumidity&gt;  &lt;Pressure&gt; 30.13 in. Hg (1020 hPa)&lt;/Pressure&gt;  &lt;Status&gt;Success&lt;/Status&gt;&lt;/CurrentWeather&gt;</GetWeatherResult>
                          • 10. Re: Calling webservice from PLSQL
                            I had followed the steps, but getting the following error.
                            I can connect to the DB so looks like the issue is not related to loistner.

                            Can someone help

                            ORA-12541: TNS:no listener
                            ORA-06512: at "APPS.GETCITYWEATHER", line 99
                            12541. 00000 - "TNS:no listener"
                            *Cause:    The connection request could not be completed because the listener
                            is not running.
                            *Action:   Ensure that the supplied destination address matches one of
                            the addresses used by the listener - compare the TNSNAMES.ORA entry with
                            the appropriate LISTENER.ORA file (or TNSNAV.ORA if the connection is to
                            go by way of an Interchange). Start the listener on the remote machine.

                            • 11. Re: Calling webservice from PLSQL

                              welcome to the forum.

                              Do not hijack previous posts. If you have a question please open a new thread.

                              Please read SQL and PL/SQL FAQ

                              When you put some code or output please enclose it between two lines starting with {noformat}
                              SELECT ...
                              It's also considered a good practice to mark questions as answered when the answers are satisfying your question or provide additional details.