Consuming Web Services from PL/SQL

create or replace procedure      XXSOA_SOAP_REQUEST     ( P_URL          in  varchar2
                                 , P_SOAP_REQUEST in  clob
                                 , p_proxy        IN  VARCHAR2
                                 , x_response     OUT CLOB)
IS
   lb_debug      BOOLEAN := TRUE;
   soap_request  CLOB;
   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);
   Pv_error      VARCHAR2(3000);
   l_chunkData   VARCHAR2(32766);
   l_chunkLength NUMBER:=32766;
   l_chunkstart  number:=1;
 
BEGIN
   DBMS_OUTPUT.PUT_LINE('XSR-001a : Begin Request');
    http_req:= utl_http.begin_request( p_url,'POST', 'HTTP/1.1');  --begin_request
    utl_http.set_header(http_req, 'Content-Type', 'text/xml');
    utl_http.set_header(http_req, 'Content-Length', length(p_soap_request));
    utl_http.set_header(http_req, 'initiate', ''); -- header requirements of particular web service
    --

LOOP
l_chunkData := null;
        l_chunkData := SUBSTR(p_soap_request, l_chunkStart, l_chunkLength);
        utl_http.write_text(http_req, l_chunkData);
        if (length(l_chunkData) < l_chunkLength)
        then     
        NULL;
          exit;
        end if;
        l_chunkStart := l_chunkStart + l_chunkLength;
END LOOP;
DBMS_OUTPUT.PUT_LINE('XSR-002 : After Write Text');
http_resp:= utl_http.get_response(http_req);
    DBMS_OUTPUT.PUT_LINE('XSR-003 : After Get Response');
    utl_http.get_header_by_name(http_resp, 'Content-Length', v_len, 1); -- Obtain the length of the response
    DBMS_OUTPUT.PUT_LINE('XSR-004 : After get header by name');
    --
FOR i in 1..CEIL(v_len/32767) -- obtain response in 32K blocks just in case it is greater than 32K
    LOOP
        DBMS_OUTPUT.PUT_LINE('XSR-005 : in for loop to read text');
        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
        DBMS_OUTPUT.PUT_LINE('XSR-006 : in for loop after read text');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('XSR-007 : Out of Loop read text');
    utl_http.end_response(http_resp);
    DBMS_OUTPUT.PUT_LINE('XSR-008 : After end response'  );
    resp:= XMLType.createXML(soap_respond); -- Convert CLOB to XMLTYPE
    DBMS_OUTPUT.PUT_LINE('XSR-009 : Afer createXML');
    x_response := soap_respond;
    DBMS_OUTPUT.PUT_LINE('XSR-010 : End of Procedure');
  EXCEPTION
  WHEN UTL_HTTP.REQUEST_FAILED THEN
      Pv_error := SUBSTR('Request_Failed 1: ' || SUBSTR(Utl_Http.Get_Detailed_Sqlerrm, 1, 282), 1, 300);
      x_response :=  Pv_error||' HTTP ERORR '||'---ERROR=='||SQLERRM;
 
    WHEN UTL_HTTP.Http_Server_Error THEN
      Pv_error := SUBSTR('Http_Server_Error 1: ' ||  SUBSTR(Utl_Http.Get_Detailed_Sqlerrm, 1, 282), 1, 300);
      x_response :=  Pv_error||' HTTP ERORR '||'---ERROR=='||SQLERRM;
 
    WHEN UTL_HTTP.Http_Client_Error THEN
      Pv_error := SUBSTR('Http_Client_Error: 1' ||  SUBSTR(Utl_Http.Get_Detailed_Sqlerrm, 1, 282), 1, 300);
      x_response :=  Pv_error||' HTTP ERORR '||'---ERROR=='||SQLERRM;
 
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('XSR-ERR : Error Occured in SOAP Request call : ' || sqlerrm);
      X_RESPONSE :=  PV_ERROR||' HTTP ERORR '||'---ERROR=='||SQLERRM;
END XXSOA_SOAP_REQUEST;


--=================================================================
create or replace procedure XXSOA_SOAP_REQUEST     ( P_URL          in  varchar2
                                 , P_SOAP_REQUEST in  clob
                                 , p_proxy        IN  VARCHAR2
                                 , x_response     OUT CLOB)
IS
   lb_debug      BOOLEAN := TRUE;
   soap_request  CLOB;
   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);
   Pv_error      VARCHAR2(3000);
 
   req_length binary_integer;
   offset pls_integer:=1;
   amount pls_integer:=2000;
   buffer varchar2(2000);
 
BEGIN
   DBMS_OUTPUT.PUT_LINE('XSR-001a : Begin Request');
   http_req:= utl_http.begin_request( p_url,'POST', 'HTTP/1.1');  --begin_request
    utl_http.set_header(http_req, 'Content-Type', 'text/xml');
 
    --req_length:=length(p_soap_request);
 
    utl_http.set_header(http_req, 'initiate', ''); -- header requirements of particular web service
 
    if length(p_soap_request)<=32767 then
        utl_http.set_header(http_req, 'Content-Length', length(p_soap_request));
        utl_http.write_text(http_req, to_clob(p_soap_request));
    else
        utl_http.set_header(http_req, 'Transfer-Encoding', 'Chunked');
     
        while(offset < length(p_soap_request))
        loop     
            dbms_lob.read(p_soap_request,amount,offset,buffer);
            utl_http.write_text(http_req,buffer);
            offset:=offset+amount;     
        end loop;             
    end if;
--    UTL_HTTP.set_transfer_timeout (180);
    DBMS_OUTPUT.PUT_LINE('XSR-002 : After Write Text');
    http_resp:= utl_http.get_response(http_req);
    DBMS_OUTPUT.PUT_LINE('XSR-003 : After Get Response');
    utl_http.get_header_by_name(http_resp, 'Content-Length', v_len, 1); -- Obtain the length of the response
    DBMS_OUTPUT.PUT_LINE('XSR-004 : After get header by name');
 
    FOR i in 1..CEIL(v_len/32767) -- obtain response in 32K blocks just in case it is greater than 32K
    LOOP
        DBMS_OUTPUT.PUT_LINE('XSR-005 : in for loop to read text');
        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
        DBMS_OUTPUT.PUT_LINE('XSR-006 : in for loop after read text');
    END LOOP;
 
    DBMS_OUTPUT.PUT_LINE('XSR-007 : Out of Loop read text');
    utl_http.end_response(http_resp);
    DBMS_OUTPUT.PUT_LINE('XSR-008 : After end response'  );
    resp:= XMLType.createXML(soap_respond); -- Convert CLOB to XMLTYPE
    DBMS_OUTPUT.PUT_LINE('XSR-009 : Afer createXML');
    x_response := soap_respond;
    DBMS_OUTPUT.PUT_LINE('XSR-010 : End of Procedure');
  EXCEPTION
  WHEN UTL_HTTP.REQUEST_FAILED THEN
      Pv_error := SUBSTR('Request_Failed 1: ' || SUBSTR(Utl_Http.Get_Detailed_Sqlerrm, 1, 282), 1, 300);
      x_response :=  Pv_error||' HTTP ERORR '||'---ERROR=='||SQLERRM;
 
    WHEN UTL_HTTP.Http_Server_Error THEN
      Pv_error := SUBSTR('Http_Server_Error 1: ' ||  SUBSTR(Utl_Http.Get_Detailed_Sqlerrm, 1, 282), 1, 300);
      x_response :=  Pv_error||' HTTP ERORR '||'---ERROR=='||SQLERRM;
 
    WHEN UTL_HTTP.Http_Client_Error THEN
      Pv_error := SUBSTR('Http_Client_Error: 1' ||  SUBSTR(Utl_Http.Get_Detailed_Sqlerrm, 1, 282), 1, 300);
      x_response :=  Pv_error||' HTTP ERORR '||'---ERROR=='||SQLERRM;
 
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('XSR-ERR : Error Occured in SOAP Request call : ' || sqlerrm);
      X_RESPONSE :=  PV_ERROR||' HTTP ERORR '||'---ERROR=='||SQLERRM;
END XXSOA_SOAP_REQUEST;