Tuesday, April 12, 2022

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 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


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 

No comments:

Post a Comment

Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...