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