An Oracle blog about New Generation Database Access

  • January 3, 2017

Why Use REST and ORDS to Transform your Oracle Database into a RESTful API Service?

Oracle REST Data Services (ORDS) is a powerful tool that enables
developers with database skills to build enterprise class, data access APIs to
Oracle Databases that today’s modern, state-of-the-art application developers
want to use, and indeed increasingly demand to use, to build applications.     

The data access APIs can fully exploit all the power of
Oracle database technology to deliver the highest levels of reliability,
security, and performance.   Application
developers can use these data access APIs to build enterprise class applications
employing the same methods and techniques that have become dominant in the
startup world for over a decade, at universities where new developers are
trained, at the leading web companies like Google, Facebook, and many more, and
the most advanced mainstream enterprise IT departments.

With ORDS and REST developers can:

1) Choose
the programming language they want to use

Years ago, leading IT departments
built everything in either Java or .Net.   
No more.   Today’s world is much
more diverse.  Javascript is as widely
used today as is Java.   Other languages
such as Python, PHP, Ruby, etc. continue to be very popular.   New languages such as Go from Google and
Swift from Apple have gained substantial market share.   Having one API that supports this entire
diverse world of application development languages is extremely valuable and a
necessity for emerging polyglot and microservice architectures.

2) Use
native language capabilities with no client drivers or complicated proprietary

REST is native to all programming
languages.   Unlike SQL access, there are
no special drivers to install and maintain, or complicated, proprietary
networking to wrestle with.    REST is
totally based on Internet technologies, in particular HTTP, which all
programming languages support today.      

3) Access
the database the same way they access all other external services

REST is by far the dominant
technology used today by application developers to access external services
outside of their programs.   With ORDS
and REST the database can be accessed in exactly the same way.   IT shops can make immediate use of
application developers straight out of university, or from the startup world,
or from the many web companies that only use REST, or from leading enterprise
IT shops with no additional, demanding training to learn about special client
drivers, proprietary networking, or SQL.    
You also don’t need to deal with the resistance you may get from developers
who do not wish to acquire these skills. 

4) Use
JSON for easy integration with programming languages and agile development

Databases have been difficult for
programming languages to integrate with.  
Commonly referred to as the “impedence mismatch”, it was been hard to
map relations in databases to objects in programming languages using SQL.    ORDS and
the new SQL/JSON functions in Oracle Database 12c have eliminated this problem
by enabling all data exchange between the application program and the database
to be done in JSON which is readily mapped to/from programming language
objects.   (JSON is the Object Notation used by JavaScript and is very
similar to the object notation used by other programming languages.)

With Oracle Database 12c, data can
also be stored as JSON in the database.  
This allows developers to employ new, agile, “schemaless” or “schemalast”
development methodologies that enable small teams of developers to safely operate
with more independence and release new functionality faster and in smaller

5) Build
everything Cloud first

With ORDS and REST everything is
built cloud first.   REST uses the HTTP
protocol which is at the core of the Internet and the key “stateless”
technology that gives the Internet its simplicity, reliability, and
scalability.   No adaptors are
needed.   No tunneling.   It is entirely native to the Internet.    ORDS
and REST are also entirely suitable for on premise and mixed hybrid cloud
deployments as well.   


ORDS provides tools for automatically generating basic data
access REST APIs for individual tables and views and manually generating more
advanced APIs, e.g., for executing stored procedures and for accessing nested data
in multiple tables.

This includes tools for defining the URI to identify the REST
resource and route REST HTTP calls, e.g., GET (query), POST (insert), or PUT
(Update), to the SQL or PL/SQL handler routine that processes the request.    Parameters are passed in a variety of ways
to/from the handler routine.   These
include using JSON, or other formats for data objects like images, in the body
of the call, e.g., in a POST or PUT.   
Parameters call also be passed in the URI using query strings or route
patterns.   Results are returned to the
calling application in JSON, or optionally in a CSV format.

Figure 1:   What ORDS

ORDS will auto-generate handlers to perform basic CRUD
operations on single tables or views.   
These include query all rows, query individual row, insert single row,
bulk insert, update, and return metadata.

Handlers can also be manually defined using SQL or
autonomous PL/SQL blocks to perform more advanced functions.    This can include stored procedure calls
with input and output variables.    It
can also include mapping nested JSON documents to/from hierarchical relations
using the SQL/JSON functions in Oracle Database 12c.  

How ORDS Works with new SQL/JSON Functions in Oracle
Database 12c

ORDS REST API handlers can make full use of the new SQL/JSON
functions available in Oracle Database 12c.   
For example, handlers can use the json_table function to insert a JSON
purchase order document containing a nested array of purchase order items into PurchaseOrder
and PurchaseOrderItems tables.   For the
reverse operation, the json_object and json_arrayagg functions can be used to return
a JSON purchase order object containing a nested array of purchase order items
by querying PurchaseOrder and PurchaseOrderItem tables.  

ORDS and REST can also be used to access JSON stored in
Oracle Database 12c in the separate JSON Document Store facility in Oracle
Database 12c using the Simple Oracle Document Access or SODA protocol or in
columns within regular relations or tables.    Many other useful SQL/JSON functions are
also available such as the IS JSON  function can be used as a database constraint
to ensure that any JSON stored in the database is in a proper JSON format.

ORDS Architecture

ORDS is a Java application that runs in Java application
servers.    It runs in a standalone mode,
using Jetty, that is ideal for development and testing.    It can also be used in production
deployment that doesn’t require strong application server management
capabilities.    For more demanding
production deployments, ORDS runs in Weblogic, Tomcat, or Glassfish.

ORDS supports Oracle Database 10gR2 or above.     It also supports the SODA protocol with
the JSON Document Store in Oracle Database 12c.    ORDS can also be used to provide REST API
services for the Oracle NoSQL Database.

Figure 2:  ORDS

ORDS Security

For security, ORDS supports OAuth2 which is the IETF
standard for securing access to REST APIs.  
This includes both server to server (two legged) and end-user to
application (three legged) forms for third party applications.   ORDS also supports first party
authentication where requests originate from the same origin as the REST
service.   And, ORDS supports external
authentication with Identify Managers, single sign on mechanisms, and other

 ORDS is also secure
because it runs in application servers such as Weblogic and Tomcat.    It is protected by the security facilities
these enterprise class application servers provide.

ORDS also does not allow direct database access from client
applications.    Client application can
only make calls to REST APIs defined by specifically authorized Data Access
Developers with high levels of database skills to ensure the APIs are secure.  


ORDS enables you to transform your Oracle Database into a
RESTful API Service that today’s modern application developers will want to use.
  Developers with database skills, such
as SQL, can build data access REST APIs to the database that:

· Fully exploit advanced database capabilities for
maximum reliability, security, and performance

· Are generated automatically for single tables
and views or easily generated manually for more advanced functions.

· Use JSON for data exchange between applications
and the database

· Access JSON stored in the database to facilitate
use of agile development methodoligies

· Are protected by OAuth2 and other advanced security

· Are fully Cloud ready

Application developers can use these data access REST APIS with
any programming language, natively without client drivers or complex proprietary
networking.    Application developers
don’t have to acquire SQL and other advanced database skills.  They call these data access APIs in the same
manner that they access other external services, i.e., with REST and JSON.      

For more information on
ORDS see the ORDS webpages on the Oracle Technology Network here
(or just Google “ORDS”).   These webpages
include a “Downloads” tab section where you can download and try the product, a
“Documentation” tab section which has all of the ORDS documentation, a “Community”
tab section which include the ORDS forum where you can get your technical
questions answered and links to various blogs on ORDS related topics by the ORDS
Product Manager and senior ORDS Developers, and a “Learn More” tab section that
provides links to the highly recommended ORDS Hands-On Lab (HOL) and the ORDS
Video Channel on YouTube.   The ORDS HOL enables
you to download a VM with all the software needed to try out ORDS on your
laptop including exercises that walk you step-by-step through the key ORDS
functionality.   The ORDS YouTube Video
Channel currently  includes an ORDS
overview video and several quick briefs (<10 minute videos) on key ORDS
topics such as installation, security, auto enablement, and multiple datastore

Join the discussion

Comments ( 7 )
  • Luis Silveira Tuesday, February 7, 2017

    Nice post. It's powerful to think how ORDS makes database application development easier than using hardcoded SQL queries.

  • Gaurav Wednesday, March 1, 2017

    Hi Team,

    This is really good technology, we have done little bit of POC with ORDS and everybody really likes this approach.

    But our senior technical manager is quite skeptical about enabling ORDS in production environment because of disaster recovery of Oracle DB.

    Do you have any study or documentation which demonstrates how ORDS will behave during disaster recovery, detail logging and scalability?

    Many Thanks

    Gaurav Modi

  • Gordon Wednesday, March 1, 2017
  • Gaurav Wednesday, March 8, 2017

    Hi Gordon,

    Thanks a lot for your previous pointer on disaster recovery, it was really very helpful.

    We have Oracle WebLogic Server and Oracle DB as infrastructure set up.

    Our team has few more questions about ORDS as below.


    Is there any way we can turn on/off ORDS in production environment without affecting other service or server components?


    How we can migrate ORDS from lower environment to higher environments?


    Will ORDS put any extra load on our DB? Do we need to take any extra precautions?


    What are major knows bugs are there in current version of ORDS?

    We are very existed about this technology, as ORDS will solve many problems we are facing.

    Many Thanks


  • Gordon Smith Wednesday, March 8, 2017


    I am not sure I understand your questions exactly, but keep in mind that ORDS is a Java program running in an application server like Weblogic that accesses the Oracle Database using JDBC. It receives a REST call, looks up the SQL or PL/SQL that has been registered to be executed when such a call is received, and executes that SQL or PL/SQL returning any results.

    1) So, yes you can turn off ORDS just like you would turn off any Java program that accesses the database

    2) I don't know what you mean by lower and higher environments.

    3) The load ORDS puts on your database is the same as any other program executing JDBC

    4) I am not aware of major, known bugs.

    Does this help?

    - Gordon

  • Gaurav Wednesday, March 8, 2017

    Thanks Gordon for your help, We are actively working on this ORDS POC in our Dev env.

    1) how we can turn on / off this ORDS in Weblogic ? any document on this ?

    2) suppose we want to migrate ORDS from Dev environment to System Test environment, ST to prod environment. how we can migrate these ODRS?

    Many Thanks


  • Gordon Smith Friday, March 10, 2017

    See section 1.5 Deploying to Oracle Weblogic Server in the ORDS Installation, Configuration, and Development guide


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha

Integrated Cloud Applications & Platform Services