0 
0 While preparing for writing an article about “Oracle API Platform Cloud Service” I wanted to quickly be able to generate example JSON payload’s based on some tables in the “HR’ schema. To achieve this, I came across “Oracle REST Data Services (ORDS)” and wanted to know more about it.
In this article I will explain how I created some REST Data Services with Oracle SQL Developer.
Oracle REST Data Services (ORDS) makes it easy to develop modern REST interfaces for relational data in the Oracle Database and upwards from ORDS 3.0, the Oracle Database 12c JSON Document Store and Oracle NoSQL Database. ORDS is available both as an Oracle Database Cloud Service and on premise.
ORDS is included with both Oracle Database and Oracle SQL Developer installs. It is supported in Weblogic, Tomcat, Glassfish, and also as a standalone application running Jetty in embedded mode.
[http://www.oracle.com/technetwork/developer-tools/rest-data-services/overview/index.html]
Because I was not familiar with Oracle REST Data Services (ORDS), and wanted some quick results, I downloaded a pre-built Oracle VM VirtualBox appliance, named: “Database App Development VM”, which contains the following:
- Database App Development VM Oracle Linux 7
- Oracle Database 12c Release 2 Enterprise Edition (12.2.0.1 Linux x86-64)
- Oracle XML DB
- Oracle SQL Developer
- Oracle SQL Developer Data Modeler
- Oracle Application Express
- Hands-On-Labs (accessed via the Toolbar Menu in Firefox)
For the appliance see:
http://www.oracle.com/technetwork/community/developer-vm/index.html
and
http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html
After importing and starting the appliance in VirtualBox, the desktop looks like:
Before starting the appliance I included a SharedFolder (visible on the desktop as sf_MySharedFolder).
Lab “Oracle REST Data Services”
The appliance contains a number of labs, including one about “Oracle REST Data Services”. Just click on the red Start button, to start the Labs Browser.
The lab “Oracle REST Data Services” contains the following parts:
- Installing and Administering Oracle REST Data Services (ORDS) with SQL Developer
- AutoREST Enable Database Objects
- REST Development
- Insert, Update, and Delete Data using Restful Services
- Securing your RESTful Service in Oracle REST Data Services
These where very helpful in setting up the environment I needed in order to be able to generate example JSON payload’s. In order to set up ORDS using a GUI, I used Oracle SQL Developer.
This article gives a high level overview of the steps I took in the lab. For more detail I refer you to the documentation in the lab.
First I performed the steps in lab part “Installing and Administering Oracle REST Data Services (ORDS) with SQL Developer”. Then I had a go with the other lab parts. As it turned out the “AutoREST Enable Database Objects” feature is for using basic queries and the “REST Development” feature, for using more complex SQL and PL/SQL operations.
Oracle SQL Developer
Oracle SQL Developer enables you to administer Oracle REST Data Services using a graphical user interface. To take full advantage of these administration capabilities, you must use SQL Developer Release 4.1 or later. Using SQL Developer for Oracle REST Data Services administration is optional.
Using this graphical user interface, you can update the database connections, JDBC settings, URL mappings, RESTful connections, security (allowed procedures, blocked procedures, validation function and virus scanning), Caching, Pre/Post Processing Procedures, Environment, and Excel Settings. Oracle SQL Developer also provides statistical reporting, error reporting, and logging.
[https://docs.oracle.com/cd/E56351_01/doc.30/e87809/installing-REST-data-services.htm#AELIG7182]
Installing and Administering Oracle REST Data Services (ORDS) with SQL Developer
As mentioned in the lab part “Installing and Administering Oracle REST Data Services (ORDS) with SQL Developer”, a pluggable database named ords will need to be created for this lab, via script. ./newpdbords.
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jan 15 02:28:56 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL>
…
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
unzipping storm.zip
importing storm.dmp
End of Import
oracle ORDS is available please wait while coffeeshop schema is installed
End of newpdbords
[oracle@localhost bin]$
This script creates schema user “HR” with the following objects:
As part of the lab I created an ORDS Administrator user (ords_admin) to manage configurations and an ORDS RESTful Services User (ords_dev) to create/edit RESTful Services.
I provided the following information:
ORDS Administrator:
Username: ords_admin
Password: oracle
ORDS RESTful Services User:
Username: ords_dev
Password: oracle
Requires Secure Socket Layer: Uncheck
Every time SQL Developer is started, the Oracle REST Data Services can be started via menu Tools | REST Data Services | Run. Via menu View | Processes, you can verify that ORDS is currently running.
The Oracle REST Data Services – Log looks like:
With Oracle SQL Developer, you can administer an installed ORDS via menu View | Rest Data Services | Administration.
Here I created a new REST Data Services Connection, called “ords_admin”.
The ORDS Administration is where you manage your ORDS Global and Database settings.
AutoREST Enable Database Objects
The AutoREST Enable feature introduced in SQL Developer 4.1 allows you to quickly set up REST services on your ORDS enabled database by running a simple wizard. The ORDS service will then query the definitions set up by the Auto REST wizard and displays the data on the browser. This feature allows users wanting to quickly enable database objects and run basic queries. Users wanting more complex operations will need the full REST development setup.
[Lab part “AutoREST Enable Database Objects”, Before You Begin]
To enable AutoREST on a schema and a table, make a new connection (via menu View | Connections | New Connection …) and connect to schema user “HR”.
On the Connections navigator, connect to schema user “HR” by expanding it. Right click schema user “HR” and select REST Services | Enable REST Services… .
The RESTful Services Wizard will appear. Follow the instructions in the lab (providing “hr” as Schema alias).
After stepping through the wizard, right click table EMPLOYEES and select Enable REST Service. Follow the instructions in the lab (providing “employees” as Object alias).
You can review all objects that are REST enabled by viewing the Oracle REST Data Services report, via menu View | Reports | Data Dictionary Reports | Oracle REST Data Services | REST Enabled Objects.
Here you can see that the EMPLOYEES table is REST Enabled (with alias “employees”):
Once a table is REST enabled, with the following URL you can retrieve the table’s metadata:
http://<HOST>:<PORT>/ords/<SchemaAlias>/metadata-catalog/<ObjectAlias>/
For example:
http://localhost:9090/ords/hr/metadata-catalog/employees/
Once a table is REST enabled, with the following URL you can retrieve the table’s data:
http://<HOST>:<PORT>/ords/<SchemaAlias>/< ObjectAlias >/
For example:
http://localhost:9090/ords/hr/employees/
Once a table is REST enabled, with the following URL you can retrieve the table’s data, using an identifying key value:
http://<HOST>:<PORT>/ords/<SchemaAlias>/< ObjectAlias >/< identifying key value>
For example:
http://localhost:9090/ords/hr/employees/100
REST Development
When the basic queries provided by the AutoREST Enable feature doesn’t provide the needed functionality and more complex SQL and PL/SQL operations are needed then the REST Development feature can be used.
The REST Development in SQL Developer 4.1 provides users with the full capabilities of REST. REST endpoints can be defined along with basic operations including create, query, update and delete. More complex SQL and PL/SQL operations can be defined and mapped. The results can be displayed in JSON and other formats.
[Lab part “REST Developement”, Before You Begin]
As part of the lab I created several RESTful Services, but focused only on tables EMPLOYEES and DEPARTMENTS.
Name Null? Type
————– ——– ————
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
SQL> describe DEPARTMENTS
Name Null? Type
————— ——– ————
DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)
With Oracle SQL Developer, you can create a REST Development connection via menu View | REST Data Services | Development.
Here I created a new RESTful Services Connection, called “ords_dev”.
Once you are connected, the Modules and Privileges nodes are displayed on the RESTful Services navigator. Here I created a Resource Module (named “module_demo1”) with Resource Templates and Resource Handlers, based on the instructions given in the lab and with the help of wizards.
In the table below I summarized the RESTful Services I created:
As Data Format, JSON is used.
In order to activate the changes, in the RESTful Services navigator, right-click a module (module_demo1) and select Upload… .
Testing Restful Services with a REST Client tool
To test the Restful Services a REST Client tool can be used.
In the running appliance I installed “Postman” (A powerful GUI platform to make your API development faster & easier, from building API requests through testing, documentation and sharing) in order to send requests to an Oracle REST Data Service. For this (as mentioned in the lab) also the default installed REST Client (WizTools.org RESTClient 3.5) can be used, or other REST Client tools , like for example “Insomnia”.
Installing Postman
First I downloaded “Postman-linux-x64-5.5.0.tar.gz”, which I made available to the Virtual Box appliance, via the SharedFolder.
See:
https://www.getpostman.com/app/download/linux64
In the Virtual Box appliance I opened a terminal and entered the following command’s:
Then I created a Link and placed it on the desktop. When I tried to run it – simply nothing happened, no window opened. In order to be able to run Postman, installing (as user root with password: oracle) “libXScrnSaver” did the trick.
su root
(with password: oracle)
yum install libXScrnSaver
Postman
Postman is the complete toolchain for API developers, used by more than 3 million developers and 30000 companies worldwide. Postman makes working with APIs faster and easier by supporting developers at every stage of their workflow, and is available for Mac OS X, Windows, Linux and Chrome users.
[https://www.getpostman.com/]
After closing the window shown on launch, Postman looks like:
A new request can be created via the upper left corner button/drop-down list New | Request.
I created a collection named “HumanResourceServiceCollection”(in order to bundle several requests) and created a request named “GetAllEmployeesRequest”, providing method “GET” and request URL “http://localhost:9090/ords/hr/demo1/employees/”. After clicking on button Send, a response with “Status 200 OK” is shown:
On the left side panel, all the requests that were sent are shown in the tab “History” (which also can be cleared via button “Clear All”). In the same way as the “GetAllEmployeesRequest”, I created several other request.
The requests belonging to a collection (“HumanResourceServiceCollection”) are shown on the left side panel in the tab “Collections”. These requests can of course be edited.
In the table below I summarized the requests that I created:
| Request name | Method | Request URL |
| GetAllEmployeesRequest | GET | http://localhost:9090/ords/hr/demo1/employees/ |
| CreateEmployeeRequest | POST | http://localhost:9090/ords/hr/demo1/employees/ |
| GetEmployeeRequest | GET | http://localhost:9090/ords/hr/demo1/employees/100 |
| UpdateEmployeeRequest | PUT | http://localhost:9090/ords/hr/demo1/employees/219 |
| GetDepartmentRequest | GET | http://localhost:9090/ords/hr/demo1/departments/30 |
| GetDepartmentEmployeeRequest | GET | http://localhost:9090/ords/hr/demo1/departments/30/employees/119 |
GetAllEmployeesRequest:
CreateEmployeeRequest:
On the tab Headers the Value of the Key “Content-Type” was changed to “application/json; charset=UTF-8”.
On the tab Body | raw the following JSON payload was set.
After the request is sent, a record is created in the EMPLOYEES table:
GetEmployeeRequest:
UpdateEmployeeRequest:
On the tab Headers the value of the key “Content-Type” was changed to “application/json; charset=UTF-8”.
After the request is sent, a record is updated in the EMPLOYEES table:
GetDepartmentRequest:
GetDepartmentEmployeeRequest:
Summary
Thanks to the pre-built Oracle VM VirtualBox appliance, named: “Database App Development VM”, and the thereby provided lab “Oracle REST Data Services” and tooling like Oracle REST Data Services, Oracle SQL Developer and Postman as REST Client tool, I was quickly able to generate example JSON payload’s based in the EMPLOYEES and DEPARTMENT tables from the “HR” schema.
Related posts:
The APEX of Business Value… or: the Business Value of APEX? Cloud takes Oracle APEX to new heights! 
No JDBC based data retrieval in Java applications – RESTstyle, JSON formatted HTTP based interaction from Java to Database 
Everything That Is Really Useful in Oracle Database 12c for Application Developers – Oracle OpenWorld 2014 Using an aggregation function to query a JSON-string straight from SQL 
Reflections after Oracle OpenWorld 2015 – Data Management (Database)
Hi ,
i have created ords enabled procedure and able to execute through post and able to get the value of out parameter . Now i need help to call same procedure through other databases …
Awesome post, and very happy you were able to get this going!
Just wanted to make sure you were aware you can also Dev/manage your REST Services directly via your DB connection in SQLDev. I think it’s easier and provides a more complete experience than the REST Dev panel.
Looking forward to reading more of your blogs!
Hello Jeff,
Thanks for your reply and tip about Rest Data Services Development directly via the Database Connection.
Besides Tables, Views, etc. also a REST Data Services map is visible under the Database Connection, where you can create, edit, etc. RESTful Services, simular to the RESTful Services navigator I mentioned in the article.