Tuesday, 4 July 2017

An Introduction to JSON Support in the Oracle Database

This article provides an overview of the JSON functionality available when using an Oracle database, along with links to relevant articles.

  • What's the point?

In today's development world relational databases are considered a legacy technology by many. PL/SQL is also considered a legacy language. With that in mind, how do we as Oracle DBAs and PL/SQL developers stay relevant? One way is to make sure everything we do is easily accessible.

I'm a fan of the thick-database model. For years I've been telling people to publish their PL/SQL APIs as web services. In the past I was mostly preaching XML (REST and SOAP). With the dominance of JavaScript in the development world, RESTful web services and JSON are everywhere. Fortunately Oracle gives us lots of tools to present SQL and PL/SQL as RESTful web services.

  • What is JSON?

Here is a quote.

"JSON is a syntax for serializing objects, arrays, numbers, strings, booleans, and null. It is based upon JavaScript syntax but is distinct from it: some JavaScript is not JSON, and some JSON is not JavaScript."

JSON data is made up of name/value pairs, where the value can be a JSON object, JSON array, number, string, boolean or null.


A JSON object is made up of one-to-many name/value pairs separated by commas and placed inside curly braces. The name/value pairs don't have to be of the same types.

{"employee_number":7934, "employee_name":"CLARK"}

A JSON array is a comma-separated list of objects inside square brackets. The objects don't have to be of the same type.

  {"employee_number":7782, "employee_name":"CLARK"},
  {"employee_number":7839, "employee_name":"KING"},
  {"employee_number":7934, "employee_name":"MILLER"}

It's really easy to convert JSON to a JavaScript object and vice-versa.

var obj = JSON.parse(jsonText);
var str = JSON.stringify(obj);

Even if you don’t use JavaScript, JSON is still useful. You can think of JSON as "skinny XML". Typically the JSON representation of data requires less characters than the XML equivalent, whether you use tag-based or attribute-based XML. The following example of tag-based XML requires about 768 characters when minified. Minification is the removal of all unnecessary characters, including unnecessary white spaces and new lines.

    <location>NEW YORK</location>

The same data represented as attribute-based XML requires about 537 characters when minified.

  <department department_number="10" department_name="ACCOUNTING" location="NEW YORK">
      <employee employee_number="7782" employee_name="CLARK" job="MANAGER" manager="7839" hiredate="1981-06-09" salary="2450" />
      <employee employee_number="7839" employee_name="KING" job="PRESIDENT" manager="" hiredate="1981-11-17" salary="5000" />
      <employee employee_number="7934" employee_name="MILLER" job="CLERK" manager="7782" hiredate="1982-01-23" salary="1300" />

The JSON representation of the data requires just 470 characters when minified.

        "location":"NEW YORK",

Anything that reduces the number of characters being sent, whilst retaining readability, is interesting where data transfers are concerned, which is why JSON has become a strong favourite when coding web services.

  • What is REST?

REST stands for Representational State Transfer. There are many long and boring discussions about what it is or is not. If you are interested, you can start reading about it here. Instead of that, let's cut to the chase by focussing on JSON-based RESTful web services using HTTP.

To put it simply, we should have a URI which represents a specific resource. Assuming we want to interact with data about employees, we might have something like the following.


All interactions with employees will be via this same URI. We identify what we want to do using the HTTP method.
  1. GET : This is for returning information about a resource. Depending on the parameters used this could return all records, a specific subset of records, or a single record. The important point is a GET should not be used to alter data. In simple examples a GET is often mapped to a SELECT statement.
  2. POST : This is used to create a new resource. So we might use a POST to create a new employee. In simple examples a POST is often mapped to an INSERT statement.
  3. PUT : This is used to amend an existing resource. So we might use a PUT to amend an existing employee. In simple examples a PUT is often mapped to an UPDATE statement. In many cases, a put will amend a resource if it is present, or create it if it isn't. I personally don't like this functionality, but that is just my opinion.
  4. DELETE : This is used to remove an existing resource. In simple examples a DELETE is often mapped to an DELETE statement.
There are other methods, but we will keep things simple by ignoring them. Each method can itself perform several related actions, depending on the parameters in the URI or an associated JSON payload.

To be true to REST, we should never include specific actions in the URI. For example, the following would be considered wrong.


Using this type of URI would be considered Remote Procedure Call (RPC) over HTTP, not REST.

HTTP response codes are used to determine success or failure. Typically a successful GET, PUT or DELETE call will return a 200 success response, while a successful POST call will return a 201 created response. It makes sense to output additional information about the error, rather than relying solely on the HTTP return codes.

REST does not dictate the output returned by a web service call. Some companies will attempt to standardise their approach (see Google JSON Style Guide), but this is not SOAP and no rigid format is forced.

  • What is ORDS?

Oracle REST Data Services (ORDS) is a WAR file (ords.war) that can run in standalone mode, or under Tomcat, WebLogic or GlassFish. These articles explain how to install, configure and run ORDS.
  1. Oracle REST Data Services (ORDS) 3.0 Installation on Tomcat 7 and 8
  2. Oracle REST Data Services (ORDS) : Standalone Mode
  3. Oracle REST Data Services (ORDS) : Configure Multiple Databases
ORDS was originally known as the "APEX Listener", which was a replacement for mod_plsql, an Apache module that was part of the Oracle HTTP Server. You will often see ORDS in APEX architecture diagrams such as this.

As more functionality was added, it got renamed to ORDS, which better reflects its functionality. In addition to fronting APEX and other PL/SQL Web Toolkit applications, ORDS enables RESTful web services for Oracle databases (10g upward) and the Oracle NoSQL Database. It also allows access to the 12c JSON Document Store using SODA.

How does ORDS enable RESTful web services?

URIs passed to ORDS are mapped to SQL or PL/SQL calls. If the URI contains parameters, or if parameters are passed as a JSON payload, they are mapped to the parameters in the SQL or PL/SQL. If a result set is returned by the call, it is transformed to JSON or CSV. If the call is to a PL/SQL Web Toolkit application, like APEX, the output of the call is just pushed out to the client.

  • JSON Web Services

ORDS provides several ways to enable JSON web services.
  1. Oracle REST Data Services (ORDS) : Create Basic RESTful Web Services Using PL/SQL
  2. Oracle REST Data Services (ORDS) : RESTful Web Services Handling Complex JSON Payloads
  3. Oracle REST Data Services (ORDS) : AutoREST
  4. Oracle REST Data Services (ORDS) : Simple Oracle Document Access (SODA)
In addition to the automatic generation of JSON, you can use the APEX_JSON package or the PL/SQL object types for JSON to generate JSON to push through the gateway or store in the database.
  1. APEX_JSON Package: Generate and Parse JSON Documents in Oracle
  2. PL/SQL Object Types for JSON in Oracle Database 12c Release 2 (12.2)
Here are some of my opinions on the various options available to you.
  1. In my opinion, the best option is to use ORDS to present your PL/SQL APIs as JSON web services. This allows you to continue to use your current skill set, but present your code to the outside world in a modern fashion. It also means tools can interact with your APIs without needing an Oracle client installation or Oracle-specific driver.
  2. If you are not using ORDS, you can still generate JSON web services through APEX, manually using the APEX_JSON package, manually using the PL/SQL object types for JSON, or manually using basic string handling. In all four cases, you will still need a gateway to present the web services. This could be mod_plsql and the Oracle HTTP Server or the Embedded PL/SQL gateway. Both methods use a Database Access Descriptor (DAD) to map URIs to PL/SQL calls.
  3. AutoREST may look quite appealing as it allows you to present tables and views as RESTful web services with very little effort, but I think using this option is likely to be a mistake in many cases. You should be trying to present and receive data through APIs that give a real-world representation of the data, not display your underlying implementation of the relational schema. For example, creating a new employee is a single logical operation, but this might result in new records being added to many tables in the database. You can present this as a single operation through your JSON web service and hide the relational complexity beneath a PL/SQL API. That seems like a more natural approach to me, rather than expecting a JavaScript client developer to interact will all the tables independently. Of course, opinions may vary. In a future release it will be possible to AutoREST-enable a stored procedure. That will make AutoREST much more appealing.
  4. The Simple Oracle Document Access (SODA) functionality is useful in a situation where you are predominantly an Oracle shop focusing on relational development, but have a requirement that suits a JSON Document Store. SODA allows you to meet that need, while still staying in the Oracle world, and has the advantage of allowing you to query the JSON data like it were relational data using the SQL/JSON functionality built into the database. I don't see this as a replacement for dedicated JSON stores like MongoDB or CouchDB, but it might find a specific niche.

  • JSON support in 12c

Oracle 12c Release 1 (12.1) introduced a number of new JSON related features.
  1. The IS JSON, JSON_EXISTS and JSON_CONTAINS conditions.
  2. The JSON_VALUE, JSON_QUERY and JSON_TABLE functions.
  3. Simple Oracle Document Access (SODA).
Oracle 12c Release 2 (12.2) includes several enhancements to the JSON functionality in the database including the following.
  1. New manual - JSON Developers Guide
  3. The JSON conditions and functions from 12.1 are now supported in PL/SQL
  4. New PL/SQL object types for JSON (JSON_ELEMENT_T, JSON_OBJECT_T, JSON_SCALAR_T, JSON_KEY_LIST) allow manipulation of JSON documents natively from PL/SQL.

  • Miscellaneous Points

  1. Prior to Oracle database 12c Release 2 (12.2), definitely consider the APEX_JSON package for production and parsing JSON. It requires APEX to be installed, but you don't need to configure or use APEX to use this package.
  2. You can call out to REST web services using the APEX_WEB_SERVICE package. The resulting JSON can be stored directly in the database, or parsed using the APEX_JSON package and stored in relational tables.
  3. ORDS allows several authentication flows, including three OAuth2 flows.
  4. SQL Developer can be used to develop and administer ORDS.