Using DBWS to call PL/SQL APIs

This blog explains how to setup DBWS in Eclipse so you can generate webservices based on PL/SQL APIs in your database. This is a very convenient way of accessing existing logic in your database, without the need of heavy duty infrastructure like a SOA Suite or Service Bus.

DBWS is part of Eclipselink, the open source project lead by Oracle that transforms relational data to Java objects, and relational data to XML. 

I put the standard procedures in the HR schema, in a package.  In this example we want to expose the following PL/SQL API:

PACKAGE HR_PACKAGE AS

  PROCEDURE add_job_history

  (  p_emp_id          job_history.employee_id%type

   , p_start_date      job_history.start_date%type

   , p_end_date        job_history.end_date%type

   , p_job_id          job_history.job_id%type

   , p_department_id   job_history.department_id%type

   );

   PROCEDURE secure_dml;

END HR_PACKAGE;

SQLJUTL
Note that if you use the Oracle10g Express Edition database, you need to install SQLJUTL in the SYS schema. Susan Duncan wrote a blog about that back in 2006.

• Log into the database as sys (with the role sysdba) and run sqljutl.sql

DBWS builder file
The webservice is created based on a so called builder file. This file contains information about the database connection, and the procedures, tables and functions you want to expose as a webservice.  Here you see an example of the builder file that exposes the package we described above:

<?xml version="1.0" encoding="UTF-8"?>
<dbws-builder xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<properties>
<property name="projectName">hr</property>
<property name="driver">oracle.jdbc.OracleDriver</property>
<property name="password">hr</property>
<property name="url">jdbc:oracle:thin:@localhost:1521:XE</property>
<property name="username">hr</property>
<property name="platformClassname">org.eclipse.persistence.platform.database.oracle.Oracle11Platform</property>
</properties>
<plsql-procedure
name="JOB_HISTORY"
    catalogPattern="HR_PACKAGE"
    procedurePattern="%"
    isSimpleXMLFormat="true"
  />
</dbws-builder>

If you have used JPA (or Eclipselink JPA), these properties might look familiar. There are a couple of things to note here:

• the database properties for DBWS are elements. In EclipseLink JPA these are defined as attributes, like this:

<persistence-unit name="default" transaction-type="RESOURCE_LOCAL">
    <provider>
        oracle.toplink.essentials.PersistenceProvider
    </provider>
    <exclude-unlisted-classes>false</exclude-unlisted-classes>
    <properties>
        <property name="javax.persistence.loglevel" value="INFO"/>
        <property name="javax.persistence.jdbc.driver" value="oracle.jdbc.OracleDriver"/>
        <property name="javax.persistenc.jdbc.url" value="jdbc:oracle:thin:@myhost:l521:MYSID"/>
        <property name="javax.persistence.jdbc.password" value="tiger"/>
        <property name="javax.persistence.jdbc.user" value="scott"/>
    </properties>
</persistence-unit>

• The database property names are different than then the database properties in EclipseLink JPA. You can find the list op properties in the documentation. 

After creating the builder file, you can generate the web service. You can run the dbws utility from the command line using the following command:

dbwsbuilder.cmd -builderFile dbws-builder.xml -stageDir stage -packageAs wls hr.war

Another option is to run the DBWSBuilder class from eclipse.

• Create a new webservice project 'jobhistory'
• Create a new Java project 'dbws-test'
• Create a variable that points to your eclipselink installation, and a variable that points to your jdbc driver:

• Create a Java class that runs the DBWSBuilder class:

package test;
import javax.wsdl.WSDLException;
import org.eclipse.persistence.tools.dbws.DBWSBuilder;
public class DBWSLauncherTest {
    public static void main(String[] args) {
        try {
            DBWSBuilder.main(args);
        }
        catch (WSDLException e) {
            e.printStackTrace();
        }
    }
}

• In the Run configuration you specify the arguments for the main class:

-builderFile ./dbws-builder.xml -stageDir /home/oracle/dbws/Jobhistory -packageAs eclipse

The utility will create a an exploded war file, that contains a WSDL, mapping files, and two Java classes.

• Deploy the webservice and insert a record in the job history table. 

I would like to thank Mike Norman, Team lead for the DBWS Eclipselink project, who helped me setup
Eclipse so I could run the DBWSBuilder class from within the IDE.