CRUD Operation in Oracle Integration Cloud (Part-1)
This is the first part of DB CRUD operation in Oracle Integration Cloud(OIC).
Use Case: Create a REST service which will insert the users in the Oracle database table
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_Insert and click on Create button
- Select Rest_Trigger_conn
- Enter below information and click Next button
- What do you want to call your endpoint: InsertData
- What is the endpoint’s relative resource URI?: /users
- What action do you want to perform on the endpoint: POST
- Configure a request payload for this endpoint: Select this checkbox
- Configure this endpoint to receive the response: Select this checkbox
- Select JSON Sample from the drop down and then click on <<<inline>>> link
- Enter below json sample and click OK button
{
“Users”:[
{
“FIRSTNAME”:””,
“LASTNAME”:””,
“DESG”:””,
“QUAL”:””
},
{
“FIRSTNAME”:””,
“LASTNAME”:””,
“DESG”:””,
“QUAL”:””
}
]
}
- Select JSON Sample from the drop down and then click on <<<inline>>> link
- Enter below json sample and click OK button
{
“status”:””
}
- Click Done button
- Drag the DB connection or Select Test_DB_Connection
- Enter below details and click Next button
- What do you want to call your endpoint: PushData
- What operation do you want to perform?: Perform an Operation On a Table
- Select Insert 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 Next button
- Click Done button
- Edit PushData mapper
- Map all the parameter from left to right except Id as it will be auto-generated when data will be inserted into the table via the database trigger
- Drop a mapper just below the DB connection and set the hardcoded status as “Data Inserted Successfully“
- 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.
Enter below payload into the POSTMAN and click Send button
{
“Users”:[
{
“FIRSTNAME”:”Ankur”,
“LASTNAME”:”Jain”,
“DESG”:”TL”,
“QUAL”:”MCA”
},
{
“FIRSTNAME”:”Nitin”,
“LASTNAME”:”Jain”,
“DESG”:”BTech”,
“QUAL”:”AVP”
}
]
}
Open database table and see the record should be inserted successfully
This comment has been removed by the author.
Great post. There is one problem with this implementation. How does the client know what the IDs are of the newly created people. How do you return an array of responses for each create result (ID or Error message), This would be a more real world example, such as:
{"results": {"result":[
{
"FIRSTNAME":"Ankur",
"LASTNAME":"Jain",
"ID":"123456",
"status":"created"
},
{
"FIRSTNAME":"Nitin",
"LASTNAME":"",
"status":"ERROR: Not null constraint violated"
}
]}
}