Friday, 13 January 2017

Poll database using DB adapter in Oracle Service Bus

We have used DB Adapter in OSB to perform operation in database. One of the features that is mostly used is database polling when working with database.

In a blog, i will show you different polling strategies in ACTION from OSB perspective.

I have taken a use case of migration data from one source(Users) table to destination(Users_Dump) table.

To complete this demo you would be requiring Jdeveloper 12C and Database.

This post assumes the reader has basic understanding of working with DB adapter in SOA using Jdeveloper.

Assume we have two tables in the database.

USERS:



 USERS_DUMP






Let's start step by step explanation

Project structure will look like




Create Business Service

1) Right click on the Right swim lane and configure the DB adapter like below:









Polling Adapter

1) Right click on the left swim lane and configure the DB adapter as a polling adapter





Select the Poll for New or Changed Records in a Table option


Select the source(USERS) table



Select the Update a Field in the Table(Logical delete). This option allows you to update the Field after the record has been picked up.

Enter the below details:
a) Logical Delete Field: Select Field based on you want to poll. In our case field name          is ISACTIVE
 b) Read Value: Enter the value that you want to update after read the row
 c) Unread Value: Enter the value, on which  you want to select record
 d) Reserved Value: Enter the value that would be updated once the record picked up,let's say 'L'




Click Next and Finish.

Let's start with message flow(Pipeline)

Create a XSLT transformation to transform the poll result to insert result



Create a pipeline, Right click on the middle swim lane and insert pipeline



Message flow will look like


Replace properties

a) Location: body
b) Value: select XSLT


c) Replace Option: Replace node content

Run the application and check the Users_DUMP table. This table must have 2 records as Users table had two records with INACTIVE column having value 'Y'. Once the poll completed, you can see INACTIVE flag become to 'N' in Users table.

Done!


1 comment: