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:
- Create a sequence in the database.
- Create a Rest Trigger and DB connection.
- Configure the rest adapter to get the eomployees data.
- 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.
Implementation steps:
Notes:
- The insert or merge table should have a primary key field that will be assigned from the sequence.
- 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.
