Made a SOAP WebService client using Oracle PL/SQL

This simple script allow you to made a webservice client that runs on Oracle database.

• First of all you need to download SOAPUI  https://www.soapui.org/
• Then take the url of the WSDL of the webservice you want to connect, in this example we use http://www.webservicex.net/CurrencyConvertor.asmx?WSDL
• Open a new SOAP project in SOAPUI and try connect to the webservice sending and receiving data, you have to copy the XML code that soapui has generated for you.

• Take the pl/sql script that you find below and change it to call your webservice, you will see the output in the standard output.

declare

    l_response_payload  XMLType;

    l_payload           varchar2(2000);

    l_payload_namespace varchar2(200);

    l_target_url        varchar2(200);

    l_soap_request  varchar2(30000);

    l_soap_response varchar2(30000);

    http_req        utl_http.req;

    http_resp       utl_http.resp;

begin

    --l_payload_namespace := 'http://tempuri.org/';

    l_target_url        := 'http://www.webservicex.net/CurrencyConvertor.asmx';

    l_soap_request      :=  '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 

                               xmlns:web="http://www.webserviceX.NET/">

                               <soapenv:Header/>

                               <soapenv:Body>

                                  <web:ConversionRate>

                                     <web:FromCurrency>USD</web:FromCurrency>

                                     <web:ToCurrency>EUR</web:ToCurrency>

                                  </web:ConversionRate>

                               </soapenv:Body>

                            </soapenv:Envelope>';

    http_req       := utl_http.begin_request(l_target_url,

                                             'POST',

                                             'HTTP/1.1');

                                                                                    

    utl_http.set_header(http_req, 'Content-Type', 'text/xml');

    utl_http.set_header(http_req, 'Content-Length', length(l_soap_request));

    --utl_http.set_header(http_req, 'SOAPAction', p_soap_action);

   

    --

    --Sumit the request and get response

    --

    utl_http.write_text(http_req, l_soap_request); 

    http_resp := utl_http.get_response(http_req);

    utl_http.read_text(http_resp, l_soap_response);

    utl_http.end_response(http_resp);

    -- only return the payload from the soap response - that is: the content of the body element in the SOAP envelope

    l_response_payload :=  XMLType.createXML(l_soap_response) .extract('/soap:Envelope/soap:Body/child::node()',

                                                                       'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"');

    

    --

    --PRINTOUT THE RESPONSE

    --

    dbms_output.put_line(l_response_payload.getStringVal());

    dbms_output.put_line('ConversionRateResult->' ||

    trim(l_response_payload.extract('/ConversionRateResponse/ConversionRateResult/text()',

                                    'xmlns="http://www.webserviceX.NET/"').getStringVal()));

end;

Reference: https://technology.amis.nl/2008/06/16/rapid-plsql-web-service-client-development-using-soapui-and-utl_http/