Bind query parameter in database adapter in Oracle Integration Cloud Service

In this blog, we will show you how to use bind parameter in SQL query when use Oracle Database adapter.

Bind parameters are required to get the result based on the certain parameters.

Use Case: Develop REST service that takes EmployeeId as a input parameter and return the response as below:

{  
   "employeeId":"ABC@123",
   "firstName":"Ankur",
   "lastName":"Jain",
   "Qualification":"MCA",
   "Designation":"Sr. Manager"
}

This Use case requires only simple 4 steps to complete:

• Create REST connection
• Create DB connection
• Create an Integration
• Activate the Integration

Let's go step by step

1. Create REST connection: The REST connection we are gonna to create will work as a Trigger point. We have already showcased the same how to create REST connection in one of my blog. Please check it out before moving forward.
2. Create DB connection: DB connection will work as a Target point. We have already showcased how to create Oracle DB connection in one of my blog. Please check it out before moving forward.
3. Create an Integration: Follow below steps to create an Integration

• Login into ICS console
• Click on the Integration tile from ICS home page
• Click on Create button from upper right corner
• Select Orchestration pattern from the dialog box
• Enter Below information and click on Create button
• Select Application event or business object radio button
• Enter Integration Name in What do you want to call your integration text box
• An Identifier would be picked up automatically from Integration name however you can edit it
• Let the Version as it is
• Enter the description in What does this integration do box
• Leave the package box as it is

• Integration pane will look like

• Drag the TEST_REST_Conn Connection on the canvas from REST connection

• Enter below information and Click Next
• Name of the end point
• Relative URI(must start with /) for example (/getemployee)
• Select HTTP verb : GET
• Select Add and review parameter for this endpoint radio button
• Select Configure this endpoint to receive the response radio button

• Select + Sign from Specify Query Parameters section to add request parameter. Enter EmployeeId in Name column and select string in Data Type column then click Next button

• Select JSON sample radio button, click inline link and enter below payload. Click OK button then Next

{  

   "employeeId":"ABC@123",
   "firstName":"Ankur",
   "lastName":"Jain",
   "Qualification":"MCA",
   "Designation":"Sr. Manager"
}

• Select Done button
• Click Invokes from right navigation, Select Oracle Databases, drag and drop the TEST_DB_Conn below Map to GetEmployee

• Enter below information and Click Next
• Enter endpoint name(SelectEmployees) in What do you want to call your endpoint
• Select Run a SQL Statement from What operation do you want to perform drop down

• Enter below insert query in SQL query box and Click Validate SQL query to validate the entered query and Click Next button

select employee_id, first_name,last_name,qualificatoin,designation from apps.employee_info where employee_id = #employeeId

Note: Use # sign to define the binding parameters

• Click Done button on the last screen
• Edit the SelectEmployees mapper and Map EmployeeId parameter to employeeId

• From Actions, drop Map activity just below the SelectEmployees 

• Expand SelectEmployees-> SelectEmployeesOutputCollection -> SelectEmployeeOutput and map the fields as shown in the below screen shot

Integration is completed. Let activate the integration and test using POSTMAN tool

End point would be reachable on below URL:

https://<ICS_URL>/integration/flowapi/rest/REST_SERVICE/v01/metadata/getemployee?EmployeeId=123

Add Basic Authentication in the POST and hit the request