Top 60 Oracle Blogs

Recent comments

Using ORDS To Protect the Crown Jewels (your data)

Using ORDS To Protect the Crown Jewels (your data)


Information Technology today is fantastic. Never before have we had so much capability to collect, store, and analyze data. Never before have we had so many wonderful tools for presenting data and our analysis of data.

Today, data represents the “crown jewels” of IT. All of our wonderful systems are less useful if the data is incorrect or inaccurate.
crownjewels 600w" sizes="(max-width: 300px) 100vw, 300px" />

Cornucopia of Available Tools

Today’s applications are built using a polyglot of tools with new tools springing up seemingly every day. Today’s developers often think of data as a resource; they have no inclination, desire, or time to learn SQL or PL/SQL; let alone the details of efficient access. In the haste to make data available to modern web and mobile applications our data gets dumped to flat files or spreadsheets and copied in various ways making it available to applications. Who knows how accurate or up-to-date that data is or who else has access to it? Efficiency and accuracy are sometimes the victims of the haste to make data easily available. Over the last decade, many organizations have adopted a “thick-database” approach by placing business logic in the database rather than in the client or middle tier; all of that work is often skipped around in the quest to make data available to web and mobile applications.

Modern Expectations

Today’s developers are not stupid or lazy; they have unreasonable deadlines like most of us and an ever-changing landscape of new tools to use; “old” skills like SQL and PL/SQL are not high on their list. So what do they want? What do they expect?

Today’s developers want and expect to access data through Web Services via RESTful APIs (more on this later) with data being passed to and from the Web Services using JSON (most of the time).  Many of today’s development tools are designed assuming data is available via RESTful APIs and that parameters and values will be passed via JSON.

What is REST?

So, what is REST? Representational State Transfer (REST) describes a different architecture than the SOA Web Service architecture used in many systems. The term REST originated in a doctoral dissertation about the web written in 200 by a fellow named Roy Fielding; here’s a link to the paper, I think you may find it sleep-inducing .

What most people mean by RESTful APIs loosely describes asynchronous transmission of domain-specific data via HTTP/HTTPS using simple HTTP-style requests without SOAP, WSDL, or any type of session tracking.

How RESTful APIs Work

RESTful APIs work with resources, not procedures. Resources are identified uniquely by URI and HTTP verbs are used to act upon them in a standard way:
•    GET           Query
•    PUT           Create (sometimes Update)
•    POST         Update (sometimes Create)
•    PATCH     Partial Update
•    DELETE    Delete

You Already Know REST

One of the strengths of (what we call) REST is that you already use it everyday:

•    You use a browser to access a “resource” –
o    Each URI includes a website’s domain or IP address
o    The URI’s “resource” (zebra above) describes the context of the request
o    The URI’s “resource” web page (index.html above) identifies the individual resource involved
o    You “GET” (or “POST” or “PUT” etc.) the data corresponding to the URI

REST in Action

URLs use nouns for resources (not verbs for actions); note in the examples below who the URI names the resource; the HTTP verb describes the type of action.

GET /ords/hr/employees/                         Preferred
GET /ords/hr/GetAllEmployees/             Not Preferred

As with many good things in IT; standards are the key here. Uniform APIs for operations: GET, POST, PUT, DELETE make it easy for people to read,  understand, and create code. Requests are stateless; any necessary state information is contained in URI’s and the REST API’s parameters (as Oracle’s Jeff Smith says, “it’s all about the hyperlinks”).

Standard error code families help developers properly handle exceptions in code:

100’s – Informational
200’s – Success
300’s – Redirection
400’s – Client Error
500’s – Server Error

Reviewing Web Services

Web Services, (often called Microservices today) are the targets of RESTful API calls. So, what is a Web Service anyway? Here’s a definition from the W3C (World-Wide Web Consortium) website: “A Web service is a software application identified by a URI, whose interfaces and binding are capable of being defined, described and discovered by XML artifacts and supports direct interactions with other software applications using XML based messages via internet-based protocols.” Note that the W3C’s definition reflects the days of SOAP and XML but the notion is the same. We want to access code without regard to the programming language it is written in or the operating system it might be running on.

Web Service interactions are performed by sending message via URI and a “payload” contained in parameters, headers, and responses. Web Services interactions were originally XML-based; today, JSON-based interactions are pretty much the standard for Mobile and becoming the standard for most other web applications too.

Why XML and/or JSON?

The plain-text of XML and JSON makes them ideal for cross-language/platform use. Every operating system and every language knows how to work with character stream data. Using XML or JSON with standard interfaces like SOAP and REST makes Web Services potentially programming language and operating system independent.

Emergence of JSON

JSON (JavaScript Object Notation) has become the mechanism of choice for sharing and passing data to-and-from mobile applications. JSON is an international, open standard governed by the ECMA; here is a link where you can learn more

JSON-XML Similarities

JSON and XML share many similarities. JSON is text only, just like XML and thus is an excellent vehicle for data interchange. JSON and XML are “human readable” and “self-describing” (sort of, I’m not sure anybody really want to read them). JSON and XML are hierarchical (data sets nested within data sets). JSON and XML both offer validation capability; XML’s is more mature and capable today.

JSON-XML Dissimilarities

XML is verbose, JSON is less-verbose. JSON has no end tags, end tags are required in XML making JSON quicker to read and to write.  Reading XML documents often requires “walking the DOM” – reading JSON does not. JSON works more easily and is faster than XML when working with AJAX.

Perhaps the biggest and most-meaningful XML-JSON difference is XML’s standard requiring a document to be well-formed XML. Each time an XML document is processed the code is required by standard to make sure that the XML document is “well-formed” (follows specific rules and is syntactically correct); further, if an XML document is passed to a subroutine it too is required to make sure the XML document is “well-formed” before it may begin processing it (and so on, and so on, and so on). This constant testing of the XML document “well-formed-ness” slows things down. JSON has no such requirement; however, some documents might be unreadable and cause errors.

XML vs JSON Verbosity

Here are two sample documents, one XML and one JSON containing EXACTLY the same data; note the actual difference in size. The principle reasons to use JSON are the speed JSON gains by not bothering to check for well-formed-ness and the ready availability of tooling that works with JSON.

XML File (300 characters without counting spaces)

 <?xml version="1.0"?>
       Learning XML
       Eric T. Ray
       XML Bible
       Elliotte Rusty Harold
      IDG Books
       XML by Example
       Sean McGrath

JSON File (228 characters without spaces)

    [ {"book": {
       "name":"Learning XML",
       "author":"Eric T. Ray",
       "publisher":"O'Reilly" }
    {"book": {
       "name":"XML Bible",
       "author":"Elliotte Rusty Harold",
       "publisher":"IDG Books" }
    {"book": {
       "name":"XML by Example",
       "author":"Sean McGrath" }


What if you could expose your Tables, Views, and stored PL/SQL (thick database) as web services? What if you could accept input in JSON and generate JSON easily? What if you could make data available to today’s developers safely in a form they’re familiar with and want to use?

ORDS to the Rescue!ordslogo 150w" sizes="(max-width: 68px) 100vw, 68px" />

Oracle’s REST Data Services (ORDS) provides a solution to:

  • Expose Tables and/or Views via REST APIs
  • Expose PL/SQL via REST APIs
  • Transfer data using JSON or other datatypes

Introduction to ORDS

ORDS provides RESTful access to Oracle data that modern tools expect. ORDS maps SQL to REST style HTTP: GET, POST, PUT, DELETE calls that may return results in JSON or other data types.

What is ORDS?

ORDS was originally known as APEX Listener in 2010.  Support for JSON was added in 2011. The product officially became Oracle Rest Data Services (ORDS) in 2014. ORDS works with APEX (Application Express), SQL Developer, or PL/SQL via a package named ORDS. This article primarly shows ORDS via SQL Developer; a later post will illustrate ORDS via APEX.

ords1 600w" sizes="(max-width: 558px) 100vw, 558px" />

  • ORDS is included in Oracle DBaaS instances
  • ORDS may be installed easily in any instance
  • ORDS runs in any Java EE container (Weblogic, Glassfish, Tomcat, etc.)
  • Developers may create standalone version
  • ORDS is an enhanced version of Oracle’s Java mod_plsql Apache module

ords2 600w" sizes="(max-width: 562px) 100vw, 562px" />

  • ORDS data is stored in Oracle (relational tables and columns)
  • ORDS defines  URI-to-SQL mapping with SQL results mapped to JSON (or other data types)
  • Applications use URIs via HTTP(S) to GET and POST data


ORDS provides a subset of the typical HTTP-style API calls.

Here’s an example of a typical REST “payload” returned from a query

{  "items": [
 "employee_id": 100,
 "first_name": "Steven",
 "last_name": "King",
 "email": "SKING",
 "phone_number": "515.123.4567",
 "hire_date": "1987-06-17T04:00:00Z",
 "job_id": "AD_PRES",
 "salary": 24000,
 "commission_pct": null,
 "manager_id": null,
 "department_id": 90,
 "links": [
 .... More Here ....

ORDS Installation

As stated before, ORDS comes “out of the box” with Oracle DBaaS. Or, your DBAs might already have installed ORDS. F not, installation is easy with SQL Developer (partially shown below) or APEX (not shown in this article).

ORDS may be installed using a simple set of wizards (not all steps shown here); here’s the first part of the process.

ordsinstall1 452w" sizes="(max-width: 430px) 100vw, 430px" />
Once ORDS is installed a review screen describes the installation.

ordsinstalllast 600w" sizes="(max-width: 437px) 100vw, 437px" />

ORDS Administration via SQL Developer

SQL Developer provides two tools for ORDS; one for Development and one for Administration.

ordsadmin1 300w" sizes="(max-width: 341px) 100vw, 341px" />

ORDS Reporting in SQL Developer

SQL Developer provides many built-in reports including on for ORDS as shown here.


ordsreports2 600w" sizes="(max-width: 471px) 100vw, 471px" />

Enabling ORDS for a Schema in SQL Developer

Using SQL Developer’s Connection Navigator right-click on a connection to set up ORDS for that schema. You will provide an “alias” that will be used to describe the resource in URIs and optionally set additional security by requiring that users be part of an Oracle role.

ordsenableschema 600w" sizes="(max-width: 379px) 100vw, 379px" />

Enabling ORDS for a Table and/or View in SQL Developer

SQL Developer will automatically set up ORDS calls for selected database objects so that you don’t have to. Using the SQL Developer Object Navigator right-click on an object to enable ORDS. You will provide an “alias” for the resource allowing a meaningful name rather than our sometimes non-obvious standards-laden Table/View names. You will also have the opportunity to require additional security requiring users to be part of an Oracle role.

ordsenable1 277w" sizes="(max-width: 223px) 100vw, 223px" />ordsenable2a 600w" sizes="(max-width: 300px) 100vw, 300px" />ordsenable3 600w" sizes="(max-width: 300px) 100vw, 300px" />

Manual Mapping of ORDS in SQL Developer

Developers may manually map ORDS for a query, PL/SQL procedure/function call, or event anonymous PL/SQL. This avoids the “SELECT *” of the automatically generated ORDS mappings described in the previous section.

ordsdeveloper1 355w" sizes="(max-width: 213px) 100vw, 213px" />ordsdeveloper2

ordsdeveloper3 600w" sizes="(max-width: 300px) 100vw, 300px" />ordsdeveloper4 600w" sizes="(max-width: 300px) 100vw, 300px" />


Oracle provides a PL/SQL package that allows creation and manipulation of ORDS in code. This PL/SQL “ORDS” package is used for defining and configuring RESTful services so that ORDS mapping may be put into scripts and may be repeated quickly and easily. Complete documentation of the ORDS package may be found at this URL: – AELIG90180

Here’s an example of the PL/SQL API in Use:

       p_module_name    => 'samples.employees',
       p_base_path      => '/samples/employees',
       p_pattern        => '.',
       p_items_per_page => 5,
       p_source         => 'select * from hr.employees
                              order by employee_id');


Security is an important part of todays applications. ORDS provides a standardized mechanism for URIs allowing application designers to tie into Oracle Identity Management via WebGate to access Single Sign On (SSO) or to use OAuth2 (built-in).

ORDS Sample Output

Use URL to view all table rows:

ordsemployees 548w" sizes="(max-width: 300px) 100vw, 300px" />
Use URL to view row contents:

ordsemployees200 600w" sizes="(max-width: 300px) 100vw, 300px" />

Wrapping it all Up

With ORDS the Crown Jewels are safe! crownjewelschina2new



ORDS allows controlled access to our data by providing exactly what the developers need and want:

  • Web Services available via RESTful APIs
  • Data in and out in JSON as well as other data types

ORDS helps enable your newest applications. Give it a try today!