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”
}
“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
- 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.
- 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.
- 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”
}
“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
This comment has been removed by the author.
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
Hi Ankur,
I am uanble to activate this integration because of the error "Missing primary business identifier for tracking"
How can I resolve it? Appreciate your response.
Thanks,
Arif
Great articles. Is there any way to subscribe to receive all the posts ?
Practically all projects contain some inside bugs.https://www.dbdesigner.net The SQLite database program has some minor bugs which may cause defilement.
This comment has been removed by the author.
This board is fundamentally the minds of the drive, and it is the place the PC interfaces to the hard drive. An electrical disappointment can happen out of the blue whenever.data recovery manchester
Thanks for sharingcustom erp solutions
Thanks for such a valuable and informative platform I find here. I am regular to read publication of this blog and definitely waiting for more articles.
Pest control Melbourne
Wow, wonderful blog layout! How long have you been blogging for?
you make blogging look easy. The overall look of your website is fantastic, let alone the
content!
I was wondering if you ever considered changing the structure of your site?
Its very well written; I love what youve got to say. But maybe you could
a little more in the way of content so people could connect with it better.
Youve got an awful lot of text for only having one or two images.
Maybe you could space it out better?
After checking out a handful of the blog posts
on your web page, I seriously like your way of writing
a blog. I book-marked it to my bookmark webpage list and will be checking back in the
near future. Please check out my website as well and let me know
how you feel.
Fantastic beat ! I wish to apprentice while you amend your web site, how can i subscribe for a blog website?
The account helped me a acceptable deal. I had been a little bit acquainted of this your broadcast
provided bright clear idea
Does your blog have a contact page? I’m having a tough time locating
it but, I’d like to send you an email. I’ve got some suggestions for your
blog you might be interested in hearing. Either way, great blog and
I look forward to seeing it develop over time.
Yes it is there. You can directly send a message
I read this paragraph fully regarding the comparison of
latest and previous technologies, it’s awesome article.
WOW just what I was looking for. Came here by searching for เวปไซต์
Great post. I will be facing many of these
issues as well..
I am really enjoying the theme/design of your site.
Do you ever run into any browser compatibility problems?
A small number of my blog audience have complained about my site not working correctly in Explorer but looks great in Chrome.
Do you have any solutions to help fix this problem?