OIC - How to use DB sequence while insert or merge to database table in Oracle Integration.

Usecase: We will develop an integration with on premise oracle database and insert employee data into database table using a sequence as a unique id as a Primary key.

Logic steps:

1. Create a sequence in the database.
2. Create a Rest Trigger and DB connection.
3. Configure the rest adapter to get the eomployees data.
4. Configure the database adpater >> select operation as "Perform an Operation on a table" >> select Insert or Merge(this 2 only valid) >> select Advanced Options Edit >>  select the created Sequence to fetch the sequence id and insert into the table. 

This is only valid for the insert and merge operarions.

Implementation steps:

Notes:

1. The insert or merge table should have a primary key field that will be assigned from the sequence.
2. The adapter generates sequence numbers in a  batch of 50. Configure sequence in increments of 50. This issue only applies to the oracle database adapter.

Example of a sequence created:

Create Sequence SD_Sequence Start with 1 increment by 50 nominvalue nomaxvalue nocycle nocache;

If we dont follow the notes >> 2nd  step (increment by 50 in sequence), we will get below error:

The sequence named [SD_Squence] is setup incorrectly. Its increment does not match its pre-allocaton size.