Calling web service from Database using PLSQL

Part-1: Compile the below procedure & execute as you have required.

CREATE OR REPLACE PROCEDURE p_soap_request(p_username IN VARCHAR2, p_password IN VARCHAR2, p_proxy IN VARCHAR2) 

 IS 

  soap_request VARCHAR2(30000); 

  soap_respond CLOB; 

  http_req   utl_http.req; 

  http_resp   utl_http.resp; 

  resp     XMLType; 

  soap_err   exception; 

  v_code    VARCHAR2(200); 

  v_msg     VARCHAR2(1800); 

  v_len     number; 

  v_txt     Varchar2(32767); 

 BEGIN 

   UTL_HTTP.SET_PROXY(p_proxy); 

   -- Define the SOAP request according the the definition of the web service being called 

   soap_request:= '<?xml version = "1.0" encoding = "UTF-8"?>'|| 

           '<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">'|| 

           ' <SOAP-ENV:Body>'|| 

           '  <m:DownloadRequest xmlns:m="http://www.website.net/messages/GetDetails">'|| 

           '   <m:UserName>'||p_username||'</m:UserName>'|| 

           '   <m:Password>'||p_password||'</m:Password>'|| 

           '  </m:DownloadRequest>'|| 

           ' </SOAP-ENV:Body>'|| 

           '</SOAP-ENV:Envelope>'; 

   http_req:= utl_http.begin_request 

        ( 'http://www.website.net/webservices/GetDetailsService.asmx' 

        , 'POST' 

        , 'HTTP/1.1'); 

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

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

   utl_http.set_header(http_req, 'Download', ''); -- header requirements of particular web service 

   utl_http.write_text(http_req, soap_request); 

   http_resp:= utl_http.get_response(http_req); 

   utl_http.get_header_by_name(http_resp, 'Content-Length', v_len, 1); -- Obtain the length of the response 

   FOR i in 1..CEIL(v_len/32767) -- obtain response in 32K blocks just in case it is greater than 32K 

   LOOP 

     utl_http.read_text(http_resp, v_txt, case when i < CEIL(v_len/32767) then 32767 else mod(v_len,32767) end); 

     soap_respond := soap_respond || v_txt; -- build up CLOB 

   END LOOP; 

   utl_http.end_response(http_resp); 

   resp:= XMLType.createXML(soap_respond); -- Convert CLOB to XMLTYPE 

 Exception 

  when others then null; 

 END; 

Execute the above procedure as below:

Begin  

   p_soap_request(p_username =>'' ,p_password =>'' ,p_proxy =>'' );  

End;

Part-2: XML parsing after getting web service call (Assume that you know the expected output of XML).

Declare  

  response_env   varchar2(32767);  

  v_xml       XMLTYPE;  

  l_user_first_name varchar2(1000);  

  l_user_last_name varchar2(100);  

  l_error_value   varchar2(100);  

  ns        varchar2(200) := 'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:axis2ns1="http://www.test.com/services"';  

 Begin  

  response_env := '<?xml version="1.0" encoding="utf-8"?><soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">  

  <soapenv:Body>  

  <UserInfoObjResponse xmlns:axis2ns2="http://www.test.com/services">  

  <axis2ns2:FirstName >First Name Output</axis2ns2:FirstName>  

  <axis2ns2:LastName>Last Name Output</axis2ns2:LastName>  

  <axis2ns2:ErrorDescription/>  

  </UserInfoObjResponse>  

  </soapenv:Body>  

  </soapenv:Envelope>';  

  v_xml := XMLTYPE(response_env);  

  select EXTRACTVALUE(v_xml,'/soapenv:Envelope/soapenv:Body/UserInfoObjResponse/axis2ns1:FirstName',ns),  

      EXTRACTVALUE(v_xml,'/soapenv:Envelope/soapenv:Body/UserInfoObjResponse/axis2ns1:LastName',ns),  

      EXTRACTVALUE(v_xml,'/soapenv:Envelope/soapenv:Body/UserInfoObjResponse/axis2ns1:ErrorDescription',ns)  

   into l_user_first_name, l_user_last_name, l_error_value  

   from dual;  

  dbms_output.put_line('l_user_first_name: ' || l_user_first_name);  

  dbms_output.put_line('l_user_last_name : ' || l_user_last_name);  

 End;