OIC/Integration/12 → REST Connection with multiple methods to perform multiple operations on Database table

6 min readAug 31, 2023

This is a post on Oracle Integration Cloud, highlighting CRUD operation on Database table.

(1) Flow:

SELECT : [1.a] Trigger → [1.b] Map to Select DB → [1.c] Invoke DB Select → [1.d] Map to Reply → [1.e] Reply
UPSERT : [2.a] Trigger → [2.b] Map to Upsert DB → [2.c] Invoke DB Upsert (Merge) → [2.d] Map to Reply → [2.e] Reply
DELETE : [3.a] Trigger → [3.b] Map to Delete DB → [3.c] Invoke DB Delete → [3.d] Map to Reply → [3.e] Reply

(2) Flow Description: Here we are creating an App Driven Orchestration flow exposing via REST with multiple methods for Select, Merge, Delete (CRUD) Database operation which can be invoked while passing the Query Parameters.

(3) Steps:

Go to OIC console → Integrations → Create an App Driven Integration with your project name.

[1.a] Trigger:

Use the following XSD, “RestDBMultOperSchema.xsd”:

<?xml version="1.0" encoding="windows-1252" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.rest.db.mult.operation.com"
            targetNamespace="http://www.rest.db.mult.operation.com" elementFormDefault="qualified">
  <xsd:element name="listReq">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="operation" type="xsd:string" minOccurs="0"/>
        <xsd:element name="req" minOccurs="0" maxOccurs="unbounded">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="field_1" type="xsd:string" minOccurs="0"/>
              <xsd:element name="field_2" type="xsd:string" minOccurs="0"/>
              <xsd:element name="field_3" type="xsd:string" minOccurs="0"/>
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
  <xsd:element name="listResp">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="result" type="xsd:string" minOccurs="0"/>
        <xsd:element name="resp" minOccurs="0" maxOccurs="unbounded">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="field_1" type="xsd:string" minOccurs="0"/>
              <xsd:element name="field_2" type="xsd:string" minOccurs="0"/>
              <xsd:element name="field_3" type="xsd:string" minOccurs="0"/>
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

[2.a] Trigger:

[1.c] Invoke DB Select:

[1.b] Map to Select DB:

[1.d] Map to Reply:

[2.c] Invoke DB Upsert (Merge):

Update DB table to add Primary Key constraint:
Updated DB Table:

CREATE TABLE "RRJ"."EMP" 
   ( "EMP_ID" NUMBER NOT NULL ENABLE, 
 "EMP_NAME" VARCHAR2(50 BYTE), 
 "CRT_UPD_DATE_TIME" TIMESTAMP (6) DEFAULT CURRENT_TIMESTAMP, 
  CONSTRAINT "EMP_PK" PRIMARY KEY ("EMP_ID")
   );

After correcting now retry the steps and click next.

[2.b] Map to Upsert DB:

Reposition the activity “Map to upsertDB” to highlighted position:

Test Upsert operation with below payload:

<listReq xmlns="http://www.rest.db.mult.operation.com">
 <operation>operation4537</operation>
 <req>
  <field_1>001</field_1>
  <field_2>Avik</field_2>
 </req>
 <req>
  <field_1>002</field_1>
  <field_2>Souvik</field_2>
 </req>
</listReq>

New records in EMP DB table:

Now test Select operation:

Now we will add another operation in the exposed service for Delete.

[3.a] Trigger:

[3.c] Invoke DB Delete:

[3.b] Map to Delete DB:

Test Upsert operation with below payload:

<listReq xmlns="http://www.rest.db.mult.operation.com">
 <operation>operation2291</operation>
 <req>
  <field_1>003</field_1>
  <field_2>Aunik</field_2>
  <field_3></field_3>
 </req>
</listReq>