OIC Oracle Database polling | Oracle Integration cloud DB Polling

Here, we will see how to poll new rows from Oracle database and then write them to a file.

Highlevel steps:

1. Create a db table for polling
2. Create a Db Trigger connection
3. Creat an app driven orchestration integtation and configure db trigger 
4. Call a ftp connection adapter and write file

Steps in detail(With Screenshots):

Create a Database table and insert 2 rows.

Create Table EMPLOYEES_NEW

(

"ID" VARCHAR2(20 BYTE) ,

"NAME" VARCHAR2(200 BYTE),

"ISNEW" VARCHAR2(10 BYTE)

)

INSERT INTO EMPLOYEES_NEW(ID,NAME,ISNEW) VALUES(13,'Sanddy1','NEW')

INSERT INTO EMPLOYEES_NEW(ID,NAME,ISNEW) VALUES(14,'Dip1','NEW')

Create a database Trigger connection

Choose Oracle Database

Provide Connection Name, select role as Trigger >> Create

Provide Host, Port and Sid or service name

Select security as Username Password Token and provide user and password details. If the db is configured with Agent, then choose the respective agent group.

Create an App driven orchestration.

Adding Db Trigger and configure

Drop the Oracle DB connection as a Trigger type.

Enter a endpoint Name

Click Import Tables

Select the DB schema and enter table name and Search >> select the table and click Ok

Click on Edit button of Review the polling strategy and specify polling options.

Polling strategy: Logical delete

Logical Delete field: ISNEW

Read Value: PROCESSED(this value indicates the row has been processed)

Unread value: NEW( This value indicates the row to be processed)

Polling Frequency(Sec): 10(after which time of secconds, the rows wil be polled)

Click Next

Done

Adding a FTP adapter to write file.

Drop a FTP conntection

Privide Endpoint name

Select Operation as Write file

Select a Transfer mode as ASCII

Output directory, File Name and select Append to Existing file option.

Privide a CSV file sample.

Map the polling data to write file.

Save and Activate

Before activation:

After Activation

File content:

Note: suppose at a time there are multiple new rows in the db table, then for each row, separate transaction will be created and that we can observe in the monitoring page