Executing a Database SELECT Query in Oracle Service Bus (OSB) 12c via Business Service Configuration

This article will focus on building SOAP integration in OSB to access data from a database. In this integration, we will utilize the database adapter as a business service, which needs to be configured with select operations. 

To begin building the integration, the first step is to create a table. Please use the script below to create the table structure:

CREATE TABLE XXOSB_PRACTICE_TEST (

CUST_ID NUMBER,

CUSTOMER_NAME VARCHAR2(1000),

CUSTOMER_CITY VARCHAR2(1000),

CUSTOMER_DESIGNATION VARCHAR2(1000),

CREATION_DATE DATE,

CREATED_BY NUMBER,

LAST_UPDATE_DATE DATE,

LAST_UPDATE_LOGIN NUMBER,

LAST_UPDATED_BY NUMBER

)

Insert one dummy record into the table 

INSERT INTO XXOSB_PRACTICE_TEST (CUST_ID,

                                 CUSTOMER_NAME,

                                 CUSTOMER_CITY,

                                 CUSTOMER_DESIGNATION,

                                 CREATION_dATE,

                                 CREATED_BY,

                                 LAST_UPDATE_DATE,

                                 LAST_UPDATE_LOGIN,

                                 LAST_UPDATED_BY)

     VALUES (1798798,

             'Pranay Tiwari',

             'Indianapolis',

             'Consultant',

             SYSDATE,

             1334,

             SYSDATE,

             1334,

             1334)

Now create Service Bus Application and Project 

Provide the Application Name 

Provide Project Name 

Create Folder structure as shown in below screenshot. 

Create Business service based out the DB Adapter. 

Provide the Adapter Name 

Configure the TNS details for the database connection

Select the Table Name 

Select any key as Primary Key Column 

Pick the operation as Select 

Click on Next , and provide the Parameter Name 

Map the parameter name as shown in the below screenshot. 

Run the business Service 

Provide the parameter value 

Finally Payload generated 

Validate from Database