In this blog we will explore how we can create REST APIs for database tables using Oracle SQL Developer & Oracle Rest Data Services (ORDS). Also we are going to test those APIs using Postman.
We will create APIs so that we can GET some data from database table, POST some data in database table i.e. Inserting rows & DELETE Method for deleting record.
ORDS enable REST interface for the Oracle Database, so that you can create RESTfull APIs that uses HTTP/HTTPS requests to GET, PUT, POST & DELETE resource in the database table.
Step 1: You can install the ORDS in three different mode like Standalone, Apache Tomcat or using Oracle Weblogic Server.
I have already installed the ORDS – 20.2 running on Apache tomcat (localhost:8080) server as a web listener for my Oracle Apex. Check this blog for Apex & ORDS Install: Oracle Apex 20.1 Installation. Apex is not pre-requisite for the ORDS. Refer complete documentation here for system requirement & installation : Link
Make sure your ords is up & running.
Step 2: Created one developer user with the role “SQL Developer”
Step 3: Setup/Enable sample schema in database. Enabled it for REST. I’m using Customer-Orders Sample Schema | Oracle 12c in this blog. Database 12c Pluggable.
Steps – Enable Rest Service – Schema
Go to SQL Developer. Connect your schema. Right click on connection name-> Rest Services -> “Enable Rest Service”.
Provide the alias name for your schema. In this blog for simplicity as of now, we are not setting up the authorization. Click on “Enable Schema”
Click on next & you can see the SQL for enabling schema. After that finish.
DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN ORDS.ENABLE_SCHEMA(p_enabled => TRUE, p_schema => 'CO', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'customerorders', p_auto_rest_auth => FALSE); commit; END;
Step 4: Identify individual database tables on which you want to build RESTfull APIs. Enable the Rest Services on the object level. See below one sample example.
Go to SQL Developer. CO schema connected. Right Click on table name-> “Enable Rest Service”. For simplicity as of now not setting up the authorization. Click on “Enable Object”
Provide table alias name: If you want to access with some other name.
Click on next & you can see the SQL also. After that finish.
DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN ORDS.ENABLE_OBJECT(p_enabled => TRUE, p_schema => 'CO', p_object => 'CUSTOMERS', p_object_type => 'TABLE', p_object_alias => 'customers', p_auto_rest_auth => FALSE); commit; END;
I have enabled the “Rest Services” for below tables in the Customer Order Schema. As I’m going to build the APIs using all these tables in my CO schema.
select * from customers; select * from orders; select * from order_items; select * from products;
Exploring REST Services
For example here is my customers table in CO schema, schema alias customerorders provided in Step 3.
Now try to access the REST service url giving details like schema & object name.
Below of this JSON output, there are links for next set of data, as limit is 25.
Getting information for single customer: Customer Id = 306
Building RESTfull API – GET Method
Go to SQL Developer. Click on View. Enable Development tab.
Go to Rest Development Tab & Click on connect.
Create the RESTfull services connection – Using SQL Developer. Provide username & password created in the Step 2.
After successfully login, you can validate the REST Data Services.
Connection setup is done. Lets build our first API.
Requirement: Let say we want to build API, to retrieve customer name & email whose name starts with some text provided at runtime. Like below SQL:
Step 1: Lets build GET Method API. Create ‘New Module’
Provide the module name & prefix for your URI. Check the Publish check box. Pagination is set to 5 for testing purpose. Click on next.
Step 2: Provide URI Pattern here. Click on next.
Step 3: Select Method Handler: GET
Click on Finish.
Step 4: Change the SQL Query. Open the Method.
Module is uploaded. Now test the API to get the customer details based on the name. You can get the details in this tab.
Example 1: Searching users whose name starts with san
Example 2: Searching users whose name starts with adam
Building RESTfull API – Multiple Parameter – GET
Here is the order table. Let say we want a API which can fetch orders based on customer id & status. See below sample SQL for reference.
Follow the same procedure as we have done above API for name search. Create new module provide all details. Edit the GET method & provide the query.
Adding the Input & Output parameters required for the GET Method.
Testing Rest API
Let see first data. For customer 272 we have multiple orders & status.
Example 1: Get COMPLETE Orders for customer 272
Example 2: Get REFUNDED Orders for customer 272
Refer this blog for POST & DELETE Method: Building REST APIs | SQL Developer | POST & DELETE
Testing APIs using Python: Calling Oracle Database REST APIs using Python
Securing REST API: Securing Oracle Database REST APIs using BasicAuth
Tools & Software used:
- SQL Developer : Version 18.104.22.168
- ORDS: select ords.installed_version from dual — 20.2.0.r1781804
- Chrome – Postman & JSON Formatter
- Database : 12c Pluggable Database
Happy Learning! Your feedback would be appreciated!Follow @shobhitsinghIN