Database polling in Oracle Integration Cloud Service

In this article, we will show how to use Oracle database polling in Oracle ICS. Oracle ICS allows very straight forward solution to poll the Oracle database in comparison to SOA.

Let's see how we can achieve database polling strategy in Oracle ICS.

• Create a database table(XX_EMPLOYEE) with below script

CREATE TABLE XX_EMPLOYEE

 (             "ID" VARCHAR2(20 BYTE),

                "NAME" VARCHAR2(200 BYTE),

                "ISNEW" VARCHAR2(10 BYTE)

 )

• Run below script to insert some values

 insert into XX_EMPLOYEE(ID,NAME,ISNEW) values(1,'Ankur Jain','NEW')

 insert into XX_EMPLOYEE(ID,NAME,ISNEW) values(2,'Nitin','NEW')

• Create a Oracle Database Connection in Oracle ICS. Have a look to this blog for Oracle DB connection. Make sure Role must be of Trigger type
• Create an Orchestrated Integration

• Drop the Oracle DB connection as a Trigger point

• Enter Name in What do you want to call your endpoint input box and click Next

• Click Import Tables

• Select the DB schema in which XX_EMPLOYEE table has been created, Enter the table name and transfer to Selected Tables. Click OK button

• We will notice the below configuration screen

• Click on Edit button of Review the polling strategy and specify polling options and enter/select below values
• Polling Strategy: Logical Delete
• Logical Delete Field: ISNEW
• Read Value: This value used to indicate the row has been processed. Enter PROCESSED
• Unread Value: Indicate the row to be process. Enter NEW
• Polling Frequency(Sec): Specify the polling frequency in seconds to process the new record

• Click Next and Done

This is the only configuration required to poll the Oracle DB. Now activate the integration and check the DB rows. We'll notice that the rows has been picked by Integration and ISNEW flag values has been set to PROCESSED

Before Activate the Integration

After Activate the Integration

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!

File adapter configuration in Oracle Service Bus

This blog explain how to configure File adapter in Oracle Service bus and create XML files with very minimal configuration.

We will poll on the database, select the new records and create the XML files.

Let's follow the below simple steps:

1) Create a Service Bus application with service bus project

2) Open the composite

3) Right click on the left swim lane, Insert Adapters - > Database

4) Select connection and enter JNDI name

 5) Select Poll for New or Changed Records in a Table, click Next

6) Select Employees table, click Next

7) Click Next

8) Click Next

9) Choose Update a Filed in the Table option

 10) Enter the below values:

      a) Logical Delete Field: Select Field based on you want to poll. In our case field name is POLL
      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 -1

11) Enter Next -> Next-> Next->Finish

Polling adapter has been configured properly.

Let's move ahead and configure File adapter

12) Right click on the Right swim lane, Insert Adapters -> File

13) Click Next -> Next -> Next

14) Enter the below information

   a) Directory for outgoing files: physical path where the files will get stored
   b) File Naming convention: Enter the name of file. That can be changed later in pipeline

15) Select the DB_Poll.xsd and click Next -> Finish

File adapter has been configured successfully.

Let's proceed further and create pipeline that will call File Adapter configured in very previous steps.

16) Create a XSLT that will transform the DB adapter output for the File Adapter input. During XSLT configuration, select the primary source and target to refer the same XSD(DB_Poll_table.xsd)

17) Right click on the Middle Swim lane and insert pipeline. Enter pipeline name, click next and select WSDL option. Select DB_Poll-concrete WSDL, un-check the Expose as a Proxy service and Finish

18) Drop the Pipeline pair node. Drop the Publish activity in the request pipeline

19) Select the Write_File.bix service from Publish activity properties

20) Drop the Replace activity in the publish activity and configure the properties:

a) Location: body

b) Expression: .

c) Value: Select the XSLT resource and select xslt that we have created.

d) Replace option: Replace node contents

21) Drop the Assign activity under the publish activity and configure the property. This will be used to set the xml file name dynamically. In this case we will keep the file name like firstName_employeeId

22) Drop the Transport Header activity under the Publish activity and set the value:

Your pipeline will look like:

Now run the DB adapter, and see the file got created or not at the location you configured during file adapter configuration.

Note: You must have Poll field with 0 values in the Employees table.

That's it!