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.
- Oracle Cloud, try out with the New Always Free tier 🙂
- Oracle Autonomous Processing Database
- Oracle Database Table with some data in it
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;
Test the request on postman;
To explore more on ORDS check out the documentation.