OIC - Database package call to insert Varray size of data | DB package insert in oracle integration

Use Case: Here, we will create a package which is accepting Varray type of elements and then call it and feed customers data from Oracle Integration.

Note: use Varray or nested table to create a valid collection type of the fields. Here we will use Varray types.

Create VARRAY type:

Create or replace TYPE NUMBER_ARRAY IS VARRAY(1000) OF INTEGER;

Create or replace TYPE STRING_ARRAY IS VARRAY(1000) OF VARCHAR2(200);

Create or replace TYPE STRING_ARRAY_ID IS VARRAY(1000) OF VARCHAR2(20);

Package specification:

Create or replace package PKG_CUSTOMERS_MULT AS

PROCEDURE addCustomers(P_Id STRING_ARRAY_ID, P_Name STRING_ARRAY, P_Age NUMBER_ARRAY, P_Address STRING_ARRAY);

END PKG_CUSTOMERS_MULT;

/

PACKAGE BODY:

Create or replace package PKG_CUSTOMERS_MULT AS

PROCEDURE addCustomers(P_Id STRING_ARRAY_ID, P_Name STRING_ARRAY, P_Age NUMBER_ARRAY, P_Address STRING_ARRAY)

IS

BEGIN

FOR i IN P_Id.FIRST .. P_Id.LAST

LOOP

INSERT INTO CUSTOMER1(ID,NAME,AGE,ADDRESS) VALUES(P_Id(i),P_Name(i),P_Age(i),P_Address(i));

END LOOP;

END addCustomers;

END PKG_CUSTOMERS_MULT;

/

Steps in Detail:

Integration flow:

Rest trigger adapter configurarion

Invoke stored procedure steps:

Map rest request to db target package elements.

Note: In the map, for each package Varray field, for each cuatomers, we have to map the item elements individually.

For example, - for P_ID

<P_ID>

   For each customer,

      <P_ID_ITEM>

         Id of each customer

      </P_ID_ITEM>

</P_ID>

Testing:

Input:

Output: DB table: