Calling BPEL from PL/SQL

The UTL_HTTP package
Calling PL/SQL code from BPEL is a walk in the park. But what if you want to do the opposite? Is there a way? Well, fortunately, there is. Oracle 9i/10g comes intact with the UTL_HTTP package that can be used to access data on the Internet over the HTTP protocol. With a little tweaking you can leverage its functionality to call BPEL processes. And I shall show you exactly how to do it.

Declare variables
We declare the following PL/SQL variables

• request_envelope VARCHAR2(30000): This is the SOAP request that will be sent to the BPEL process
• response_envelope VARCHAR2(30000): The response message relayed back by the BPEL process after the request has been successfully served
• http_request utl_http.req: The PL/SQL abstraction of the HTTP request sent to the web server
• http_response utl_http.resp: The PL/SQL abstraction of the HTTP respone delegated to the caller

The SOAP message
Since SOAP is widely recognised as an industry standard to communicate with services avaialble over the internet, your first task is to create the message. For the sake of convinience, I shall assume that there is a synchronous BPEL process in place that accepts a string as input and concatenates the string with 'Hello' - a typical HelloWorld BPEL process. Initialize the request_envelope variable with the SOAP message as shown below

request_envelope :=
'<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Header/>
<soap:Body xmlns:ns1="http://xmlns.oracle.com/HelloWorld">
<ns1:HelloWorldProcessRequest>
<ns1:input>Sankash</ns1:input>
</ns1:HelloWorldProcessRequest>
</soap:Body>
</soap:Envelope>';

BEGIN_REQUEST
BEGIN_REQUEST begins a new HTTP request. When the function returns, the UTL_HTTP package has established the network connection to the target Web server, and has sent the HTTP request line. This function takes three parameters which are

• url : The end-point for the service you wish to invoke. This will typically be the URL of the BPEL process
• method : POST or GET.

The "GET" method is suitable for non-parameterized URLs or for URLs with a manageable volume of parameter name-value pairs. The maximum length of the URL string is limited by the capacity of the PL/SQL VARCHAR2 variable used to pass it.

The "POST" method is suitable for parameterizing the request with an arbitrarily large volume of data, especially for example as might be the case when the request is expressed as an XML document.

• http_version : the version of HTTP like 1.0 or 1.1 etc

In our case this function will look like

http_request :=
utl_http.begin_request(
url => 'http://172.28.0.54:7777/orabpel/default/HelloWorld/1.0', method => 'POST', http_version => 'HTTP/1.1');

Header information
The next step is to set the header information. For this we shall use SET_HEADER function. This function sets a HTTP request header. The request header is sent to the Web server as soon as it is set. The function takes three parameters which are

• r : The http request object
• name : The header name
• value : The header value

In our case we need to set the values for Content-Type, Content-Length and SOAPAction to complete the header. This is done as follows

utl_http.set_header(
r => http_request,
name => 'Content-Type',
VALUE => 'text/xml');

utl_http.set_header(
r => http_request,
name => 'Content-Length',
VALUE => LENGTH(request_envelope));

utl_http.set_header(
r => http_request,
name => 'SOAPAction',
VALUE => 'process');

WRITE_TEXT
This function writes text data in the HTTP request body. As soon as some data is sent as the HTTP request body, the HTTP request headers section is completed. Text data is automatically converted from the database character set to the request body character set. This function takes two parameters

• r : The http request object
• data : The text data that forms the request. In our case this is the SOAP message

This is how we will call this function

utl_http.write_text(r => http_request, data => request_envelope);

GET_RESPONSE
This procedure reads the HTTP response. When this procedure returns, the status line and the HTTP response headers have been read and processed. The status code, reason phrase and the HTTP protocol version are stored in the response record. We shall call this in the following way

http_response := utl_http.get_response(r => http_request);

READ_TEXT
This reads the HTTP response body in text form and returns the output in the caller-supplied buffer. The end_of_body exception will be raised if the end of the HTTP response body is reached. Text data is automatically converted from the response body character set to the database character set. It takes two parameters

• r : the HTTP response object
• data : the text data of the response. In our case this is the SOAP response

We shall use this function in the following way

utl_http.read_line(r => http_response, data => response_envelope);

END_RESPONSE
This ends the HTTP response. This completes the HTTP request and response cycle. The function takes only one parameter which is the HTTP response object.
Use it like this

utl_http.end_response(http_response);

Handle Exceptions
To take care of any inadvertent exceptions that may arise we embed the following exception handling block in our code

EXCEPTION
WHEN utl_http.end_of_body
THEN utl_http.end_response(http_response);
WHEN utl_http.request_failed THEN
DBMS_OUTPUT.PUT_LINE('Request Failed: ' utl_http.get_detailed_sqlerrm);
WHEN utl_http.http_server_error THEN
DBMS_OUTPUT.PUT_LINE('Server Error: ' utl_http.get_detailed_sqlerrm);
WHEN utl_http.http_client_error THEN
DBMS_OUTPUT.PUT_LINE('Client Error: ' utl_http.get_detailed_sqlerrm);
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm);

Gotchas
So far so good. But when you try to read the output you in your PL/SQL code you are almost certain to get this error, because in most of the cases the output from the BPEL process will be verbose.

ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line

This is because DBMS_OUTPUT.PUT_LINE can write a maximum of 255 characters in one line as the error says. Thus you will have to break the output into multiple lines. To get round this problem I shall use the following piece of code. This restricts the number of characters per line to 255. Extra characters are passed onto the next line.

FOR i IN 1 .. MOD(LENGTH(response_envelope), 255)
LOOP
DBMS_OUTPUT.PUT_LINE(SUBSTR(response_envelope, j, 255));
j := j + 255;
END LOOP;

That is all. Check the BPEL console to ensure that the process was successfully initiated.