Monday, May 27, 2013

Oracle Database Adapter in SOA 11G Tutorial

Hi Everyone,

I'll be demonstrating Oracle Database Adapter in today's tutorial.Oracle JCA adapter for DB enables a BPEL process to communicate with Oracle databases or third party databases through JDBC. The Oracle Database Adapter service is defined within a BPEL process partner link by using the Adapter Configuration Wizard of Oracle BPEL Process Manager (Oracle BPEL PM).

There are number of operations avilable that you can perform with DB adapter namely :

  • Call a Stored Procedure or Function :Select this option if you want the service to execute a stored procedure or function.

  • Perform an Operation on a Table: Select this option for outbound operations. You can select Insert or Update, Insert Only, Update Only, Delete, Select, or any combination of the six. 

  • Poll for New or Changed Records in a Table:  Select this option for an inbound operation (that is, an operation that is associated with a Receive activity). This operation type polls a specified table and returns for processing any new rows that are added. You can also specify the polling frequency.
  • Execute Pure SQL: Useful when dealing with arbitrarily complex statements, aggregate queries (result is not row-based), and XMLType columns.

Uscase Scenario: We will be using DB table for polling new records and updating the same table with some field.To execute thiw we will use one inbound DB adapter,polling for new records.One outbound adapter for updating records in a table.BPEL process that will contain our process logic.

Before starting the lab,I would like to explain dirrerent types of files that are being created while configuring DB adapter
  1. <serviceName>.wsdl: This is an abstract WSDL, which defines the service end point in terms of the name of the operations and the input and output XML elements.
  2. <serviceName>_table.xsd: This contains the XML file schema for these input and output XML elements. Both these files form the interface to the rest of the SOA project.
  3. <serviceName>_or-mappings.xml: This is an internal file. It is a TopLink specific file, which is used to describe the mapping between a relational schema and the XML schema. It is used at run time.
  4. <serviceName>_db.jca: This contains the internal implementation details of the abstract WSDL. It has two main sections, location and operations. Location is the JNDI name of an adapter instance, that is, eis/DB/SOADemo. Operations describe the action to take against that end point, such as INSERT, UPDATE, SELECT, and POLL. The contents of the db.jca file are wholly determined by choices made while running the Adapter Configuration Wizard.
  5. <serviceName>.properties: This is also an internal file. It is created when tables are imported, and information about them is saved. It is used only at design time.

At run time, the location is used to look up the adapter instance which executes the service. Based on the properties in the db.jca file and the linked or-mappings.xml file, <seviceName>.properties file generates the correct SQL to execute, parses the input XML, and builds an output XML file matching the XSD file. To execute the SQL, it obtains a pooled SQL connection from the underlying data source.

Lets start with the tutorial:

Step1: Create one sample table which will be used in our tutorial.I have created "TB_USERS" table and inserted some records in it.Kindly observe the value of "ReadFlag".Its significance will be discussed later on.
Step2: Drag and drop DB adapter from service adapters onto the exposed services lane
Step3: DB Adapter configuration wizard will open up.Click next.
Step4: Give the service name as "PollerDB".
Step5: In the next window give the connection details.I presumed you guys have already created a DB connection.If not,kindly create one and use here.
Step6: Select "Poll for New or Changed Records" as a operation type.It is an inbound operation which polls for new records in a table or whenever existing records are changed.
Step7: In next window,we will import our table from database.Click on import tables.
Step8: Search for your table using Name Filter and Query,and then move it to selected section by clicking on the arrow.
Step9: Our table will be imported and shown in next window.
Step10: DB adapter requires a primary key in a table to uniquely identify a row in atable.Since while creating table we haven't created any primary key,select "FName" as primary key and move next.
Step11: We are working on single table only.No need to define relationships,move next.
Step12: Select the database columns that you want to query from table.I have selected all of them.
Step13: Next window will ask you to choose an operation that will be performed when a record is read.Select Logical Delete option.This operation employs the logical delete polling strategy. This strategy involves updating a special field on each row processed and updating the WHERE clause at run time to filter out processed rows. It mimics logical delete, wherein applications rows are rarely deleted but instead a status column isDeleted is set to true. The status column and the read value must be provided, but the modified WHERE clause and the post-read update are handled automatically by the Oracle Database Adapter.
Step14: If you go back I have created one field "ReadFlag" in the table and given value as "N" for all the records.This field will act as logical delete field.DB adapter will poll for all records having unread value (N in our case) and after processing will update the value of this field to Read Value (P in our case) after successful read.
Step15: In the next window set the values for Polling frequency,DB Rows per transaction.Also check Distributed Polling as distributed polling is used to ensure that the same data is not retrieved more than once.
Step16: Next window no change is required .Click next.
Step17: Next.
Step18: Finish.We have configured our DB adapter for polling records from a table.
Step19: Now,to write records to the same table,drag and drop one more DB adapter this time to external references lane.Give the service name as "WriteDB".
Step20: Same way we did for polling adapter,choose DB connection and click next.
Step21: Under operation type,choose "Update Only" ,since we will update the value of FullName only without modification in other column values.
Step22: Import Table.
Step23: Define Primary Key.
Step24: Since we are going to update the value for "fullname" field only.Choose fullname and click next.
Step25: Next and finish.
Step26: Drag and drop BPEL component onto the components lane and choose Define Interface Later Template and finish.
Step27: Wire both polling adapter and adapter for writing records with BPEL by dragging and dropping wires to BPEL component.
Step28: Drag and drop receive activity and link it with Polling adapter partner link.Configure receive activity and create input variable for this activity.If you dnt know how to work with and configure receive activity ,kindly refer to my initial blogs.In that I have explained in detail about using receive activity.
Step29: Likewise,drag and drop invoke activity to invoke Write DB adapter.In the same way create input variable for invoking DB adapter.
Step30: As of now your BPEL process will look like this.
Step31: Now drag and drop assign activity between receive and invoke activity.We will populate the input variable for invoking DB adapter in this assign activity.Map the first name field from receive input variable to invoke DB adapter input variable.And for full name use expression builder to concatenate the Fname and lname from Input and map it with Fullname field of invoke DB adapter input variable.

Step32: Save all and deploy your composite on EM
Step33: We have set the polling frequency to 10 seconds,so after that interval out interface will poll for new records at regular interval.Click open one composite.
Step34: Observe the input payload that is received using pollign adapter and the invoke activity input variable for invoking the Write DB adapter.
Step35: Now to verify the records are also updated in table,go back to your DB and open up your table.Fullname column is populated with values and also observe the value of read flag "P". Now poller will not poll for any records anymore as no record exists with value "N".It will poll again when some new records are inserted in the database or existing records ReadFlaf value is set back to "N".
Thats it guys.Hope you guys have understood the concept ob DB adpater in Oracle SOA Suite 11G,its use and configuration.In the coming tutorial I'll be posting blogs on using other operations of DB adapter as well.

If you guys have any concerns,coubts or issues ,please feel free to contact me by commenting here or emailing me.I'll get back with the reply at earliest.Also,if you guys need blog specific to topic you want to undertand,I Will be happy to do that as well.Till then take care

Happy Learning,


  1. hello karan i have developed same composite but instead of updating the table i m pushing it in jms queue with a jams adapter the issue is when i deployed it then after db adapter nothing is getting invoked but when i check shyncronous post to bepel in db adapter configuration it works but it does not update the table's poll flag kindly suggest

  2. Hi Karan I follow the same steps but for me the value is not updating in database

  3. Very Nice Thoughts. Thanks For Sharing with us. I got More information about Oracle from Besant Technologies.

  4. Helo karan.i follow the same steps,i successfully deployed in server but there instance has not begining created and not get updating the table in database

    1. Hi Naveed,

      Did you configure your DB adapter on weblogic console and created datasource to be used for the same ? Are you seeing any error in the SOA logs ?


    2. yah i had configured DB and created datasource but the new records are not be polling into the DataBase.

    3. can you check the process column on basis of which you are polling "Processed" and "Unprocessed value". Is it same in the table that you have configured in DB adapter ?

    4. I followed the same steps to create polling db adapter for the BPEL process I created. Deployed successfully. Configured Data source and database connection factory in WL admin console. However no bpel process instance is being created for some reason. any help to debug would be much appreciated...

  5. Helo Karan i follow the same above steps and i have successfully deployed into server,i set polling frequency as 10 but i cant get created the instances so further i'm unable to update the table in the database..will u plz kindly suggest for this

  6. Hi Karan...Can u suggest how to resolve the problem if the DB adapter is not importing the tables from database.

    1. You mean while configuring DB adapter, you are not able to import the tables? If that is the case. Close the wizard , close the DB connection and retry one more time.

      Let me know if that helps.

    2. it does not help

  7. Hi Karan,

    Is that possible to poll the records from 2 different tables present in the same schema?
    If this is possible, can you please give some basic steps for the same?


    1. Yes, its pretty much possible to poll from multiple tables..All you need to do is while configuring DB adapter , import all the tables you want to poll data from, create relationship between them based on primary key.

      Rest everything will remain the same.

  8. Can we insert multiple statements in in dbadapter??

    1. Could you please provide some more details

  9. Hi Karan,

    i am doing a db polling operation. But when i am 1000 records to "UNREAD" state , the data source is getting suspended. So the initial instances are a success , but later the instances are getting failed. .I have set the Max transaction size to 10 . But still i get the same error. Is there any way to control no of records processed per polling interval?
    Please help . Its really urgent !!!

    1. have you set num of xml rows per document in your DB adpater. That way you can control number of rows/records per transaction

  10. HI Karan,

    Thank you so much for the reply . According to my understanding , if i set the no of xml rows to 10 , then...10 rows polled form the database will be in 1 instance. But i need each row in each instance. please correct me if i am wrong.

  11. Hi Karan,
    I am following all of these steps but while deploying the application, i am getting error
    "Error(49): Operation "receive" of portType "{}poll_DB_Adapter_ptt" is a request-response operation. Need to respond with a activity."

    How to resolve this issue plz..

  12. Hi Karan,
    I did exactly the same, but instance is not getting created, neither it is showing any error.
    I have configured the DB Adapter properly along with JDBC DataSource (I am able to fetch or insert into DB)

    I am attaching screenshot here:

    1. Sorry, it turns out i was not using correct JNDI Name for the Polling adapter. Now its working :)

  13. Hi Karan ,

    My issue is resolved . Thanks ! Can i have your email id please .My id is . Please send me an email when you are free if possible :)

  14. This comment has been removed by a blog administrator.

  15. Hello Sir,

    Thanks for the post, I followed clear steps mentioned in blog, I can to able to deploy in my composite, But I am unable to see my instance in em console.I also created new jndi connection, But even I am unable to see instance. Please provide me good idea to solve this


  16. Thanks for sharing dear. But is that possible for you to share the data source details/ snap shot. I tried this but no update in the rows.

  17. Awesome and very helpful.. :) thank you.