Publishing PL/SQL "Services" as WebService using Oracle BPEL

Oracle BPEL Process Manager is a BPEL engine that runs in any J2EE Application Server, though typically Oracle 10gAS (OC4J), JBoss, WebLogic or WebSphere. Oracle BPEL PM publishes business processes in the form of WebServices. These business processes can be very complex BPEL definitions, composed of many steps, service invocations, loops and decisions points. However, these processes can also be extremely simple, entailing no more than a single service call. The services called from the BPEL process ran by the BPEL engine can be WebServices themselves, or – through WSIF or the adaptors shipped with Oracle BPEL PM – services implemented in Java or PL/SQL or services that effectively poll a Database Table, an FTP server or the File system for events to happen. This post discusses how a Web Service can be published based on a PL/SQL Package inside the Oracle Database. This very same subject was described in a previous post – Publishing PL/SQL Based WebServices – where we achieved the WebService publication using Oracle 10g JDeveloper.

In this article we will create a PL/SQL based WebService based on the Package we discussed in previous posts Consuming Web Services from PL/SQL – Part I: Using Java Stored Procedures and Consuming Web Services from PL/SQL – Part II: A pure PL/SQL solution using UTL_HTTP (Oracle 9i or 10g).

The more or less traditional method of publishing a PL/SQL based WebService is something like:

create a Java Class – a proxy for the PL/SQL service – with a static method that will use JDBC to invoke the PL/SQL prcoedure to be published. Create a WebService for this JavaClass and publish it; indirectly you will have published the PL/SQL package. If you use the Wizards in JDeveloper to create and publish a WebService directly based on a PL/SQL package, something like this will happen, though not as explicitly.

Using the Oracle BPEL Engine to publish a PL/SQL Service as WebService may seem like a long haul to achieve the same thing. It has at least two points to take good notice of: you will need a license for Oracle BPEL Process Manager – not cheap! – and there is some overhead involved with running the service as a BPEL process. I am not yet sure how much overhead there is and for a service that is not called with very high frequency this second point may be neglible. If you already have the license, the first point is moot as well. If you don’t, this whole article is moot; you would not want to even consider buying Oracle BPEL PM only for publishing PL/SQL based services.

On the up-side, using Oracle BPEL PM for publishing a PL/SQL based WebService has some distinct advantages

• It is the easiest thing I have seen in terms of development and deployment of the WebService
• Oracle BPEL PM gathers statistics on execution of the process. Without any action on your part, you will have full analysis of the number of calls to the service, the parameters used in calling it, the time taken for each execution and the possible exceptions that occurred
• Extending the Service with exception handling – for example to deal with erroneous parameter values or the database not being available – is very simple
• Changing the implementation of the Service itself is quite simple and completely transparant to the WebService consumers; using a different database, a different technology or changing the functionality of the service itself, are all easily accomplished using Oracle BPEL Designer and Oracle BPEL Process Manager

Note: I had substantial help from André en Hans for providing the foundation to build this article on!

Introducing the PL/SQL Service

The piece of PL/SQL that I want to publish using BPEL is the following:

function get_quote
( p_product_id in number
, p_quantity  in number
, p_delivery_date in date
, p_currency in varchar2 default 'eur'
) return number
is
  l_discount number(3); -- discount percentage
  l_date_discount number(3); -- discount percentage because of the delivery date (deliveries on Monday are cheaper)
  l_product_discount number(3); -- discount percentage - some products are heavily pushed
  l_standard_price number(10,2); -- price as calculated in euros without applying any discounts
  l_price number(10,2); -- price after applying discounts
begin
  l_discount:= 10;
  -- monday deliveries get an extra 15% discount
  if is_monday(p_delivery_date)
  then
      l_date_discount := 15;
  end if;
  -- a special discount rate is used for products on the hotlist; if the product is not on the hotlist,
  -- then the standard discount rate applies that is used for all products ordered through the webservice
  if is_on_hotlist(p_product_id)
  then
    l_product_discount:= get_hotlist_product_discount;
  else
    l_product_discount:= get_standard_product_discount;
  end if;
  l_discount:= l_discount + l_date_discount + l_product_discount;
  l_standard_price:= p_quantity * get_product_price( p_product_id);
  -- calculate the price as the standard_price times the discount-percentage
  l_price:= l_standard_price * (100 - l_discount)/100;
  -- price is calculated in Euros; the quote may hvan been requested in a different currency (p_currency)
  -- and must be converted in that case
  l_quote:= l_price * get_conversion_rate('eur', p_currency);
  return l_quote;
end get_quote;

Note that this piece of PL/SQL is implemented in a package APP_SALES that uses three other packages – APP_CURRENCY, APP_UTILS and APP_PRODUCT. The source code for these packages can be found here:See here the listing for the four packages: app_utils, app_product, app_currency and app_sales.

Publishing a PL/SQL Business as WebService

The function APP_SALES.get_quote should be published as WebService. I will use the Oracle BPEL Designer that runs inside Oracle 10g JDeveloper (10.1.2) for this. The steps are as follows:

• Create a new BPEL Project in Oracle BPEL Designer (JDeveloper or Eclipse, on our case JDeveloper 10.1.2)
• Add a PartnerLink for the Database PL/SQL Function using the Adapter Wizard
• Define variables
• Create the BPEL Process steps
• Deploy and test the BPEL process

After deploying the BPEL Process, the runtime environment looks as follows:

The GetQuote WebService runs inside the BPEL Engine. It can be invoked from outside our organisation by customers etc. through normal HttpRequests carrying SOAP messages. The GetQuote service does nothing more than copy the input parameters to the input parameters used when invoking the database package app_sales, more specifically its get_quote function. The app_sales package runs inside an Oracle database. It calls other packages – app_currency, app_util and app_product – to provide other services. App_currency uses an external WebService to implement its get_conversion_rate function.

Create a new BPEL Project

Open Oracle BPEL Designer (or effective JDeveloper 10.1.2 with BPEL Designer installed), create a new Workspace (or open an existing Workspace), create a new BPEL Project (in the New Gallery, under General – Projects, there should be an option BPEL Process Project; if there is not, make sure that the Filter By elements is set to All Items rather than Available Items).

Create PartnerLink for PL/SQL Function

In the BPEL Process, create a new PartnerLink, using the Adapter Wizard to create a PartnerLink for the APP_SALES.get_quote PL/SQL functions using the Oracle BPEL Database Adapter. Rightmouse click in the swimlane with the heading PartnerLinks and select the option Create Partner Link. This popup opens:

Click on the little icon depicting a database to open the Adapter Configuration Wizard.

Select the option database adapter. Specify the name and description of the new PartnerLink:

Select (and if necessary first create) a database connection to the schema that holds (a synonym to) the database function we will publish through this webservice:

Select the option Call a Stored Procedure or Function:

Select the desired stored program unit:


Click Finish to complete the wizard. Oracle BPEL Designer will now create a WSDL file for this Adapter Service – a WebService-style description of the PL/SQL Function to be published. Along with this WSDL file, it will create an XSD, describing the input and output to this “service”.

It is useful to take a look at the XSD file created for our Adapter Service, since we can use some of its contents for editing the WSDL file for the BPEL Process – as we need to specify the structure of the Input and Output of our WebService:

This next step is the one that I struggled with the most. We need to specify that our WebService requires a ‘complex input type’. That means: the WebService requires more than a simple string as its input. And we need to indicate later on how the complex input is mapped onto the parameters fed into the Adapter Service for the PL/SQL Function. Specifying the structure of the BPEL Process input seems not to be supported by a wizard or some other visual editor: we need to directly manipulate the WSDL file for the BPEL Process:

Here you can see how we edit the Type definition for the GetQuoteProcessRequest, the input of this service. By copying, pasting and refining from the XSD created by the Adapter Service Configuration Wizard, it is fairly simple to specify the structure of the complex type we need to feed into this WebService.

Create Variables

Now is a good time to define the variables we will require in our BPEL Process. Apart from the Input and Output of the Process, we will need variables to put into the PartnerLink as well as get out of the PartnerLink. From the Variables window, we create two new variables, based on the MessageTypes defined in the PartnerLink getQuotePlsqlService.

Define process steps: Assign, Invoke, Assign

The BPEL process will consist of only a few steps: receiveInput and replyOutput – which are default steps – to get started with a call from and return the result to the WebService client. We add an Assign step to copy the proper components from the Input to the variable used with invoking the PartnerLink, a step to invoke the PartnerLink and another Assign step to retrieve the relevant components from the output of the PartnerLink into the proper sections of the Output to be returned to the client.

First add the Assign process step:

Configure the Assign Step by specifying that the constituent components of the inputVariable should be copied to specific parts of the getQuoteInput variable:

In the same way we edit the second Assign step, where we need to specify how we copy part of the output of the PartnerLink to the outputVariable:

Finally, we can create the Invoke step and have it invoke the parterlink. We have already set up the input variable and output variable for this invoke-step and we have made sure through the Assign steps that the proper data is copied.

Deploying the BPEL Process

Now that we have complete defined the BPEL process, we can deploy it the Oracle BPEL PM Engine we have already running. We can make use of the Deployment tool that is included with Oracle BPEL PM Designer:

The default domain’s password is bpel. When we deploy, the BPEL process is validated and compiled and packaged in a JAR that is then uploaded to the BPEL Engine.

Testing the BPEL Process from the Web Console

Now it is time to eat the pudding. Open the BPEL Console – remember: currently only supported in the Internet Explorer – to test the BPEL Process from the BPEL Console. Login to the default domain (default password bpel). The list of Processes should now include the just deployed getQuote process:

When we go to the Initiate Tab of the getQuote process page, we can start a new instance of the GetQuote BPEL Process – or run a load test on a zillion of them. The BPEL console presents us with HTML Form fields for all elements in the ClientInputRequestMessage that were defined some time ago in the WSDL file for this webservice: product_id, quantity, delivery_date and currency.

After we have posted the request, we have to wait for a little while before we can find the results of executing this process instance. The Audit tab provides all details of the process: how long each step took, what data was fed into each step and what the results of executing each intermediate process step were. Note: in the Debug Tab we can find a detailed overview of process execution associated with the WebService’s WSDL-file.

We may conclude that we have successfully deployed our PL/SQL function APP_SALES.GET_QUOTE as a WebService, using the Oracle BPEL PM engine to host and facilitate that service. We can invoke the service, from the console as well as from any other client.

Next Steps

I would like to establish just how much the overhead is of using the Oracle BPEL PM Engine for running the PL/SQL Service. So I should publish it in the conventional way, invoke it 10000 times and also invoke the BPEL process 10000 times. Comparing the timings for these load-tests should give a good feel for the overhead.

Another question I have: should I publish the BPEL Process based on the PL/SQL Service directly, or would it be better to consider that BPEL process an internal building block and instead base another BPEL process on it – and only publish that second process? And if I were to do that – what is the additional overhead of that extra BPEL process put in between?

Resources

Useful posts on the AMIS Technology Weblog on Oracle BPEL Process Manager:

• Book review: Business Process Execution Language – with a link to a free bookchapter (50 pages) on Oracle BPEL PM (release 2.x)
• More on BPEL – report from two weeks of investigations into Oracle BPEL PM 10g Beta 2
• First impression of Oracle jDeveloper BPEL Beta2
• BPEL-Soft – Oracle’s previous acquisition showcased during AMIS Query