Retrieve Records from DB via Oracle OIC

Tap your database querying capabilities with Oracle Integration Cloud (OIC). Discover how Oracle OIC empowers you to effortlessly query databases, extract valuable insights, and integrate data across your applications for enhanced efficiency and informed decision-making. Earlier we have gone through inserting new records in a database using OIC. Now we will create a new integration to run select statements and retrieve records from DB via Oracle OIC.

Create Connections

As we have already seen, we need to define two connections. One for Rest end point to trigger the integration outside of Oracle OIC and other for for Oracle ATP DB. Configure the Oracle Database Adapter as a connection in your integration project. We will use the connection already created in the article. Please review the connection details before proceeding further. For details on Oracle OIC Agent, refer to the link.

Create Integration & Configure adapters

Create a new app driven integration flow that includes a trigger Rest end point and an Oracle Database Adapter as an invoke action. We will only configure response payload of REST endpoint here.

Drop the Rest connection on the canvas and configure the Rest endpoint. We will use GET action and configure the response payload only for simplification.

To configure the response payload, we need to check our product table data structure in the database as follows

Based on this, our response payload configuration will look like

{
  "Products" : [
  { 
    "instance_id" : "instance_id",
    "Product_name" : "Product_Name",
    "Product_price" : "Product_price"
   },
     { 
    "instance_id" : "instance_id",
    "Product_name" : "Product_Name",
    "Product_price" : "Product_price"
   }
   ]
}

To configure database adapter, we just need to pass SQL select statement for the product table

Map Data to Retrieve Records from DB in Oracle OIC

Use the Mapper in Oracle Integration Cloud to map the data from the source to the target format. Ensure that the data types and structures match between the source and the target. We will map the Rest end point response payload with the database adapter response since data retrieved from database will be returned to rest response.

And final flow mapping will look like this. Notice the mapping is in “Map to demo” point since we want to map response sides of both Rest and DB adapters.

Activate and Test

Activate your integration to make it live. Monitor the integration for any errors or issues. Test the integration by triggering it manually or through the configured trigger mechanism. Verify that records are retrieved from the Oracle Database as per the sql select query.

Refer to the link for more details on using Oracle database adapter with Oracle OIC.