Building REST APIs using SQL Developer & ORDS

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.

Prerequisite

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;

Now you can access the table using the Rest Services

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.

URL: <host>:<port>/ords/<schema-alias>/<object-alias>

Now try to access the REST service url giving details like schema & object name.

Get Data: http://localhost:8080/ords/customerorders/customers/

Below of this JSON output, there are links for next set of data, as limit is 25.

Metadata – http://localhost:8080/ords/customerorders/metadata-catalog/customers/

Getting information for single customer: Customer Id = 306

http://localhost:8080/ords/customerorders/customers/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: