OIC - DB polling and logical delete from oracle database in Oracle Integration

Usecase: Here, we will show you how to poll records from a database based on status field logical delete concept and if the record has non empty email id then, it will write them to a ftp directory, else update the record as ERROR in the database table.

Logic steps:

1. Create a database connection as Oracle database or dbaas 
2. Create a db table and insert records with status as NEW.
3. Create an app driven orchestration integration and configure db polling where 
1. Logical delete field = Status
2. Read value= PROCESSED
3. Unread value = NEW
4. Rejected value =REJECTED
5. POLLING Frequency = 30 sec
4. Add a switch and check if the record has non empty email id and then write them to a ftp ditectory.
5. Else configure db adpater invoke and update the status as ERROR and update date using current-dateTime() function.

Notes:

• Support for processing message payloads up to 10 MB in size. In the case of polling, we must set the Rejected value property to REJECTED on the polling strategy and options page. If the incoming message is greater than 10 MB threshold size, that particular record is updated to REJECTED instead of read or new.
• For each db record each instance will be created.

Create table query:

Create table SD_EMPLOYEE_Test(

ID NUMBER PRIMARY KEY,

FirstName VARCHAR2(200),

LastName VARCHAR2(200),

Department VARCHAR2(100),

Age NUMBER,

EmailAddress VARCHAR2(100),

Status VARCHAR2(10),

CreateDate Date,

UpdateDate Date

);

Detailed steps with screenshots:

Integration flow:

Configure db polling trigger

Edit > Review and verify table and relationship attributes

Edit > Review the polling strategy and specify polling options.

Add a switch and have below condition

Configure write file FTP part

Map the polled record to ftp directory

In else case, configure update db status part.

Testing: 

6 records added with status as NEW

After activating the integration, we can see, 5 records processed and 1 in ERROR as no email id.

Below 5 files created for each eno empty email record :