Oracle Rest Data Services | How To Use ORDS to expose PL/SQL APIs

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.