Oracle and JSON: Using PL/JSON

JSON (JavaScript Object Notation) is a lightweight data format that is very well suited for transmitting data over the Internet. Despite the reference to JavaScript in its name, JSON is a language-independent syntax and native support for it has been included in many modern programming languages. In fact, JSON is so popular nowadays that entire database management systems have built their record structure around it, for example Apache CouchDB.

As a Web developer who does a lot of work with PL/SQL, I have recently found myself creating PL/SQL procedures that output JSON data, and making these procedures available as RESTful HTTP services that are then consumed by Ajax functions in the front-end of my applications. This can be a bit of a pain, however, as I need to output the JSON data manually, making it very prone to errors and very difficult to debug in many cases.

Thankfully, an open source library for PL/SQL called PL/JSON is available that resolves some of the issues associated with working with JSON data in an Oracle application. In this post, you will learn how to install and use PL/JSON to work with JSON data in your own PL/SQL applications.

Download and install the library

The latest version of PL/JSON, at the time of writing, is version 0.8.6. The compressed archive is less than 200KB in size. You can download PL/JSON from SourceForge at http://sourceforge.net/projects/pljson/files/. Unzip the library to your hard drive and install it in a SQL*Plus session using the following command:

@install

You should see the following output:

-----------------------------------
-- Compiling objects for PL/JSON --
-----------------------------------
PL/SQL procedure successfully complete

Type created.
No errors.

Type body created.
No errors.

Type created.
No errors.

Type body created.
No errors.

Type created.
No errors.

Type created.
No errors.

Type created.
No errors.

Type created.
No errors.

Type created.
No errors.

Type created.
No errors.

Package created.

Package body created.

Package created.

Package body created.

Type body created.
No errors.

Type body created.
No errors.

Package created.

Package body created.

Assuming you see no errors from running the install script, PL/JSON is now installed in your Oracle database.

Using PL/JSON

With PL/JSON installed, you can now start working with the traditional “Hello, world” example, using the following PL/SQL code:

set serveroutput on
declare
    jsonObj        json;
begin
    --Create a new JSON object, passing the JSON code as the constructor
    jsonObj := json('{"greeting":"Hello World"}');
    --Output the value for the JSON data with the key "greeting"
    dbms_output.put_line(json_ext.get_varchar2(jsonObj, 'greeting'));
end;
/

This produces the following output:

Hello, World

As you can see from the above output, PL/JSON parses JSON data. But what if you want to create a JSON object without actually providing the data in JSON format. Take the following example:

set serveroutput on
declare
    jsonObj       json;
begin
    --Use an empty constructor to create a blank JSON object
    jsonObj := json();
    -- Use the put procedure to add a string, number, boolean and null property to the JSON object
    jsonObj.put('name', 'Joe Lennon');
    jsonObj.put('age', 24);
    jsonObj.put('awesome', json_bool(true));
    jsonObj.put('children', json_null());

    --Print the string representation of the JSON object (pretty-print)
    jsonObj.print;
    dbms_output.put_line('Am I awesome?');
    --Use getters to retrieve the value of the boolean property "awesome" and print the result
    dbms_output.put_line(json_ext.get_json_bool(jsonObj, 'awesome').to_char);
end;
/

The output for the above looks like:

{
  "name" : "Joe Lennon",
  "age" : 24,
  "awesome" : true,
  "children" : null
}
Am I awesome?
true

JSON Arrays

JSON objects can contain data in several types – number, string, boolean, null or array. At this point, you’ve seen how to work with the first four of these types, but not arrays. PL/JSON refers to these as a json_list. Working with json_list objects is very similar to working with a regular JSON object, as shown in the following example:

set serveroutput on
declare
    jsonArray        json_list;
    jsonObj           json;
begin
    jsonArray := json_list('[{"name":"Joe","age":24},{"name":"Jill","age":26}]');
    jsonObj := json.to_json(jsonArray.get_elem(1));
    dbms_output.put_line(json_ext.get_varchar2(jsonObj, 'name'));
    jsonObj := json.to_json(jsonArray.get_elem(2));
    dbms_output.put_line(to_char(json_ext.get_number(jsonObj, 'age')));
end;
/

The above code create a JSON array (or json_list) containing two objects with two properties, name and age. The first object has the name “Joe” and age 24, the second has name “Jill” and age 26. The get_elem function is used to return an item in the JSON array, and the to_json function casts this is a JSON object. In the above example, you first assign jsonObj to the first object in the JSON array, printing out the value of the name property for this object (in this case, the string value “Joe”). Next, you assign jsonObj to the second object in the array, and print out the value of the age property (in this case, age is 26). The output for this example is as follows:

Joe
26

To wrap up this post, let’s have a look at a more complex scenario for using JSON in PL/SQL. Let’s say that you want to create a JSON representation of the result of a SQL statement. The following example demonstrates just that:

set serveroutput on
declare
    jsonArray     json_list;
    jsonObj       json;

    cursor get_data is
        select initcap(lower(object_type)) name, count(*) count
        from all_objects
        where upper(object_type) like 'INDEX%'
        group by object_type;
begin
   jsonArray := json_list();
   for objType in get_data loop
       jsonObj := json();
       jsonObj.put('object_type', objType.name);
       jsonObj.put('count', objType.count);
       jsonArray.add_elem(jsonObj.to_anydata);
   end loop;

   jsonArray.print;

   dbms_output.new_line;

   for i in 1..jsonArray.count loop
       dbms_output.put_line(
           json_ext.get_varchar2(
               json.to_json(jsonArray.get_elem(i)),
               'object_type'
           )||'->'||
           to_char(json_ext.get_number(
               json.to_json(jsonArray.get_elem(i)),
               'count'
           ))
       );
   end loop;
end;
/

In the example above, you first initialize the JSON array to an empty json_list. Next, you loop through the get_data cursor, which selects back the counts for all object types in the database that begin with “INDEX”. For each iteration of this loop, a JSON object is created, the object type name and count are added as properties to the object, and the object is added to the JSON array. Then the string representation of the array is printed as output. Next, you loop through the JSON array itself, and print out the object_type and count properties for each item in the list. The overall output should look as follows:

[{
  "object_type" : "Index Partition",
  "count" : 99
}, {
  "object_type": "Index",
  "count" : 1411
}, {
  "object_type" : "Indextype",
  "count" : 8
}]

Index Partition->99
Index->1411
Indextype->8

It’s important to note that the “print” function which outputs a string representation of JSON objects or arrays merely uses the DBMS_OUTPUT.PUT_LINE Oracle function to dump data to the SQL prompt. There are buffer limitations with this function, particularly on clients before 10g Release 2 (10.2). As a result, on large sets of data you may get an error:

ORA-06502: PL/SQL: numeric or value error: host bind array too small

This is not an issue with PL/JSON, but rather an issue with the Oracle DBMS_OUTPUT.PUT_LINE function. Your JSON objects can be used in other means perfectly fine, but the print member function for the json and json_list objects suffer from this limitation.

PL/JSON provides a ton of excellent features for working with JSON in PL/SQL. The best way to grips with it is to get your hands dirty and try it out for yourself. This post should be a good base to start from, and you can find out more by reading the doc.pdf file that comes with PL/JSON. Also, be sure to check out the examples folder, as it contains some good sample usages that are ready for you to test out. Finally, check the source code itself, particularly the type specification files:

• json.typ
• json_list.typ

As these will give you a good idea of the member functions available for json and json_list objects. If you have any specific questions about PL/JSON, leave a comment and I will do my best to answer them.

Advertisement