How to invoke a web service from PL/SQL

Usecase: Invoke a webservice from PL/SQL Procedure,

Below is the sample code to invoke BPEL SOA process from PL/SQL stored procedure. Have this stored procedure defined in database.



Create or replace procedure TestBpelInvoke is
    soap_request  varchar2(3000);
    soap_respond  varchar2(3000);
    http_req   utl_http.req;
    http_resp   utl_http.resp;
    resp   xmltype;
    l_xsl_nonamespace  varchar2(1000) := null;
    i    number;
    l_error_loc         number;
    call_error          exception;
    endpoint_url varchar2(1000);
    response_value varchar2(100);
BEGIN
soap_request := '<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
    <soap:Body>
        <ns1:process xmlns:ns1="http://xmlns.oracle.com/BPELInvokeExample/BpelInvokeExample/BPELInvoke">
            <ns1:input1>2</ns1:input1>
            <ns1:input2>3</ns1:input2>
        </ns1:process>
    </soap:Body>
</soap:Envelope>';
endpoint_url := 'http://192.168.1.142:8001/soa-infra/services/default/BpelInvokeExample/bpelinvoke_client_ep';
  http_req:= utl_http.begin_request(endpoint_url,'POST','HTTP/1.0');
  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, 'SOAPAction', 'process');
  utl_http.write_text(http_req, soap_request) ;
 
 
  http_resp:= utl_http.get_response(http_req) ;
 

  utl_http.read_text(http_resp, soap_respond) ;
  utl_http.end_response(http_resp) ;
 resp := XMLType.createXML(soap_respond);
   l_xsl_nonamespace := '<?xml version="1.0" encoding="UTF-8" ?>
  <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      <xsl:template match="comment()|processing-instruction()|/">  
      <xsl:copy>    
          <xsl:apply-templates/>  
      </xsl:copy>
      </xsl:template>
      <xsl:template match="*">  
      <xsl:element name="{local-name()}">    
          <xsl:apply-templates select="@*|node()"/>  
      </xsl:element>  
      </xsl:template>
      <xsl:template match="@*">  
          <xsl:choose>    
             <xsl:when test="name() != ''xmlns''">      
               <xsl:attribute name="{local-name()}">        
                 <xsl:value-of select="."/>      
               </xsl:attribute>    
             </xsl:when>  
          </xsl:choose>
      </xsl:template>
  </xsl:stylesheet>';
 
  SELECT XMLTransform(resp, xmlType(l_xsl_nonamespace)) into resp from dual;
 
  resp := resp.extract( '/'||'BPELInvoke'||'ProcessResponse/result/child::node()');
  if (resp is null) then
     response_value := '';
  else
     response_value := replace(replace(replace(resp.getStringVal(), '<', '<'), '>', '>'), '"', '"');
dbms_output.put_line('Response Value:'||response_value);
end if;
end;