Enable RESTful Services on your Oracle Autonomous Database

2

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 SQL developer or APEX 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.

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.

begin 

 insert into CASES (countyname, code, casesno, recoveryno, deathsno) values ( :countyname, :code, :casesno, :recoveryno, :deathsno);
 
 :status_code:=201; 
end;
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!

0 0 vote
Article Rating

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.

Subscribe
Notify of
guest
2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Samuel
Samuel
8 months ago

Good stuff Laban