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.