Publishing PL/SQL Based WebServices

This post discusses how a Web Service can be published based on a PL/SQL Package inside the Oracle Database – 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)

There seem to be two very similar ways of publishing a PL/SQL based WebService:

• Create a Java Class 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
• Use the Wizards in JDeveloper to create and publish a WebService directly based on a PL/SQL package. Note that this too will create a Java Server Side Stub that invokes the PL/SQL code and gets published itself as WebService.

Apart from convenience on the one hand and accessibility of (Java)code on the other hand, there is no real distinction between these methods. We will discuss the second approach in this article.

Series on Web Services, PL/SQL and Oracle BPEL Process Manager

This post is dedicated to the first method. This post is part of a series that work towards Oracle BPEL Process Manager and its application in the context of PL/SQL. This is an area where until now Oracle Workflow was used to govern processes. Many people wonder whether Oracle BPEL can and will be used in this area – or whether PL/SQL may become a forgotten country in terms of WebServices and BPEL. In order to link PL/SQL based services into Oracle BPEL, these services must be exposed as WebServices. Note that in a future version of Oracle BPEL there is the intention of providing a short-cut for invoking PL/SQL procedures – that may take away the requirement of a fully worked out webservice. Of course, PL/SQL code may itself make use of WebServices.

The piece of PL/SQL that I want to publish and then redesign 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 Oracle 10g JDeveloper for this.

Open JDeveloper, create a new Workspace (or open an existing Workspace), create a new Project (or use an existing project). Create a connection to the database schema that contains the package APP_SALES. Select that package in the Connections Navigator and open the Right Mouse Button Menu. Select the option: Publish as WebService.

In the Publish PL/SQL WebService wizard, select the Function GetQuote, fill in a proper name for the WebService and Finish.

The wizard will generate the WSDL file and the Java Server Side Stub – that will invoke the PL/SQL package through SQLJ commands. Note that the wizard uses JPublisher behind the scenes. You can now run the WebService locally. When the WebService is running, you can open a Browser and inokve the WebService endpoint, in this case “http://localhost:8989/CurrencyConversionWebService-ViewController-context-root/SalesQuoteWS”:

This call is executed eventually by the PL/SQL code in the database package that is invoked from the Java Stub that was generated by the Publish PL/SQL Web Service Wizard. The result is the following SOAP message:

We have seen how simple it is to Publish a PL/SQL Package as WebService. This means that calling business logic implemented in PL/SQL to external applications may be nothing but calling a WebService – that wrap a Java Stub and a PL/SQL package. Here we see how WebServices act as a bridge between technologies: PL/SQL is called by invoking a WebService which can be done from any technology. Including PL/SQL. Needless to say that there is some overhead between calling a PL/SQL Package directly from PL/SQL and calling the same package through the WebService mechanism. Yet that may be a better solution in certain circumstances: direct PL/SQL call between packages of different applications will not improve the SOA nature of your enterprise architecture.

Resources

Details on publish PL/SQL WebServices from JDeveloper are in the Oracle 10g by Example for JDeveloper tutorial. Step by step details on accomplishing the same using only OC4J are in this article by Jason Price on OTN.

Then there is this Oracle Whitepaper – Database WebServices (from november 2002) on publishing webservices from the database. The Oracle Documentation on the subject – both consuming and publishing webservices from the database – is here.