Calling a BPEL process from RAW PLSQL

There is a "nice" website for calling WS out of the database. The page is a bit complex, but it could work. I have spend some time on this, but... at the end I succeed (sse this article). But for those who want to build their own low level SOAP request read further.

So I choose to write a low level SOAP call from PLSQL.

We are using the UTL_HTTP database package and the XMLTYPE of the database. We are "hardcoding" the SOAP request for the server.

The following PLSQL function calls a synchronous BPEL process:

FUNCTION GetHelloWorldPayload
(
p_Payload       IN VARCHAR2
)
RETURN VARCHAR2
IS
soap_request VARCHAR2(30000);
soap_respond VARCHAR2(30000);
http_req     UTL_HTTP.REQ;
http_resp    UTL_HTTP.RESP;
resp         XMLTYPE;
response     VARCHAR2(30000) := '';
l_detail      VARCHAR2(30000);
i integer;
--
namespace VARCHAR2(128) :=
   'xmlns="http://oracle.nl/HelloWorldPayload"';
endpoint  VARCHAR2(128) :=
   'http://host.nl:7779/orabpel/default/HelloWorldPayload';
BEGIN
IF ((p_DefaultDetail is null) or length(p_DefaultDetail) = 0)
THEN
  l_detail := '';
ELSE
  l_detail := p_DefaultDetail;
END IF;
soap_request:= '<?xml version = "1.0" encoding = "UTF-8"?>'
||'<SOAP-ENV:Envelope '
||'xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"'
||'xmlns:xsi="http://www.w3.org/1999/XMLSchema-instance"'
||'xmlns:xsd="http://www.w3.org/1999/XMLSchema">'
||'<SOAP-ENV:Body>'
||'<HelloWorldProcessRequest '||namespace||'>'
||'<input '||namespace||'>'||p_Payload||'</input>'
||'</HelloWorldProcessRequest>'
||'</SOAP-ENV:Body>'
||'</SOAP-ENV:Envelope>';
--
http_req:= utl_http.begin_request
(
  endpoint
, 'POST'
, 'HTTP/1.1'
);
utl_http.set_header(http_req
  , 'Content-Type'
  , 'text/xml'); -- dealing with plain text in XML documents
utl_http.set_header(http_req
  , 'Content-Length'
  , lengthb(soap_request));
utl_http.set_header(http_req
  , 'SOAPAction'
  , 'process'); -- required to specify a SOAP communication
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);
IF (instr(resp.getStringVal(), 'ERROR:') > 0)
THEN
  raise_application_error (
     -20999,
     'GetHelloWorldPayload: Failed! '||p_Payload);
END IF;
resp := resp.extract(
    '/soap:Envelope/soap:Body/child::node()'
   , 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'
   );
-- Remove namespaces
SELECT XMLTransform(resp,
        xmlType(l_xsl_nonamespace)) into resp from dual;
--                       
resp := resp.extract(
'/HelloWorldPayloadProcessResponse/
HelloWorldPayload/child::node()');
--
if (resp  is null)
then
  dbms_output.put_line('GetHelloWorldPayload: resp3 IS NULL');
else
  dbms_output.put_line('GetHelloWorldPayload: resp3 '
                       ||resp.getStringVal());
end if;
--
i:=0;
LOOP                 
  dbms_output.put_line(substr(soap_respond,1+ i*255,250));
  i:= i+1;
  IF i*250> length(soap_respond)
  THEN
    EXIT;
  END IF;
END LOOP;
IF (resp  is null)
THEN
  response := '';
ELSE
  response :=
    replace(
      replace(
        replace(
          resp.getStringVal(), '<', '<')
        , '>', '>')
      , '"', '"');
END IF;
return response;
END GetHelloWorldPayload;
/

Note l_nonamespace is as follows:

l_xsl_nonamespace VARCHAR2(640) :=
l_xsl_nonamespace VARCHAR2(640) := '<?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>';