8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

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.

Related 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."
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/JSON

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

"employee_name":"CLARK"

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.

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

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

<department_list>
  <department department_number="10" department_name="ACCOUNTING" location="NEW YORK">
    <employee_list>
      <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" />
    </employee_list>
  </department>
</department_list>

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

{
   "department_list":[
      {
        "department_number":10,
        "department_name":"ACCOUNTING",
        "location":"NEW YORK",
        "employee_list":[
           {
              "employee_number":7782,
              "employee_name":"CLARK",
              "job":"MANAGER",
              "manager":7839,
              "hiredate":"1981-06-09",
              "salary":2450
           },
           {
              "employee_number":7839,
              "employee_name":"KING",
              "job":"PRESIDENT",
              "manager":null,
              "hiredate":"1981-11-17",
              "salary":5000
           },
           {
              "employee_number":7934,
              "employee_name":"MILLER",
              "job":"CLERK",
              "manager":7782,
              "hiredate":"1982-01-23",
              "salary":1300
           }
        ]
      }
   ]
}

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.

https://example.com/ws/hr/employees/

All interactions with employees will be via this same URI. We identify what we want to do using the HTTP method.

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, HTTP header in the request 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.

https://example.com/ws/hr/delete_employees/1234

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 errors, 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, but this is not SOAP and no rigid format is forced. Useful starting points to see what you should be aiming for are listed below.

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.

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.

APEX Architecture

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?

Gateway Mapping

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.

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.

Here are some of my opinions on the various options available to you.

JSON support in 12c Onward

Each release of the Oracle database since 12c Release 1 (12.1) has introduced a number of new JSON related features. You can read about them in these articles.

Miscellaneous Points

Some related articles are listed below.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.