Database Adapters in 10g

Summary

In this article we will be discussing Database Adapter services in Oracle JDeveloper. We will be using example with Oracle 10G database, offcourse this DB Adapter can also connect to Non-Oracle Database which has connectivity using JDBC or JDBC-ODBC bridge.

Developing BPEL Process using Database Adapters

Start JDeveloper, create new blank application BPELApp. Select Application BPELApp in Applications Navigation, right click and select NEW and from tree view select BPEL Project.

Enter the name of BPEL Project as SampleBPEL_1 and Template as Empty BPEL Process and don't change the namespace field. Click Finish to see blank BPEL Process Designer.

Oracle JDeveloper is shipped with various adaptor services which can be configured based on requirements. These services are listed in Component Pallet section

While Developing a BPEL Process we need various process activities which are also part of component section

In this article we will be dicussing about Database Adapters. We will be using DB Adapter to Oracle 10G database.

Drag the database adaptor from component palet and drop it on to services area of BPEL Process Designer. Adaptor Configuration wizard with Database Adapter Service Type will appear.

You can select a pre-existing database connection (If you have created in Connections section of JDeveloper) or you can configure new connection by clicking New button. Most important is JNDI Name for the database. This JNDI Name must be configured on Oracle Application Server as discussed in Article-I of this series.

There are various actions we can perform using database adapter e.g. calling a DB procedure/function, performing DML (SELECT/INSERT/UPDATE/DELETE) on database table, executing custom SQL or polling data in certain table.

Specify the stored procedure or function name on next screen.

In this example procedure P_GET_EMP_DETAILS has one VARCHAR2 input parameter and one RECORD type OUT parameter both automatically get mapped and displayed on next screen.

You will see Oracle JDeveloper will create another wrapper package bpel_SOADB_Adaptor in the Scott Schema.

If you read through this final screen carefully, JDeveloper will create SQL files for newly created Wrapper Package. These files will be usefully while moving the BPEL Project Code from one instance to another i.e. While developing BPEL code you will develop it by connecting to DEV database where wrapper package will be created by JDeveloper but when you need to deploy this code to PROD database, first step will be to run these SQLs manually before deploying BPEL project on PROD.

After finishing DB adapter setup, Oracle JDeveloper automatically creates the partner link for DB Adaptor and displays following screen. A partner link enables you to define the external services with which the BPEL process is to interact. You will see WSDL file name, which essentially tells that database adapter service is a Web Service which will be linked to other services in BPEL Project. So all Services/Adapters are nothing but Web Services exposed in SOA Suite which can be used while building BPEL/ESB project

Click Apply and OK to complete Adapter setup.

After creating the database adapter service, take a look at application navigator window, Oracle JDeveloper creates many files for you automatically.

The SCOTT_BPEL_SOADB_ADAPTOR_TOPLEVEL-24P_GET_EMP_DETAILS.xsd stores the declaration/structure of Procedure call gving the details of INPUT and OUTPUT parameters. The XSD can be displayed in diagram as follows:

The SQL files in Application Navigator are for deployment purpose when database instance other than development.

The WSDL files are definitions of Web Services (Database Adaptor) used in the project.

Based on the type of operation you select, WSDL file get generated with Operation, Input and Output parameters. Now with the above case of calling database procedure with input and output parameters, try to create a receive activity. Drag the receive activity and drop in Activities area of BPEL designer. Double click on the receive activity to see activity properties. From Partnerlink List of value SOADB_Adaptor. You will not see any operation in Operation List. That is because we are using Procedure call which cannot be a start of BPEL Process as BPEL Process must be triggered by external event. If we change the type of action is DB Adapter to Polling database table at regular intervals you will see the operation List box populated.

So lets create another DB Adapter service which does polling database table using DB adapter wizard. After selecting Operation type following screen appears

After selecting the table, click OK and in later screens select one of the columns as primary key. In step 8 you have to select operation to be performed after reading the row in TEMP_EMP table.

Select the polling options in next step

Complete the remaining steps in adapter creation and click finish.

Now in Recieve Activity Select DB_Polling as partner link immediately Operation List Box will be populated and variable text box will be enabled.

Click the icon on right hand side on variable text box to create Receive_1_receive_InputVariable also click Create Instance check box. Reason for this is obvious, DB Pooling service will be able to trigger receive activity and polling action reads a table ROW which can act as input variable to receive activity.

When we created DB Adapter with store procedure call, there was nothing to trigger Receive Activity. Generally DB Procedure call needs a Invoke Activity, so lets create a Invoke activity.

Drag the invoke activity from component pallete to Activities area. Double click on Invoke icon to set Invoke activity properties like Partner Link, Operation, Variable etc.

Select the SOADB_Adaptor as partner link which calls a database procedure with some input and output parameters. Immediately Operation list box gets populated. Since this procedure has both input and output variables both Input and output variable text boxes are enabled. Click on Icon near these text boxes to create input and output variables.

Now our BPEL Process is displayed as follows

The flag on Invoke_1 activity shows there is some warning or error. So click the flag to see the details

The error seems correct which says variable not initialized. That means input variable in Invoke activity needs to be set with some value. Logically this is correct, we need to pass some value to the procedure to get the output. So now we will have to perform mapping, i.e. variables which got initialized Receive activity needs to be passed to Invoke activity. To do this we will add Assign Activity in activities section between Receive and Invoke activity.

Double click on Assign icon in Activities section and in Copy Operations tab select the type of operation.

Copy Operation screeb will be displayed. Expand the Tree structures in both From and To sections

Now we want to pass Emp_No variable value received from Receive Activity to Input parameter of SOADB_Adaptor Procedure call. Perform set selection as displayed above and click OK. Now click on Yellow flag on Invoke activity and click apply. There won't be any flag now as BPEL Process is logically correct and Process will be displayed as follows:

Compile/Rebuild the BPEL Project from Application Navigator. If there are any errors in LOG window, try to analyze and fix it and for Warnings being normal developers we don't care.

You can try out other operations like performing various DML actions in tables, calling functions etc as practice. You will not only Polling Operation can be used with Receive activity remaining need Invoke activity to perform the action.

Conclusion

I hope this article would give you a direction about usage of Database adapters . In this article we example was given only with Oracle Database but it is also possible to connect to Non-Oracle databases like MS Access etc using JDBC Connectivity/JDBC-ODBC bridge. Just create the connection in Connection Navigator section and use it in DB Adapter Wizard. In the next article we will take up some other adapter for discussion.