Pages

Showing posts with label web service. Show all posts
Showing posts with label web service. Show all posts

Thursday, January 21, 2016

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;