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;

Problem while opening Oracle SOA EM Login page

If you see below Error 404 page while trying to access localhost:7001/em,

Error 404--Not Found
From RFC 2068 Hypertext Transfer Protocol -- HTTP/1.1:10.4.5 404 Not Found

The reason might be you haven't configured 'Enterprise Manager' template while configuring your SOA Domains.

Try following below steps and it should resolve the problem

To add EM to your existing domain do the following steps.
1. Stop all the servers(Includes Admin, Managed servers)
2. Invoke the config wizard. (SOA_HOME\wlserver_10.3\common\bin\config.cmd)
3. Choose Extend Domain option. Select your domain.
4. Choose EM template.
5. Complete the wizard.
6. Start the servers.



Hope this helps!!!