Enter your keyword


CRUD Operation in Oracle Integration Cloud (Part-3)

This is the third part of DB CRUD operation in Oracle Integration Cloud(OIC).

Use Case: Create a REST service which will select a user record based on User id

Follow the article for the pre-requisite.

Let’s get started and implement the use case

  • Login in the Oracle Integration Cloud and navigate to the Integration tab
  • Click on Create button and select App Driven Orchestration template
  • Enter the Integration Name as DB_Get_By_Id and click on Create button
  • Select Rest_Trigger_conn
  • Enter below information and click Next button
    • What do you want to call your endpoint: GetUserById
    • What is the endpoint’s relative resource URI?: /users
    • What action do you want to perform on the endpoint: GET
    • Add and review parameters for this endpoint: Select this checkbox
    • Configure this endpoint to receive the response: Select this checkbox
  • Add Query Parameters as UserId of type integer and click Next button

  • Select JSON Sample from the drop down and then click on <<<inline>>> link
    • Enter below json sample and click OK button
        “User”: {
            “id”: 3,
            “FIRSTNAME”: “”,
            “LASTNAME”: “”,
            “DESG”: “”,
            “QUAL”: “”
    • Just complete the wizard
    • Select Test_DB_Connection

    • Enter below details and click Next button
      • What do you want to call your endpoint: GetUserData
      • What operation do you want to perform?: Perform an Operation On a Table
      • Select the Select radio button
    •  Select below information and click Import Tables button
      • Schema: HR
      • Table Type: TABLE
      • Table Name: Enter %USER% and click the Search button
      • Select USER_MASTER table from Available pane and move to the Selected pane
    • Click on Edit link from Review and edit SQL Query section
    • Click on Edit using Expression Builder link
    • Add Query Criteria by clicking on Add New link
    • Select the information as below and click OK button:
      • First Argument: select id
      • Operator: Equal
      • Second Argument: Select Parameter radio and enter U_Id in the input box
    • Click OK
    • Click OK
    • Click Next
    • Edit the GetUser mapper
    • Map UserId -> U_Id
    • Add mapper just below the DB adapter
    • Map all the parameter from left to right
    • Configuration is completed. Just save the integration and Activate
    This is the time to test the REST service. POSTMAN client will be used the test the service. Enter the REST service endpoint in the POSTMAN and add BASIC authentication.

    One Comment

    Leave a Reply

    Your email address will not be published.