Insert Records into DB using Oracle OIC

Easily insert records into your database from Oracle Integration Cloud (OIC). Explore how Oracle OIC simplifies data insertion processes, enabling seamless integration between applications and databases for streamlined operations and efficient data management in your organization. There are a number of use cases e.g. to generate auto incremented numbers using db sequences and to insert integration run history record during each integration run for audit purposes. To insert records into a DB using Oracle OIC, we will use database adapter connection and follow below steps to create an integration.

Configure Connections

We need to define two connections. One for Rest end point to trigger the integration outside of Oracle OIC and other for for 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.

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.

Drop the Rest connection on the canvas and configure the Rest endpoint. We will use POST action and configure both request and response payloads.

Configure Request payload like

In order to confirm the record insertion, we also need to configure response payload.

In the end, we will now configure DB adapter and here we need to specify the insert query with table columns names and parameters (prefixed with #).

Map Data to Insert Records into 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 request payload with the database adapter request since data from request payload will be inserted into database. The instance ID relates to each run of OIC integration and we will also store it in database.

For this demo, we will hard code the response as follows

Here is the final mapped flow

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 inserted into the Oracle Database as expected.

We can also verify the same by querying the product table in sql developer.