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>

Customer Table

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:

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.

Execute the SQL to test.

Upload Module.

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

URL: http://localhost:8080/ords/customerorders/search/san

Example 2: Searching users whose name starts with adam

URL: http://localhost:8080/ords/customerorders/search/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.

Setup Parameters

Adding the Input & Output parameters required for the GET Method.

API: http://localhost:8080/ords/customerorders/serachorder/orders/

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 20.2.0.175
  • ORDS: select ords.installed_version from dual — 20.2.0.r1781804
  • Chrome – Postman & JSON Formatter
  • Database : 12c Pluggable Database

References:

Thanks!

Happy Learning! Your feedback would be appreciated!

3 thoughts on “Building REST APIs using SQL Developer & ORDS | GET

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s