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:

  • GET -> SELECT
  • PUT -> UPDATE
  • POST -> CREATE
  • DELETE -> DELETE

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

Prerequisites:

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.

SELECT VACANCYID, TITLE, LOCATION, JOBTYPE FROM VACANCIES

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.

To explore more on ORDS check out the documentation.

Also check:

Happy coding!

About Laban Ndwaru

I am a Cloud Solutions Engineer working with a leading cloud solutions company. I have more than 5 years’ experience in the Tech ecosystem. I began my career as a software developer. I am a tech enthusiast, an autodidact whose interests includes Data Management, Cloud Native Development, Mobile and Bots Development, Integration, Low Code Development, AI, Analytics just to say the least.

View all posts by Laban Ndwaru →

2
Leave a Reply

avatar
1 Comment threads
1 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
2 Comment authors
Laban NdwaruSamuel Recent comment authors
  Subscribe  
newest oldest most voted
Notify of
Samuel
Guest
Samuel

Good stuff Laban