Sunday, July 13, 2014

The APEX_JSON package: Generating JSON from PL/SQL

In my previous blog post, I took a look at the new APEX_JSON package that ships with Apex 5.0 and its capabilities for parsing JSON.

In this blog post, I am going to look at how the APEX_JSON package can be used to generate JSON from data in your database using PL/SQL.

There are multiple ways of creating JSON output using the APEX_JSON package. There are several overloaded write() procedures which can output simple types (like varchars, numbers, and dates), as well as those that print complex types (such as xmltype and ref cursors).

First, a simple example which prints a simple JSON structure with some hard-coded values combined with a value based on user input. The page setup in the Apex Page Designer looks like this:

Note the call to apex_json.initialize_output() which disables the automatic sending of JSON headers in the HTTP response. This is normally desirable when you are sending (just) JSON back to the client, but in my test application I want to output the JSON inside a normal HTML page, so I need to disable the JSON headers. The rest of the code consists of simple calls to open/close_object and write() to write the name/value pairs.

The output looks like this:

Next up is the very useful ability to generate JSON based on a ref cursor. This means that basically any SQL query, dynamic or static, can be transformed into JSON output, with just a single line of code. In my simple test application I've set up a PL/SQL region that opens a ref cursor based on user input, and then passes the cursor to the write() procedure.
NOTE: Using dynamic SQL based on unsanitized user input makes your application vulnerable to SQL injection. If possible, you should always use static SQL instead of dynamic SQL. Also, never trust user input and always validate it (see, for example, the dbms_assert package) before using it in a dynamic SQL statement. The example code below does not follow these security best practices, in order to keep the example simple.

Here's the result in the running application. Note that the output even supports nested queries (via the CURSOR statement), which makes it possible to generate complex/nested JSON from a single SQL statement.

Lastly, the APEX_JSON package also supports output of JSON via XML, by passing an xmltype value to the write() procedure. This is very useful in itself, but can also be combined with other PL/SQL features, such as the ability to convert any database object type to XML via the XMLType constructor.

Consider this example, where I create a database object type called T_CAR, with multiple attributes. The example SQL statement shows I can instantiate a T_CAR object and then convert the object into XML.

I can then pass the resulting XML into the write() procedure of the APEX_JSON package to generate a JSON representation of the T_CAR object type.

Here's the output:

This quick look at the APEX_JSON package from Apex 5.0 shows that this new package gives developers a lot of power and flexibility in terms of parsing and generating JSON from PL/SQL.


chrisonoracle said...

Hi Morten,

nice to see that you wrote about the new APEX_JSON APIs. I have just 2 very minor comments:

1. open l_cursor for :P2_SQL

This example can be dangerous, because it allows SQL injection. The reason why we created the cursor write() API was that, contrary to APEX_UTIL.JSON_FROM_SQL, it makes it easy to use bind variables, as in

open l_cursor for
select *
from emp
where deptno = :P2_DEPTNO;

2. Object to XMLType conversion

The XMLType constructor can automatically convert object types, no need for anydata.convertObject:

select xmltype(t_car('Toyota', 'Yaris', 2014, 1234)) from dual

Morten Braten said...


1. Good point. I would normally use static SQL as much as possible, but for testing purposes I just wanted a simple way to run any SQL statement in my test app. I have added a disclaimer about this in the text above.

2. Cool! I was looking at the xmltype documentation, but I missed the fact that the xmltype constructor can take an object type as input directly. I have updated the blog post accordingly.

- Morten

Siva Addepalli said...

Hi Morten,

My comments are not related to the above post. I didn't know what is best way to contact you. I need some help using apex Alexandria library.

In Alexandria library, I want to use apex_util_pkg.get_items procedure. The purpose of this procedure is to get multiple item values from page into custom record type.

In the remarks section of this procedure following comments were posted.
this procedure grabs all the values from a page, so we don't have to write code to retrieve each item separately
since a PL/SQL function cannot return a dynamic type (%ROWTYPE and PL/SQL records are not supported by ANYDATA/ANYTYPE),
we must populate a global package variable as a workaround
the global package variable (specified using the p_target parameter) must have fields matching the item names on the page
I'm new to pl/sql. Can you show me with an example as to how I can invoke this method?

I've to implements a audit like feature across 50 tables.. I want to handle that using this library.

Thanks for your help.


Morten Braten said...

@Siva: Consider the following example:

create table my_table (first_name varchar2(100), last_name varchar2(100), zip_code varchar2(10), country_code varchar2(2));

create or replace package my_package

g_my_table_rec my_table%rowtype;

type t_my_custom_record is record (
favorite_color varchar2(50),
favorite_car varchar2(50),
favorite_fruit varchar2(50)

g_favorite_rec t_my_custom_record;

end my_package;

Given the above, you can now use apex_util_pkg.get_items to populate either of the global variables in the package header, with the following code in an Apex page process;

apex_util_pkg.get_items (:app_id, :app_page_id, p_target => 'my_package.g_my_table_rec');

The items on the page must have names matching the record, for example P1_FIRST_NAME, P2_LAST_NAME, etc.

Or, if you use p_target => 'my_package.g_favorite_rec', then your item names should be P1_FAVORITE_COLOR, P1_FAVORITE_CAR, etc.

After having populated the items, you can then refer to the values using my_package.g_my_table_rec.first_name (ie package_name.record_name.field_name).

Hope this helps.

You can read more about record types here:

- Morten

Siva Addepalli said...

Hi Morten,

Thank you so much for the help. That's exactly what I was looking..