Sunday, 5 November 2017

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




      2 comments:

      1. This comment has been removed by the author.

        ReplyDelete
      2. Got to learn a lot of things from this article. It can be used for any kind of business. Pegasi Media Group the world's leading database vendor and the most preferred organization all over the globe. Our brand is well-known in the market. The organizations of all the sizes have experienced our services and are quite pleased with it. We perform the activities like data cleansing, data appending, data updating, data validation, and data verification on a regular basis before the final delivery. You have also got the tremendous flexibility to choose the geographical location and reach out to the prospects that you target.Database Marketing Services

        ReplyDelete