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

Lucas Jellema 9
0 0
Read Time:11 Minute, 35 Second

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
  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
  l_discount:= 10;
  -- monday deliveries get an extra 15% discount
  if is_monday(p_delivery_date)
      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)
    l_product_discount:= get_hotlist_product_discount;
    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?


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

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.
0 %
0 %
0 %
0 %
0 %
0 %

Average Rating

5 Star
4 Star
3 Star
2 Star
1 Star

9 thoughts on “Publishing PL/SQL "Services" as WebService using Oracle BPEL

  1. Hi Lucas,
    I’m anxious to hear if you performed the next steps you describe, to establish the overhead of using the BPEL engine. What is your conclusion?

  2. Hi ,
    I am new to Oracle BPEL and am trying to achieve the following using BPEL process manager:
    1. Invoke a PL/SQL procedure which takes and input order number and return an XML with :
    a. SOAP Header.
    b. Request:
    b1. Event Id.( One up sequence)
    b2. Version Number.
    b3. Request string:
    b31: Order Number
    b32: Order Status
    b33: Order Status Description
    2. The XML generated above needs to be sent an external system through a web service . The WSDL and the XSD definition for the external web service has been included and setup as a partner link.

    I have successfullt executed the step 1 , but am unable to proceed with the invokation of the external webservice. Please be informed that I have been able to call the web services by invoking a Java procedure from the PL/SQL package. When I try invoking the same through BPEL The same ends with a message :


    Failed get wsdl service definition. Failed to get a WSDL service that support the portType “{}EventSinkService_PortType” in WSDL definition “{}”. Please verify that WSDL portType “{}EventSinkService_PortType” is supported by a service in WSDL file.

  3. I am new to Oracle BPEL and I wanted to follow your example but to me there are gaps and it is assumed that one knows all the steps to take in the BPEL product. For example to do not say what sequence to create the variables for the PartnerLink. I only seem to be able to get Process Variables but your screen shots have Scope Variables. You then say about setting up Assign Invoke Assign activities but the screen shots go straight to the Invoke even though you are talking about the Assign process. It would be easier to follow if you told us (childlike) all the steps that are necessary as I am trying to understand it but am loosing it as certain bits of knowledge seem to be assumed.

  4. I succeeded in using the describtion – so it is good. It has more details than the on line documentation in BPEL about “Stored Procedure and Function Support”. The describtion about the variables were a bit confusing.

  5. You mention that currently you need to manually edit the .wsdl file of the process to modify it’s input (or output) structures. This is not true. There is a much better way:
    1) Define your input (or output) structure as an XML Schema (xsd)
    2) using the structure pane, modify the payload type of the input (or output) to the XML schema, either by adding this schema to your project before, or just adding it when you change the type.
    The OrderBooking Tutorial (which in fact is an excellent new tutorial that leads you through creating a process, adding file and DB adapters and add Human Workflow), does exactly that, changing the input document to a PurchaseOrder, based on an XSD.

  6. Lucas, I agree that WebServices and BPEL are a good combination for publishing and combining services provided by different technologies. From my point, your approach suggested that you *need* BPEL to publish PL/SQL as WebService. This is misleading.

  7. Olaf, thanks for your comment. I now realize I have not been explicit enough in this article: of course this is not the recommended way of publishing a PL/SQL program unit as a WebService! You are quite right in pointing out that if that is the only job at hand, the JDeveloper wizard that publishes a PL/SQL based WebServices (as I describe in some detail in the post Publishing PL/SQL Based WebServices) is far better suited for that task.

    In this article I merely wanted to show how you can include a PL/SQL based service in a BPEL process. The publicstion of the APP_SALES.GET_QUOTE function as webservice certainly would never justify the use of BPEL. However, and that is the point that should have come across, if you are constructing more complex WebServices that build on several, probably technology overspanning, services or APIs and that themselves contain complex flow-logic and exception handlers, than Oracle BPEL PM is good candidate for designing and publishing such services. And this article shows just how easy it is to make a PL/SQL service part of such a WebService.

  8. Lucas, why so much fuss and why not using JDeveloper’s build-in support for PL/SQL WebServices? If I don’t need BPEL, why all this overkill? Just for technology’s sake? No, Sir.

Comments are closed.

Next Post

Analytical Power

While working on a Project for one of our Clients, an interesting requirement popped up. What they wanted was the following: “From this list of dates, we need the ID from the most recent subscriptionâ€?. Here’s a sample from the list: ID, CAT, START_DATE, END_DATE 1, 0, 29-DEC-04, 17-JUL-05 2, […]
%d bloggers like this: