Database Adapters
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
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.
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
After creating the database adapter service, take a look at application navigator window, Oracle JDeveloper creates many files for you automatically.
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.
Now in Recieve Activity Select DB_Polling as partner link immediately Operation List Box will be populated and variable text box will be enabled.
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
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
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.
Apps2Fusion Articles 
Comments
Thank you for this wonderful series of articles. I am looking forward to learn more from your writing. May I know if it is possible to get a PDF version of the write-up? It seems that the PDF link for this article as well as the first (I) is not working. Thank you in advanced.
When i use database adapter, i have problem with namespases like in this article http://forums.oracle.com/forums/thread.jspa?messageID=2497332
Maybe you can help me to fix it?
Thanl you/
thanks for this useful demo.. ive been looking for support for this tech since long time.
Actually, i have a problem while invoking SP on DB that has in param and out param. simply this SP does nothing. it only inserts records into DB. But, im getting an error
Error while trying to prepare and execute an API.
An error occurred while preparing and executing the TABS.INSERTINTO ERRORSTABLE API. Cause: java.sql.SQLExc eption: No more data to read from socket [Caused by: No more data to read from socket]
Check to ensure that the API is defined in the database and that the parameters match the signature of the API. Contact oracle support if error is not fixable.
I am designing a BPEl process which polls for DB changes and run an oracle application concurrent program after finding changes.So here for single
record change and for multiple record change , the concurrent process needs to run only once.When I tried the process is running once per each record.
Can you help how to set the polling options for the above scenario?
Than ks,
Bhakta
I have a BPEL process that polls a database table (and sets the status to PROCESSED) and writes the record into a file using an FTP adapter.
I would like to rollback the updation of the status column if the FTP adapter fails to write. I tried specifying the following property in the the bpel.xml file and specified a rollback in the catchAll exception handler.
parti cipate
But this does not rollback the status.
Any help in doing this will be highly appreciated ?
Thanks in advance
Ramdas
I have run into a situation where the database password for the user 'Scott' (used in your example) was changed by DBA (90 day password change policy etc). How can I update the same in JDeveloper? I am unable to locate the file where the connection parameters are stored. Please help
RSS feed for comments to this post