In this blog post I am going to give you an overview of What Is Oracle REST Data Services (ORDS) and how can we use it to expose PL/SQL APIs using RESTfull web services.
For use in Oracle Integration Cloud (OIC) to create integration you will greatly benefit from Oracle REST Data Services, Instead of creating DB adapter, Configuring connection to table in DB, It allows you to define a pattern in the DB System streamlining the process of Incoming and Outgoing data.
If you want to learn more on how to use REST API in OIC for manipulating data then you need to check our course Oracle Integration Cloud OIC where you will learn Provisioning OIC | Connectors/Adapters | REST & SOAP APIs | Customer Case Study | Security | Monitoring Integration.
Oracle REST Data Services makes it easy to develop modern REST interfaces for relational data in the Oracle Database and the Oracle Database 18c JSON Document Store. A mid-tier Java application, Oracle Rest Data Services maps HTTP(S) verbs (GET, POST, PUT, DELETE, etc.) to database transactions and returns any results formatted using JSON.
Read this to know more about what is Web service, API and the difference between SOAP Vs REST API (Simple Object Access Protocol & REST Representational State Transfer) and how are they related.
Oracle REST Data Service provides many options for authenticating users.
- These include OAuth client, APEX User, Database Schema User, and OS User. While it is important to ensure your ORDS web services are secured, you also need to consider what a client has access to once authenticated. As a quick reminder, authentication confirms your identity and allows you into the system, authorization decides what you can do once you are in.
- Oracle REST Data Service is a Java EE-based alternative for Oracle HTTP Server and mod_plsql.
- The Java EE implementation offers increased functionality including a command-line based configuration, enhanced security, file caching, and RESTful web services.
- Oracle REST Data Service also provides increased flexibility by supporting deployments using Oracle WebLogic Server, GlassFish Server, Apache Tomcat, and a standalone mode.
- The Oracle Application Express architecture requires some form of the webserver to proxy requests between a web browser and the Oracle Application Express engine. Oracle REST Data Service satisfies this need but its use goes beyond that of Oracle Application Express configurations.
- Oracle REST Data Service simplifies the deployment process because there is no Oracle home required, as connectivity is provided using an embedded JDBC driver.
Also read: Oracle integration cloud to understand the overview for beginners.
Creating Basic RESTfull Web Services Using PL/SQL
In real-world scenarios inserting data into a single table is not enough for this we can use PL/SQL APIs to do the work and expose those as RESTfull web services.
We haven’t downloaded the Oracle REST Data Service then do so as we will be using it.
1. Create a Test User I am creating TESTUSER1 for this example
2. Grant permissions to “CREATE SESSION, CREATE TABLE, CREATE PROCEDURES”
3. Connect with the new user (testuser1)
4. Now we will create an Employee (EMP) Table.
Read More: Oracle API Platform.
5. Insert Sample data in the EMP table and commit.
6. Enable Oracle ORDS for the testuser1 and assign the base path of HR.
Also Check: Oracle Adapter.
7. Define a module that is a collection of templates or URL patterns. A module is a container for one or more templates, with an associated path (rest-v1/).
8. Now we will create a REST web service using the GET method, The Basic makeup of an Oracle ORDS RESTful web service is as follows.
- Template: Container for one or more handlers. The template must be unique within the module and is associated with a specific path (employees/), which may or may not include parameters.
- Handler: A link to the actual work that is done. Typical handler methods include GET, POST, PUT, DELETE, which are passed in the HTTP header, rather than the URL. Each handler is associated with a specific source (or action), which can be of several types.
9. For our case, the web service/endpoint URL will be a combination of Base ORDS URL, Schema (alias), Module and Template: http://localhost:8080/ords/hr/rest-v1/employees/
To Know More About How To Change Visual Builder Tenant DB To Oracle ATP click here
10. Testing web service/endpoint URL in Postman. Provide the URL with GET verb then click on send.
If you get a 200 OK response with the payload in JSON containing all the rows in the table.
11. Now, we will add another template into the module. If we add :empno to the end of the template pattern which allows us to target individual employees.
12. Testing web service/endpoint URL in Postman. Provide the URL with GET verb and employee number at the end of the URL and then click on send.
Check out: Our blog post on Advantages Of HDL For HCM Data Integration
If you get a 200 OK response with the payload in JSON containing all the details of the employee associated with the empno.
Go through this Oracle Integration Cloud Service Blog to get a clear understanding of ADF vs VBCS
Defining Views
13. We can see the definition in the database using USER_ORDS_% views.
a. For view module definition owned by our user, you will use user_ords_modules.
b. For viewing Template Definition you will use user_ords_Template.
c. For viewing Handlers Definition you will use user_ords_handlers.
Read More: About OIC vs SOA.
Creating Procedures
Creating procedures using Pl/SQL for Modifying data.
14. Creating a procedure create_employee that excepts the necessary parameter and creates a new employee.
15. Adding a new handler to employees template, this handler is for the POST method and p_source block to call the stored procedure.
Also Read: Our blog post on Oracle cpq and ebs integration.
16. Testing web service/endpoint URL in Postman. Provide the URL with POST verb, in the header specify content-Type with value application/json
17. In the Body provide the JSON payload and then click on Send.
If you get a 200 OK response then the employee details have been added, you will not get any output as we haven’t coded return document.
Read More: About Oracle Integration Cloud.
18. Checking the table if the new employee has been added to the table.
19 Now we will create a new Amend employee procedure it will accept all the necessary parameters and then use it to update the employee details.
20. Adding a new handler to employees template, this handler is for the PUT method and p_source block to call the stored procedure.
Also Check: Our blog post on OIC Notification Activity.
21. Testing web service/endpoint URL in Postman. Provide the URL with PUT verb, in the header specify content-Type with value application/json
22. In the Body provide the JSON payload and then click on Send. We are using the same payload we used last time for POST with modification in the name.
If you get a 200 OK response then the employee details have been updated in the table, you will not get any output as we haven’t coded return document.
23. Checking the table if the new employee has been updated in the table.
Also Check: How to Migrate ICS to OIC.
Remove Procedure
24. Now we will create a new Remove procedure it will accept empno for removing employee details from the table.
25. Adding a new handler to employees template, this handler is for the DELETE method and p_source block to call the removal procedure.
Also Read: ERP Cloud Adapter.
26. Testing web service/endpoint URL in Postman. Provide the URL with DELETE verb, in the header specify content-Type with value application/json
27. In the Body provide the JSON payload that specifies the employee and then click on Send. We are using the same employee we updated last time.
If you get a 200 OK response then the employee details have been updated in the table, you will not get any output as we haven’t coded return document.
28. Checking the table if the employee has been deleted from the table.
Read this blog if you are a beginner who has started to learn Oracle Integration Cloud (OIC) and want to get familiar with Process Cloud Service (PCS).
Conclusion
This is how you can expose PL/SQL APIs using RESTfull web services. This would be particularly useful when you are creating an integration with REST API and want to get data in the desired pattern.
It would help you in reducing the time required to create an integration as the data that is pulled or pushed follows a preset pattern.
I hope you find this blog post useful and now are familiar with the concept and steps of ORDS. If you want to learn how to create multi REST API in OIC check my other blog on Using REST Adapter With Multiple Resources & Verbs In Oracle Integration Cloud (OIC)
Related Links/References:
-
-
- Oracle Integration Cloud (OIC) Notification Activity
- Our best blogs for Oracle Integration Cloud – OIC
- Managing & Monitoring Integration Errors in Oracle Integration Cloud (OIC)
- 6 Docs Every Oracle Cloud Integrator Must Read
- How to exporting Business Data and Visual Builder Cloud Service (VBCS)
- SOAP Vs REST APIs In Oracle Integration Cloud (OIC)
- 40+ Oracle Integration Cloud (OIC) Interview Questions & Answers [Updated – 2022]
- Process large file (above 10MB) in Oracle Integration Cloud Service (OIC)
- Difference Between Oracle Integration Cloud [OIC] And Oracle Cloud Infrastructure [OCI]
-
Next Task For You
Do you want to learn more about Oracle and confuse where to start or which certification is right for you? Then, click on the register now button below to register for a Free Masterclass on Oracle [1Z0-1042] Certified Cloud Integration Expert, which will help you better understand and choose the right path and clear the certification exam.
Jason says
Thanks for sharing such a knowledgeable blog on Oracle.
Rahul Dangayach says
Hi Jason,
Glad you liked our blog.
Please stay tuned for more informative blogs.
Thanks and Regards
Rahul Dangayach
Team K21 Academy
Khajamiya says
Thank you so much for the informative article on ORDS. All the best for your future articles.
Rahul Dangayach says
Hi Khajamiya,
Glad you liked our blog.
Please stay tuned for more informative blogs.
Thanks and Regards
Rahul Dangayach
Team K21 Academy
Francisco says
Hello, we are trying to connect from Oracle forms or developer to BINANCE websocket to receive the JSON big information of the candles coins…ANYONE can help me?
I already install ORDS and JETTY and Oracle 19c plus forms 12c 12.1.4 version.
Rahul Dangayach says
Hi Francisco,
Regarding this, it is a customized query and for this, I would suggest you should raise an SR with Oracle support.
Thanks and Regards
Rahul Dangayach
Team K21 Academy
Julio says
Hi, can you explain me more details about of benefits of de ORDS vs DB connector? any example where to use ORDS is more indicated?
Thanks in advance
Julio–
Rahul Dangayach says
Hi Julio,
Regarding your request, Please check the link below for reference:
https://www.oracle.com/in/database/technologies/appdev/rest.html
Thanks and Regards
Rahul Dangayach
Team K21 Academy
Christian says
Hello,
first of all, Thanks a lot for the article.
Is it correct, that i just need a oracle (EE) and i can use it, or do i need some middleware part to?
If not, i think ORDS is already included in the EE license?
My second question is about using the webservice remotly and not from localhost. Just replace localhost with the fixed IP or hostname?
Thank you.
Rahul Dangayach says
Hi Christian,
A1. ORDS is included (and for no additional cost) with any Oracle Database license.
Hope this helps.
Thanks and Regards
Rahul Dangayach
Team K21 Academy
Christian says
Hi,
thank you for the answer. That sounds good.
Do you have also some informations about my second question? (Remote call of an Webservice. In all example URLS only localhost is mentioned.
Thanks
Christian
Christian says
Hi,
thank you for the answer. That sounds good.
Do you have also some informations about my second question? (Remote call of an Webservice. In all example URLS only localhost is mentioned.
Thanks
Christian
Sravan Kumar Komminedi says
Thank you K21 Team, this is so much for the informative article on ORDS.
hchunni says
This is very nice but I can’t do this using package and procedure .Which have a one out parameter ..
How do I define this ?
Rahul Dangayach says
Hi Hchunni,
Could you please confirm have you exposed this PL SQL procedure using REST?
So that we can help you accordingly.
Thanks and Regards
Rahul Dangayach
Team K21 Academy
ChitraRamya says
What could be the p_pattern for the below query
select * from emp where ename=:p_ename and job=:p_job;
Rahul Dangayach says
Hi Chita,
What is p_pattern in the provided query, please elaborate So that we can help you accordingly.
Thanks and Regards
Rahul Dangayach
Team K21 Academy
eyad says
Hello
How to get data from another database using database link
Rahul Dangayach says
Hi Eyad,
Create a DB link in the target DB pointing out to the source db and then use the DB link to get data
Hope this helps.
Thanks and Regards
Rahul Dangayach
Team K21 Academy
Alex says
Hi,
How can I do when I need to pass query params? i don’t know how to refer to them into the PL/SQL procedure.