Friday, July 21, 2023

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:


  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.

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