Thursday, February 11, 2010

REF Cursor to JSON

REF Cursors are cool. They allow you to encapsulate SQL queries behind a PL/SQL package API. For example, you can create a function called GET_EMPLOYEES that returns a SYS_REFCURSOR containing the employees in a specific department:

The client (an application written in Java, .NET, PHP, etc.) can call your API and process the returned REF Cursor just as if it was a normal result set from a SQL query. The benefits are legion. The client no longer needs to contain embedded SQL statements, or indeed know anything about the actual database structure and query text. Privileges on the underlying tables can be revoked. The API can be shared and reused among different clients, whether they are written in Java, .NET, or any number of other languages.

That is, unless your client is Oracle Application Express (Apex). Apex unfortunately lacks the ability to process REF Cursors, or, more accurately, you cannot create report regions in Apex based on REF Cursors. For standard reports, you have to either embed the SQL statement in the region definition, or return the SQL text string from a function (and hope that the string you built is valid SQL when it gets executed). For interactive reports, only embedded SQL statements are supported.

I dislike having to scatter literal SQL statements all around my Apex applications, and not be able to take advantage of a package-based, shared and reusable PL/SQL API to encapsulate queries. I submitted a feature request to the Apex team back in 2007, asking for the ability to base report regions on REF Cursors, but so far this has not been implemented.

The problem, as far as I know, is that Apex uses (and must use) DBMS_SQL to "describe" a SQL statement in order to get the metadata (column names, data types, etc.) for a report region. But not until Oracle 11g did DBMS_SQL include a function (TO_CURSOR_NUMBER) that allows you to convert a REF Cursor into a DBMS_SQL cursor handle. So, as long as the minimum supported database version for Apex is Oracle 10g, support for REF Cursors is unlikely to be implemented.

In the meantime, there are a couple of alternatives:

Option 1: Pipelined functions

It's possible to encapsulate your queries behind a PL/SQL API by using pipelined functions. For example, the above example could be rewritten as...

Option 2: XML from REF Cursor

The DBMS_XMLGEN package can generate XML based on a REF Cursor. While this does not "describe" the REF Cursor per se, it does give us a way (from PL/SQL) to find the column names of an arbitrary REF Cursor query, and perhaps infer the data types from the data itself. A couple of blogposts from Tom Kyte explain how this can be used to generate HTML based on a REF Cursor.

So back to Apex, you could generate a "report" based on a PL/SQL region with code similar to this:

It would also be possible to pass your own XLST stylesheet into the conversion function (perhaps an Apex report region template fetched from the Apex data dictionary?) to control the appearance of the report.

I put "report" in quotes above, because until the Apex team implements report regions based on REF Cursors, you will miss all the nice built-in features of standard (and interactive) reports, such as sorting, paging, column formatting, linking, etc.

For triple coolness, I want to use an API based on REF Cursors in PL/SQL, client-side data manipulation based on JSON, and Apex to glue the two together.

What I need is the ability to generate JSON based on a REF Cursor.

Apex does include a few JSON-related procedures in the APEX_UTIL package, including JSON_FROM_SQL. Although this procedure does support bind variables, it cannot generate JSON from a REF Cursor. (Also, the fact that is is a procedure rather than a function makes it less flexible than it could be. Dear Apex Team, can we please have overloaded (function) versions of these JSON procedures?)

REF Cursor to JSON: The (10g) solution

So I came up with this solution: Use DBMS_XMLGEN to generate XML based on a REF Cursor, and then transform the XML into JSON by using an XSLT stylesheet.

Note: As mentioned above, in Oracle 11g you can use DBMS_SQL to describe a REF Cursor, so you could write your own function to generate JSON from a REF Cursor, without going through XML first. (And perhaps in Oracle 12g the powers that be at Redwood Shores will provide us with a built-in DBMS_JSON package that can both generate and parse JSON?)

@Matt: I have another little project for which this technique ties in really nicely too. Stay tuned to my blog for updates...

Regarding customization of the "ROWSET" tag, this can be easily done by using DBMS_XMLGEN.SETROWSETTAG (see http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_xmlgen.htm#i1013393). When I have the time, I will update my package with this option.

Dear Mortan,My requirement has the data which is more than 4000+ length. I hv tired using XMLTYPE , but that doesn't help for JSON data to send. Please suggest how to send the CLOB of JSON or JSON data which can have more than 4000+.

I know this post was from forever ago, but I am trying to use this Alexandria library to feed some Handlebars JS functions and I keep running into this error no matter what I pass into your functions and I am not sure what could be going on. Please help. Here's my last attempt:

DBMS_OUTPUT.PUT_LINE( l_json );END;Error report:ORA-31011: XML parsing failedORA-19202: Error occurred in XML processingLPX-00601: Invalid token in: '*[count(../*[name(../*)=name(.)])=count(../*) and count(../*);1]'ORA-06512: at "SYS.XMLTYPE", line 187ORA-06512: at "ALEXANDRIA.JSON_UTIL_PKG", line 269ORA-06512: at line 3631011. 00000 - "XML parsing failed"*Cause: XML parser returned an error while trying to parse the document.*Action: Check if the document to be parsed is valid.

Never mind. I finally found the answer. There are &s in the get_xml_to_json_stylesheet and that can cause problems, since that starts a substitution in sqlplus. So I did a SET SCAN OFF and recreated json_util_pkg. It no longer throws an error for me.

Found the answer here:http://code.google.com/p/jqgrid-for-plsql/issues/detail?id=5

About Me

I have been working as a consultant and software developer for 18 years (since 1997), with a special passion for relational databases. I have done a fair share of work using Microsoft tools (including SQL Server, VB, ASP, .NET and C#) and other tools (most notably Delphi), but my favorite tool is the Oracle database with PL/SQL and Apex.