Enable RESTful Services on your Oracle Autonomous Database

You can develop and deploy RESTful Services using the native Oracle REST Data Services (ORDS) support on an Autonomous Transaction Processing database.

This is a great use case in application development to access Oracle Database without using native libraries or instant client.

ORDS is a java mid tier application which maps the HTTP(s) verbs to Oracle database transactions using SQL commands as follows:


You can use SQL developer or APEX to enable REST on your ATP database. I will use Oracle Application Express (APEX) on this blog.


Navigate to your Oracle autonomous database on Oracle cloud. Select the service console.

On the dashboard, click on development then select Oracle APEX.

If it’s your first attempt to use APEX, you will have to create a new workspace and username. Then logout from your Oracle ADMIN profile and log in to the APEX using the new credentials created.

APEX workspace has an example RESTful Service Module, oracle.example.hr. You can install the sample service while registering the schema with ORDS to learn more about the functionality.

Once logged in click on SQL Workshop and select RESTful Service. Note that you will have to REST enable your schema. (You can also configure the schema attributes such as Authorization required for metadata access, for the purpose of this demo, I have selected NO)

In my ATP database i have a table called “vacancies” which I want to expose the data using a RESTful service. Find the table DDL script here.


To expose the data using a RESTful service:

Create a new module.

Click on modules and select create module. My module name is called “tutorial

Now that we have the module created, we need to create a template on the under the module.

On the just created module page, locate the “create template” button and create a new template. Give it a name ie. “vacanciesInfo

Now we have a module and a template, now lets create a handler to define the REST methods, ie. GET, POST etc.

Locate the “create Handler” button on the template and create a new handler. Define the handler as shown below.

Restful Data services -> Module -> Template ->Handler

Save or apply changes.

Once done and applied the changes, copy the full url generated and test your RESTful service on a browser. You should get the JSON response of the data from the table in Autonomous database!

That completes the tutorial.

5th May 2020 Update:

What happens if you want to insert into a table in Oracle Database using REST. You use the POST method.

You will create a new handler for POST method, then use an insert PL/SQL code for your table. ie.


 insert into CASES (countyname, code, casesno, recoveryno, deathsno) values ( :countyname, :code, :casesno, :recoveryno, :deathsno);
The ORDs handler page on APEX

Test the request on postman;

The record is inserted, Status 201

To explore more on ORDS check out the documentation.

Also check:

Happy coding!

Author: Labanish

I am a Senior Cloud Solutions Architect at Oracle. Am interested in Cloud Automation, Cloud Native Applications dev and Emerging Tech. I have over 6 years work experience handling different tech roles in East Africa, Romania & now in United Kingdom. All views are mine.

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

%d bloggers like this: