Consuming Web Services from PL/SQL – Part I: Using Java Stored Procedures

This post discusses how a Web Service can be consumed from PL/SQL inside the Oracle Database through the use of Java Stored Procedures. In this article, we will create a Java Client for the Web Service we want to use from PL/SQL, then load this Java Client in the Oracle Database and wrap it in a PL/SQL wrapper.

There seem to be three methods for consuming Web Services from PL/SQL:

• Use the UTL_HTTP supplied package to communicate directly with the WebService and send and retrieve entire SOAP messages
• Develop Java Classes – preferably using the Web Service Wizards in JDeveloper – that consume the WebService and load these into the database wrapped with a PL/SQL package interface; this interface can be called like any normal PL/SQL procedure call
• Make use of the UTL_DBWS supplied package that allows generic WebService consumption from within PL/SQL without the need to create and load Java classes or write entire SOAP messages from within PL/SQL

In subsequent posts I will discuss the other two methods; see Consuming Web Services from PL/SQL – Part II: A pure PL/SQL solution using UTL_HTTP (Oracle 9i or 10g)

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

This post is dedicated to the second 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. All in all I want to show three things:

• PL/SQL code consuming a webservice
• PL/SQL code published as webservice
• Use of Oracle BPEL Process Manager to redesign and reimplement a piece of PL/SQL code

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

One of the components required for implementing this logic is the function get_conversion_rate. This is a PL/SQL function that derives it functionality from a WebService that provides Currency Conversion or better said: Currency Exchange Rates, for example: XMethods Currency Exchange Rates (see Tutorial for an example of using this webservice). To try this web service, see http://www.mindreef.net/tide/scopeit/start.do?referer=xmethods&url=http://www.xmethods.net/sd/2001/CurrencyExchangeService.wsdl

We need to expose a PL/SQL Function that internally invokes this WebService to obtain the Currency Exchange Rate. In this post, we will use a WebService Client written in Java that is uploaded into the Oracle Database as Java Stored Procedures and wrapped in a PL/SQL wrapper.

Consuming a WebService in PL/SQL using a Stored Java WebService Client

The first step towards development of a Stored Java Web Service client, is the creation of a normal Java Client for the Web Service. Using JDeveloper 10g, this is an extremely simple process. The steps are as follows:

• Create a new Application Workspace and Project (CurrencyConversionWebService)
• New Gallery, Business Tier – WebServices, Web Service Stub/Skeleton
• Enter the URL for the WSDL in the Wizard: http://www.xmethods.net/sd/2001/CurrencyExchangeService.wsdl
• Finish the Wizard; the class CurrencyExchangeServiceStub gets generated

Try out the new Java Client:

 public static void main(String[] args) {
    try {
      CurrencyExchangeServiceStub stub = new CurrencyExchangeServiceStub();
      // Add your own code here.
      System.out.println(stub.getRate("us","uk"));
    } catch(Exception ex) {
      ex.printStackTrace();
    }
  }

The result: 0.5371. This proves we have a functional WebService Client

Note: during my prototyping I ran into the JDeveloper Proxy Server Setting: see Menu Tools, Preferences, Web Browser and Proxy. I had the wrong Proxy Server configured and when I tried to invoke the WebService, it failed because of it.

Deploying the WebService Client as Stored Java Procedure

Before we can load this Java based Web Service Client into the database, we need to perform several prepatory steps. We need a schema with proper system privileges and we need some Stored Java privileges that allow us to call out from the database in our Stored Java Classes.

prepare Database Schema:

• Create Database Schema WS
• Grant the required system privileges to WS:

  GRANT CREATE SESSION
  ,     CREATE TABLE
  ,     CREATE TRIGGER
  ,     CREATE SEQUENCE
  TO    ws;
  

• ensure that the database schema into which the WebService Client Classes are uploaded has the proper privileges to execute the Java code and connect through the relevant port (80) to a web service url; execute the following code as SYS and replace WS with the schema you want to use yourself:

  begin
    dbms_java.grant_permission( 'WS', 'SYS:java.net.SocketPermission', 'services.xmethods.net:80', 'connect, resolve');
    dbms_java.grant_permission('WS','SYS:java.util.PropertyPermission','*','read,write');
    dbms_java.grant_permission( 'WS', 'SYS:java.lang.RuntimePermission', 'getClassLoader', '' ) ;
  end;

  note: This step is easily overlooked and that cost me a lot of time!

• load the WebServices Client into the database, as described in Loading the WebServices Client Stack into the Oracle Database: Install SOAP Client Stack in Oracle Database; this action entails: creation of new JDeveloper project, adding some JAR-files to the project’s Additional Classpath, creating a Loadjava and Java Stored Procedure Deployment Profile and deploying it to the SYS schema in the target database. You can download the JDeveloper project for generic StoredJavaWebServiceClient here. (alternatively you can load the JARs that make up the Oracle SOAP library along with the Web Service Client Class by adding these jars to the Additional Classpath of the project and Checking the Project Additional Classpath in the Contributors node under the File Groups/Project Output in the Edit Deployment Profile Properties dialog).

You can download the entire JDeveloper project with Java WebService Client and Deployment Profile here.

Load WebService Client into Oracle Database as Stored Java

Now we want to load this Class, the WebService Client, into the database to make it accessible from PL/SQL. However, we first need to provide a static method since only public static methods can be exposed to PL/SQL.

• Add the static keyword to method getRate() as well as three private member variables: _endpoint, m_http_connection and m_smr; recompile the class
• Menu New (Gallery), General, Deployment Profiles, type Loadjava and java Stored Procedures; verify that the Java Class is listed under the Filters Node in the File Group Project Output. Uncheck all checkboxes on the Loadjava Options and Resolver tabs;
• Right Mouse Button menu on the Deployment Profile, Add PL/SQL Package; name: CURRENCY_RATE_WS_API
• RMB menu on the PL/SQL Package CURRENCY_RATE_WS_API; Add Stored Procedure; select method getRate(); Finish
• Deploy Deployment Profile to the Database Connection to schema WS;
• test the WebService in PL/SQL by running the CURRENCY_RATE_WS_API package from JDeveloper:

  DECLARE
    P1 VARCHAR2(200);
    P2 VARCHAR2(200);
    v_Return NUMBER;
  BEGIN
    P1 := 'us';
    P2 := 'uk';
  
    v_Return := WS.CURRENCY_RATE_WS_API.GETRATE(
      P1 => P1,
      P2 => P2
    );
    DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
  END;
  
  v_Return = .5356

  

You can download the entire JDeveloper project with Java WebService Client and Deployment Profile here.

Resources

For consuming webservices in PL/SQL from a Stored Java Client, the best resources are Calling a Web Service From The Database, An Oracle JDeveloper How To Document, Written by Susan Duncan March, 2004 and on Loading the WebServices Client Stack into the Oracle Database: Install SOAP Client Stack in Oracle Database

Additional background is provided by this white paper “WEB SERVICES IN ORACLE DATABASE 10G AND BEYOND” by Ekkehard, Rohwedder, Oracle Corporation Kuassi Mensah, Oracle Corporation (written for Oracle Open World 2003) describes both consuming and publishing of WebServices from the database, Calling external Web Service from a Java Stored Procedure , Unleash the Power of Java Stored Procedures An Oracle White Paper June 2002 and
Simplifying Database Access By Kuassi Mensah The improved Oracle JPublisher maps database entities to Java and Web services. Demonstrates use of JPublisher to generate PL/SQL WebService Client. I assume it is a alternative route, not using JDeveloper’s GUI, to achieve the same ends: a Stored Java Client that does the actual invocation of the Web Service.