Monday, April 25, 2022

OIC - Call DB package insert operation | one to one database insert using Package

Use Case: we will create a simple package and using OIC integration, insert the customer records one by one to the database.

Highlevel steps:

  1. Create a customer table and package to insert customer records
  2. Rest trigger to accept customer records.
  3. Call package using db adapter
  4. For each customer record, insert to database.

Create table:

Create table customer1(

Id varchar2(20),

Name varchar2(200),

Age number,

Address varchar2(200));


Package Specification:

CREATE OR REPLACE PACKAGE PCK_CUSTOMER AS

PROCEDURE addCustomer(c_id customer1.id%type,

c_name customer1.Name%type,

c_age customer1.age%type,

c_addr customer1.address%type);

END PCK_CUSTOMER;

/

Package Body:

CREATE OR REPLACE PACKAGE BODY PCK_CUSTOMER AS

PROCEDURE addCustomer(c_id customer1.id%type,

c_name customer1.Name%type,

c_age customer1.age%type,

c_addr customer1.address%type)

IS

BEGIN

INSERT INTO CUSTOMER1(ID NAME,AGE,ADDRESS) VALUES(c_id, c_name, c_age, c_addr);

END addCustomer;

END PCK_Customer;

/

Rest trigger request json:

{

"Customers":{

"Customer":[

{

"Id":1,

"Name":"aaa",

"Age":12,

"Address":"qqq"

},

{

"Id":2,

"Name":"aaa",

"Age":12,

"Address":"qqq"

}]

}}



Steps in detail:

Integration flow:


Rest trigger configuration:







For each customer


Invoke  a stored procedure.




Map customer to db target fields.



No comments:

Post a Comment

Featured Post

End to End Securing Data in Oracle Integration Cloud (OIC) with OCI Vault: AES for Encryption & Decryption & RSA for Signing and verification | Implement message-level encryption in Oracle Integration using OCI Vault

Scenario: A company is integrating Oracle Integration Cloud (OIC) with an external financial system to exchange sensitive payroll data secur...