Friday, October 20, 2006

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>';


Post a Comment