Tuesday, December 27, 2011

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.

Friday, December 23, 2011

Wednesday Wizardry - Small encore

Just a small encore on my blog post about the Wednesday Wizardry session at UKOUG 2011. Thanks to Simon Haslam we have a 180 degree view on the setting and team. Based on the frowning and dislayed code on the screen the team was in the final stage of the session.

180 degree view of the Wednesday Wizardry session at UKOUG 2011

Sunday, December 18, 2011

Vennster @ UKOUG 2011

The UK Oracle User Group (UKOUG) Conference 2011 in Birmingham is a wrap! Well, at least it was when I started writing this blog :-) Now it has been over a week since I returned on a bumpy flight from Birmingham from my first UKOUG conference which I really enjoyed! Good atmosphere, friendly people, quality presentations, lots of familiar people, and surprisingly good food (at some places at least).

Here's a short summary of the sessions that were hosted by Vennster. Due to last minute cancellations we were asked to fill in for some of the speakers that couldn't attend and presented some more sessions than expected.

BPA Suite to BPEL case study
Lonneke presented on how to model business processes in Oracle BPA Suite and how to transform business process definitions from BPMN to BPEL along with the advantages and disadvantages of this approach. You can find the slides and more background information in this previous blog post.

Effective Fault Handling in Oracle SOA Suite 11g
I presented on fault prevention and handling in SOA and BPM environments and the mechanisms Oracle SOA Suite 11g offers for fault handling. A fault can be defined as something that happened outside the expected operational activity or “happy flow”. Faults can be categorized into:

  • Technical faults (network errors, full tablespace, etc.)
  • Programming faults (nullpointer exception, cursor not closed, etc.)
  • Business faults (credit check failure, invoice amount higher than the ordered amount, etc.)
  • Faulty user input (return date of a flight before the departure date, wrong credit card number, etc.).

To prevent faults you can apply various techniques: configure a robust infrastructure to avoid technical errors (clustering, redundancy, high availability), use pair-programming and peer-reviews to avoid programming errors, identify business requirements and implement these to avoid and correctly deal with business faults, and apply user experience techniques to lower the chance of faulty user input and its impact. This session mostly focused on fault prevention and handling for technical and business faults.

Event-Driven SOA: Events meet Services
Lonneke and I presented (unexpectedly since the would-be presenter was unable to attend) the use of events and how events, services, and processes can work together to provide flexibility, decoupling, and realize business requirements. Events are important from a business as well as technology perspective. Oracle SOA Suite supports eventing through several implemenations such as Advanced Queuing (AQ), Java Message Service (JMS), and the Event Delivery Network (EDN).

I included the slides from my earlier eventing presentation at Kaleidoscope 2011 which was quite similar to the UKOUG session.

Overview of Eventing in Oracle SOA Suite 11g

Some background information can be found in this 2008 blog post explaining the importance of events.

Approach to SOA: Making This a Successful Endeavor for the Whole Organization
Lonneke was asked last-minute to present on why and when to use SOA and what will makes it successful using the (according to some) infamous "breakfast" example. At the end of this session, you will know what SOA means, when it is best used, and how you can get there. 

Wednesday Wizardry
Lonneke, Lucas, and Ronald showed how to rapidly build an enterprise application based on SOA and BPM principles using Oracle Fusion Middleware in only a couple of hours. Read all about it in this blog post.

Other sessions
Due to the unexpected sessions we needed to host I wasn't able to attend that many other presentations. However, I did visit and enjoyed the "WebLogic Server and Oracle RAC" session by Simon Haslam and Frances Zhao on the integration between Oracle WebLogic Server and the Oracle (RAC) Database; especially Active GridLink and the enhancements it offers.

Christmas in Birmingham: Lonneke in front of a square with a giant polar bear and snooker on the big screen

As for me, until next year at UKOUG 2012!

Thursday, December 8, 2011

Wednesday Wizardry

No, this is not the title of a new Harry Potter novel. If it were, there would be lines of people in front of the ICC Birmingham days ago where the UKOUG conference took place. Instead there was a modest but very interested group of spectators assembled who were interested in how to rapidly build an enterprise application based on SOA and BPM principles using Oracle Fusion Middleware in only a couple of hours.

Let's step back a little bit. This year at the ODTUG Kaleidoscope conference in Long Beach a team of Dutch ACEs and ACE Directors took part in a rapid software development session called Thursday Thunder. The format is to have a small team of experienced developers that each build components of an enterprise application in parallel. The application consists of a business process, services, and a frontend. Such an approach is possible since the application is developed in a SOA-fashion: based on loosely-coupled components with well-defined interfaces that can be easily integrated. The developers' laptops are hooked up to projectors so the crowd can watch whatever developer, programming, and tools they are interested in. You'll also experience the interactions between developers and the pros and cons of the decisions they make. Moderators will question these choices, take questions from the audience, explain the design choices, interview the developers, and act as (annoying) stakeholder to the developers to spice things up a little bit. After a conference filled with theory and small demos, a final live development session showing all these concepts and resulting in a concrete result (a working application) in real-life is fun to watch!

Due to the success of the session at Kaleidoscope the format was copied at DOAG by our German and Swiss fellow ACEs and ACE Directors and repeated for this years UKOUG conference in Birmingham: Wednesday Wizardry! Goal was to develop an application supporting a conference and its speakers by implementing a speakers business process (submit an abstract, review and accept or reject it, invite a speaker, upload the presentation, collect evaluations, and so on).

Now we had a small setback with the original team. We lost some developers and moderators due to an ice hockey accident and last minute meetings. Of the original team only Lonneke Dikmans (Vennster), Lucas Jellema (Amis), and Ronald van Luttikhuizen (Vennster) remained. Luckily, Simon Haslam and John King were willing to step up as moderators!

So what were the three of us supposed to build in a three hour window? The figure below shows an overview of the various components involved. Lonneke would implement the speakers business process (blue) using Oracle SOA Suite 11g, Lucas would realize the user interface (yellow) using ADF 11g, and Ronald would develop the services (red) using Oracle SOA Suite 11g as well.

Overview of Wednesday Wizardry case


The team prepared only the database (green) and the interface of the services (so its WSDLs and XSDs) in advance. The rest was build from scratch.

In the beginning we had Murphy pay us a visit: There were some network problems and the VM on which the server was running was not reachable. Thanks to Alex Gorbachev, tips & tricks from the audience, and help from the tech guys we managed to solve the connectivity issues. In somewhat more than the two hours that were left the team was able to develop almost all of the functionality shown in the image and do a live demo for the "manager" (Simon was kind enough to play for manager, it was scary how well he did that...) at the end of the session. All steps from submitting an abstract to the acceptance and indicating the session was done were successfully executed!

Wednesday Wizardry Team in action; from left to right: Lonneke, Ronald and Lucas


Some general key take-aways:

  • SOA helps in breaking up systems into well-defined services that can be easily integrated. Thereby reducing complexity of components, speeding up development, and enabling parallel implementation.
  • A bigger development team is not always the best choice. A small experienced team can realize software fast(er) with less communication overhead.
  • Live enterprise application development in a few hours is doable and fun for both audience as well as developers. Things can go wrong and will go wrong, but these are the same issues real software development teams run into.

Some specific and technical take-aways:

  • Use MDS to store your service interfaces (WSDLs and XSDs); even for small projects! We didn't use MDS as team (too little preparation time) and as a side-effect had some troubles with defining service references for Web Services that pointed to local artefacts that were unreachable from the developers' machine.
  • Use scripting for deployment for predictable and less error-prone deployment of software components.
  • The out-of-the-box worklist application is an excellent tool in testing a busines sprocess. You don't need a working user interface that interacts with the Human Workflow components to test the business process.
  • Never overwrite a database trigger that enqueues an event on AQ with a trigger that selects sysdate from dual 5 minutes before the final demo :-)

So, what will be the next live application development session after Thursday Thunder and Wednesday Wizardry? Friday Fantasy, Monday Maniac, Tropic Tuesday? Our team is ready for it :-)