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