Oracle REST Data Services (ORDS)

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.

oracle@localhost bin]$ ./newpdbords
if unzip successful /home/oracle/Desktop/Database_Track/coffeeshop/coffeeshopApp.zip can be removed
create second ORDS pdb takes two minutes on intel i5
unzipping coffeeshopApp
THIS WILL UNSET TWO_TASK AND CONNECT AS sqlplus / as sysdba TO YOUR DATABASE and run sql…
pdb being created admin user oracle
press y to proceed will cost about 1.1GB disk space and take about 2 minutes [y/n]

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:

OBJECT_NAME OBJECT_TYPE
—————————— ——————————
LOC_COUNTRY_IX INDEX
DEPT_LOCATION_IX INDEX
LOC_CITY_IX INDEX
JHIST_DEPARTMENT_IX INDEX
JHIST_EMPLOYEE_IX INDEX
JHIST_JOB_IX INDEX
LOC_STATE_PROVINCE_IX INDEX
EMP_NAME_IX INDEX
EMP_MANAGER_IX INDEX
EMP_JOB_IX INDEX
EMP_DEPARTMENT_IX INDEX
COUNTRY_C_ID_PK INDEX
DEPT_ID_PK INDEX
REG_ID_PK INDEX
JOB_ID_PK INDEX
EMP_EMAIL_UK INDEX
EMP_EMP_ID_PK INDEX
LOC_ID_PK INDEX
JHIST_EMP_ID_ST_DATE_PK INDEX
ADD_JOB_HISTORY PROCEDURE
SECURE_DML PROCEDURE
EMPLOYEES_SEQ SEQUENCE
DEPARTMENTS_SEQ SEQUENCE
LOCATIONS_SEQ SEQUENCE
JOB_HISTORY TABLE
EMPLOYEES TABLE
JOBS TABLE
DEPARTMENTS TABLE
LOCATIONS TABLE
REGIONS TABLE
COUNTRIES TABLE
SECURE_EMPLOYEES TRIGGER
EMPLOYEES_EMPLOYEE_ID_TRG TRIGGER
UPDATE_JOB_HISTORY TRIGGER
EMP_DETAILS_VIEW VIEW

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:

/u01/userhome/oracle/java/jdk1.8.0_131/jre/bin/java -Duser.language=en -Duser.country=US -jar /u01/userhome/oracle/sqldeveloper/ords/ords.war configdir /home/oracle/sqldeveloper/ords/ords_config
Jan 18, 2018 2:05:23 AM
INFO: Set config.dir to /home/oracle/sqldeveloper/ords/ords_config in: /u01/userhome/oracle/sqldeveloper/ords/ords.war
/u01/userhome/oracle/sqldeveloper/sqldeveloper/bin>
/u01/userhome/oracle/java/jdk1.8.0_131/jre/bin/java -Duser.language=en -Duser.country=US -jar /u01/userhome/oracle/sqldeveloper/ords/ords.war standalone –parameterFile /u01/userhome/oracle/sqldeveloper/ords/params/sqldev_ords_params.properties
2018-01-18 02:05:28.624:INFO::main: Logging initialized @2049ms
Jan 18, 2018 2:05:29 AM
INFO: The document root is serving static resources located in: /u01/userhome/oracle/sqldeveloper/ords/ords_config/ords/standalone/doc_root
2018-01-18 02:05:30.077:INFO:oejs.Server:main: jetty-9.2.z-SNAPSHOT
2018-01-18 02:05:30.622:INFO:/ords:main: INFO: Using configuration folder: /u01/userhome/oracle/sqldeveloper/ords/ords_config/ords
2018-01-18 02:05:30.622:INFO:/ords:main: FINEST: |ApplicationContext [configurationFolder=/u01/userhome/oracle/sqldeveloper/ords/ords_config/ords, services=Application Scope]|
Jan 18, 2018 2:05:30 AM
INFO: Validating pool: |apex|pu|
Jan 18, 2018 2:05:31 AM
INFO: Pool: |apex|pu| is correctly configured
config.dir
2018-01-18 02:05:32.078:INFO:/ords:main: INFO: Oracle REST Data Services initialized|Oracle REST Data Services version : 3.0.9.348.07.16|Oracle REST Data Services server info: jetty/9.2.z-SNAPSHOT|
2018-01-18 02:05:32.079:INFO:oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@68c4039c{/ords,null,AVAILABLE}
2018-01-18 02:05:32.084:INFO:oejsh.ContextHandler:main: Started o.e.j.s.h.ContextHandler@47c62251{/,null,AVAILABLE}
2018-01-18 02:05:32.121:INFO:oejs.ServerConnector:main: Started ServerConnector@4501b7af{HTTP/1.1}{0.0.0.0:9090}
2018-01-18 02:05:32.121:INFO:oejs.Server:main: Started @5564ms

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.

SQL> describe EPMPLOYEES

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:

Resource Template (Created via RESTful Services navigator, right-click a module and select Add Template)	Resource Handler
URI Pattern:	Method:	Source Type:	SQL worksheet:
employees/	GET	Collection Query	select * from EMPLOYEES
	POST	PL/SQL	DECLARE id EMPLOYEES.EMPLOYEE_ID%type; BEGIN INSERT INTO EMPLOYEES (LAST_NAME, FIRST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID) VALUES (:LAST_NAME, :FIRST_NAME, :EMAIL, TO_DATE(:HIRE_DATE,’DD-MON-RR’), :JOB_ID, :SALARY, :MANAGER_ID, :DEPARTMENT_ID) RETURNING EMPLOYEE_ID INTO id; :location := id; :status := 201; END;
employees/:id	GET	Query One Row	select * from EMPLOYEES where EMPLOYEE_ID = :id
	PUT	PL/SQL	BEGIN UPDATE EMPLOYEES SET LAST_NAME = :LAST_NAME, SALARY = :SALARY, JOB_ID = :JOB_ID, DEPARTMENT_ID = :DEPARTMENT_ID WHERE EMPLOYEE_ID = :id; :location := :id; :status := 200; END;
departments/:id	GET	Query One Row	select * from DEPARTMENTS where DEPARTMENT_ID = :id
departments/:department_id/employees/:employee_id	GET	Query One Row	select d.DEPARTMENT_ID , d.DEPARTMENT_NAME , e.EMPLOYEE_ID , e.FIRST_NAME , e.LAST_NAME from DEPARTMENTS d , EMPLOYEES e where e.DEPARTMENT_ID = d.DEPARTMENT_ID and d.DEPARTMENT_ID = :department_id and e.EMPLOYEE_ID = :employee_id

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:

cd /media/sf_MySharedFolder
tar -xvf Postman-linux-x64-5.5.0.tar.gz -C /tmp
mv /tmp/Postman /home/oracle/Postman

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.

{“LAST_NAME”:”TESTINSERT”, “FIRST_NAME”:”TESTFIRST”, “EMAIL”:”TESTMAIL”, “HIRE_DATE”:”25-JUN-15″, “JOB_ID”:”IT_PROG”, “SALARY”:6000, “MANAGER_ID”:103, “DEPARTMENT_ID”:60}

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.