Using ad-hoc queries with the DB Adapter

A BPEL White Paper
Written by Olivier Le Diouris, Oracle Corporation
Setpember, 2005

Introduction
The regular path
The trick and tips
Errors to avoid
Resources

When a Database operation - select, update or insert, PLSQL function or procedure call - is to be performed from a BPEL process, the way to go is to use the DB Adapter that comes with the BPEL Process Manager and Designer.

This DB Adapter uses Toplink for the Object-Relational mapping, and exposes the generated objects using WSIF - like other Adapters do.

Toplink is providing a thick abstraction layer, hiding whenever it is possible the complexity of the SQL or PLSQL statements to perform in the database.

However, it is sometime necessary to open the hood and implement a SQL statement.
Like for example:

 SELECT SCOTT.EMP.EMPNO, 
        SCOTT.EMP.ENAME, 
        SCOTT.EMP.JOB, 
        SCOTT.EMP.MGR, 
        SCOTT.EMP.HIREDATE, 
        SCOTT.EMP.SAL, 
        SCOTT.EMP.COMM, 
        SCOTT.EMP.DEPTNO,
        SCOTT.DEPT.DNAME
 FROM SCOTT.EMP, 
      SCOTT.DEPT 
 WHERE SCOTT.DEPT.DEPTNO = SCOTT.EMP.DEPTNO AND 
       SCOTT.EMP.HIREDATE BETWEEN TO_DATE(UPPER('&dateFrom'), 'DD-MON-YYYY') AND 
                                  DECODE(NVL(LENGTH(LTRIM('&dateTo')), 0), 0, SYSDATE, 
                                         TO_DATE(UPPER('&dateTo'), 'DD-MON-YYYY'))

where dateFrom and dateTo are provided by the user. Generating an equivalent Toplink expression is quite a challenge. The BETWEEN, NVL, LENGTH, LTRIM, SYSDATE, UPPER, TO_DATE operators and functions are Oracle extensions to SQL, not handled by Toplink.
It is not unusual to have such pre-defined queries, which can be way more complex than in our example, and reusing them instead of defining or trying to define their Toplink equivalent would probably save quite some time.
This is the path we want to follow in this document, so that we take advantage of the wizards as much as possible, to generate the appropriate classes and the associated XML schemas that are going to match what the SQL statement would return.

The regular path
Top

There is a sample coming with Oracle BPEL that explains how to customize the toplink_mapping.xml file to reach the goal we're targeting.
This example is located in [BPEL_HOME]\integration\orabpel\samples\tutorials\122.DBAdapter\PureSQLSelect
It is mostly about replacing some element(s) of the mapping file with a <sql-string> element, actually containing the SQL Query to execute.
This example is clear and well-done, we are not going to go through it again. But we are going to show how to implement its recommandations with minimum effort. Let's be lazy.

The trick and tips
Top

It's all about minimizing the customization to toplink_mapping.xml, and the number of manual steps we will have to go through, so we minimize the risk of introducing errors.
In this example, we will only talk about a query (a Select statement) in the database.
When you use the DB Adapter Wizard, the following visible operations are performed:

The table(s) and/or view(s) to work on are identified
The appropriate columns of each of the identities selected above are selected
Potential user parameters are created

Under the hood, the following actions (at least) are taken:

Java classes corresponding to the data to retrieve are created
Toplink mapping file (toplink_mapping.xml) is generated
This file contains the mapping from SQL to Java, as well as the structure of the parameters and the potential Toplink expression - corresponding to some extend to the where clause of the SQL select statement.
The XML Schema matching the returned structure is created.
Mandatory for WSIF, extensively used by BPEL.

You certainly do not want to go through all those steps by hand...

The trick we used is the following one:

We created a view in the database, returning the exact same structure as the SQL Statement we wish to execute, and ran the DB Adapter wizard on it.

The SQL Statement itself could be used to create the view (with no user parameters, or with hard-coded ones), with an additional where clause like 1 = 2, to actually minimize the response time (some views with GROUP BYs, HAVINGs, etc, can take some time to execute specially if the user parameters are not correct).
We used for this example a statement like this one:


 CREATE VIEW FOR_TOPLINK AS
 SELECT SCOTT.EMP.EMPNO, 
        SCOTT.EMP.ENAME, 
        SCOTT.EMP.JOB, 
        SCOTT.EMP.MGR, 
        SCOTT.EMP.HIREDATE, 
        SCOTT.EMP.SAL, 
        SCOTT.EMP.COMM, 
        SCOTT.EMP.DEPTNO,
        SCOTT.DEPT.DNAME
 FROM SCOTT.EMP, 
      SCOTT.DEPT 
 WHERE SCOTT.DEPT.DEPTNO = SCOTT.EMP.DEPTNO AND 
       SCOTT.EMP.HIREDATE BETWEEN TO_DATE(UPPER('01-jan-1970'), 'DD-MON-YYYY') AND 
                                  DECODE(NVL(LENGTH(LTRIM('31-dec-2010')), 0), 0, SYSDATE, 
                                         TO_DATE(UPPER('31-dec-2010'), 'DD-MON-YYYY')) AND
       1 = 2;

Then we modified the mapping file - we will see that the modification to make is now a minor one.
And finally, we dropped the view.

Walk through

Let's precisely describe the steps we went through, one by one.

Create the view in SQL*Plus

We use the statement mentioned above:
Create the view

The view is created, and contains nothing.
Query the view

Create the Partner Link in JDeveloper

Now, in JDev, where you have created a new BPEL Process, you add a Partner Link.
Create Partner Link

Choose to invoke the Adapter Wizard

We will obviously use the Database Adpater

Give the service a name. Remember that this name will be used to create the name of the WSDL file for this service. It has to be unique in this project at least.
Name the Service

You now need to refer to the connection where the recently created view can be found.
Refer to the right connection

In our case, as we said, it will be a Select Statement
Choose the operation

In the chosen connection, pick up the view we have just created.

Select the view in this pane

A primary key must be defined. It is very important to refer to a column that uniquely identifies the rows. Irrelevant data might be returned otherwise.
Primary key identification

No relation is to be defined in our sample.
Skip that one

Review your work...

Here, you have the possibility to define parameters, define the two ones we already talked about, but do not create any expression, where they could potentially have been involved.

Review, and finish.
Done

The Partner Link is created.
Partner Link Created

Customization...

After walking through the steps described above, JDeveloper has created several things:

Notice the following objects:

The AdHocQueryService.wsdl file
The Toplink mapping
The ForToplink.java class

We no want to edit the toplink_mapping.xml file to modify it, manually.
Between <distinct-state> and <query-indirection>, insert the following element:

                     <distinct-state>0</distinct-state>
                     <sql-string>
 SELECT SCOTT.EMP.EMPNO, 
        SCOTT.EMP.ENAME, 
        SCOTT.EMP.JOB, 
        SCOTT.EMP.MGR, 
        SCOTT.EMP.HIREDATE, 
        SCOTT.EMP.SAL, 
        SCOTT.EMP.COMM, 
        SCOTT.EMP.DEPTNO,
        SCOTT.DEPT.DNAME
 FROM SCOTT.EMP, 
      SCOTT.DEPT 
 WHERE SCOTT.DEPT.DEPTNO = SCOTT.EMP.DEPTNO AND 
       SCOTT.EMP.HIREDATE BETWEEN TO_DATE(UPPER(#fromDate), 'DD-MON-YYYY') AND 
                                  DECODE(NVL(LENGTH(LTRIM(#toDate)), 0), 0, SYSDATE, 
                                         TO_DATE(UPPER(#toDate), 'DD-MON-YYYY'))
                     </sql-string>
                     <query-indirection>
                        <in-memory-query-indirection>
                           <policy>0</policy>

Notice the way to refer to a parameter, no quote, and a '#' in front.

Drop the view

The view is now useless, we can drop it.
Drop view

Ready to go

Let's imagine we have created an activity to invoke the Partner Link, and created the two variables it needs in input for the BPEL Process, this would be what we have:
BPEL Start

And after invoking the Partner Link, the output variable would be populated with the expected data:
BPEL Result

Errors to avoid
Top

Primary keys

This one actually does not stricly belongs to this test case, it is a general Toplink concern. The primary key has to be chosen in such a way that it identifies one and only one record returned by the query. If not, if the same key value is the same for n records, then n instances of the first record having this value for the key will be returned.

Null values for parameters

In our example, based on the where clause of the query, we can see that if the second parameter is not entered, the value for the date will be SYSDATE (current date).
To populate the invoke input variables, instead of an assign, we used a transformation to pass into toDate a blank value instead of no value, in order to avoid a NullPointerException from the BPEL Process Manager.

Resources
Top

The samples used in this document

� 2005, the A Team

Using ad-hoc queries with the DB Adapter

Introduction

The regular path

The trick and tips