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.

OIC - How to access Database sequence value in Oracle Integration

Usecase: Here, I will show you how to get the database sequence value in Oracle Integration.

Logic steps: 

• Create a sequence as required in the database.
• Create a database connection
• In the integration canvas , drag and drop the db adapter and configure as follow:
• Choose operation as "Run a SQL Statment"
• Provide sql query as select OIC_USER."Invoice_SEQ".nextval from dual and validate

Implementation steps with screenshots:

For create sequence , you can follow below blog:

https://soalicious.blogspot.com/2020/06/database-create-sequence.html

OIC - Exposing rest service supporting a file as attachment and JSON request using Oracle Integrtion Cloud

Usecase: Here, we will expose a rest service which take a file as attachment and also JSON data as request payload using Oracle Integration Cloud.

While configuring the rest trigger, select Request is multipart with payload option for sending attachment and keep 2nd option as unchecked to send request payload as required.

Implementation steps:

For more activities on upload file attachments as REST follow my below blogs:

OIC - Upload attachment file in rest ApI in oracle integration

OIC - Upload multiple attachment files in rest API in oracle integration

OIC - How to call " upload attachment as in REST integration" from another caller integration

Reference:

https://linkedin.com/pulse/exposing-rest-service-supporting-pdf-attachment-json-request-manjare

OIC - Download file from UCM using Oracle ERP Cloud adapter | ErpIntegrationService

Usecase: Here I will show you how to get the file from ucm using Oracle Cloud ERP adapter ErpIntegrationService as follows:

Case1:

1. First get document id by file prefix (doc title) 
2. Second get document by document id , fetched from 1st step.

Case2:

1. Get the document by file prefix(doc title) directly

Detailed steps with Screenshots:

Create an Oracle ERP cloud adapter connection

Integration flow:

Write a file to stage:

Create a csv file into stage:

Call Oracle ERP cloud adapter to download file from UCM:

Case1: 

Case2:

OIC - Upload file to UCM using Oracle Cloud ERP adapter | ErpIntegrationService

Usecase: Here, I will show you how to upload files to UCM using Oracle ERP Cloud adapter.

Case1:

• Using Send Files to ERP Cloud with details:
• docTitle : filename
• doctype : Document
• securityGroup : FAFusionImportExport
• docAccount : fin$/payables$/import$
• fileReference : stage file reference

Case2:

• Using Query, Create, update or delete information ErpintegrationService and operation: uploadFileToUcm with details:
• Content : file reference or base64 encoded
• Filename
• ContentType: filetype(csv, document or zip)
• DocumentTitle: filename
• DocumentSecurityGroup: FAFusionImportExport
• DocumentAccount : fin$/payables$/import$

Detailed steps with Screenshots:

Create an Oracle ERP cloud adapter connection

Integration flow:

Write a file to stage:

Create a csv file into stage:

Call Oracle ERP cloud adapter to upload file:

Case1: Send Files to ERP Cloud option

Case2: Query, Create , update or delete information option.