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:

function get_employees (p_deptno in number) return sys_refcursor
l_returnvalue sys_refcursor;

open l_returnvalue
select empno, ename, job, sal
from emp
where deptno = p_deptno;

return l_returnvalue;

end get_employees;

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...

create type t_employee as object (
empno number(4),
ename varchar2(10),
job varchar2(9),
sal number

create type t_employee_tab as table of t_employee;

function get_employees (p_deptno in number) return t_employee_tab pipelined

for l_rec in (select empno, ename, job, sal from emp where deptno = p_deptno) loop
pipe row (t_employee (l_rec.empno, l_rec.ename, l_rec.job, l_rec.sal));
end loop;


end get_employees;

And used from Apex (in a report region) via the TABLE statement:
select *
from table(employee_pkg.get_employees (:p1_deptno))

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 blog posts 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:

l_clob clob;
l_rc   sys_refcursor;
l_rc := get_employees (:p1_deptno);
l_clob := fncRefCursor2HTML (l_rc);
htp_print_clob (l_clob);

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.

Option 3: JSON from REF Cursor

Bear with me, I am finally getting to the point of this blog post.

JSON is cool, too, just like REF Cursors. It's the fat-free alternative to XML, and JSON data is really easy to work with in Javascript.

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?)

In the meantime, for Oracle 10g, I created the JSON_UTIL_PKG package.

Here is the code for the REF_CURSOR_TO_JSON function:

function ref_cursor_to_json (p_ref_cursor in sys_refcursor,
p_max_rows in number := null,
p_skip_rows in number := null) return clob
l_ctx         dbms_xmlgen.ctxhandle;
l_num_rows    pls_integer;
l_xml         xmltype;
l_json        xmltype;
l_returnvalue clob;


Purpose:    generate JSON from REF Cursor


Who     Date        Description
------  ----------  -------------------------------------
MBR     30.01.2010  Created


l_ctx := dbms_xmlgen.newcontext (p_ref_cursor);

dbms_xmlgen.setnullhandling (l_ctx, dbms_xmlgen.empty_tag);

-- for pagination
if p_max_rows is not null then
dbms_xmlgen.setmaxrows (l_ctx, p_max_rows);
end if;

if p_skip_rows is not null then
dbms_xmlgen.setskiprows (l_ctx, p_skip_rows);
end if;

-- get the XML content
l_xml := dbms_xmlgen.getxmltype (l_ctx, dbms_xmlgen.none);

l_num_rows := dbms_xmlgen.getnumrowsprocessed (l_ctx);

dbms_xmlgen.closecontext (l_ctx);

close p_ref_cursor;

if l_num_rows > 0 then
-- perform the XSL transformation
l_json := l_xml.transform (xmltype(get_xml_to_json_stylesheet));
l_returnvalue := l_json.getclobval();
l_returnvalue := g_json_null_object;
end if;

l_returnvalue := dbms_xmlgen.convert (l_returnvalue, dbms_xmlgen.entity_decode);

return l_returnvalue;

end ref_cursor_to_json;

Examples of usage

Get a small dataset

l_clob clob;
l_cursor sys_refcursor;
l_cursor := employee_pkg.get_employees (10);
l_clob := json_util_pkg.ref_cursor_to_json (l_cursor);
dbms_output.put_line (substr(l_clob, 1, 200));


A large dataset, with paging

l_clob clob;
l_cursor sys_refcursor;
l_cursor := test_pkg.get_all_objects;
l_clob := json_util_pkg.ref_cursor_to_json (l_cursor, p_max_rows => 3, p_skip_rows => 5000);
dbms_output.put_line (substr(l_clob, 1, 1000));


It works with nested datasets, too.. !

select d.deptno, d.dname,
cursor (select e.*
from emp e
where e.deptno = d.deptno) as the_emps
from dept d

l_json clob;
l_json := json_util_pkg.sql_to_json ('select d.deptno, d.dname,
cursor (select e.*
from emp e
where e.deptno = d.deptno) as the_emps
from dept d');
dbms_output.put_line (substr(l_json, 1, 10000));


Passing a REF Cursor directly to the function call by using the CURSOR function:

select json_util_pkg.ref_cursor_to_json(cursor(select * from emp))
from dual


Download the package

You can download the complete package, including the XSLT stylsheet, here (spec) and here (body).

Update 12.02.2011: This package can now be downloaded as part of the Alexandria library for PL/SQL.

Note that to compile the packages you need the following SQL type defined in your schema:

create type t_str_array as table of varchar2(4000);


Gareth said...

Excellent post!

Matt said...

Morten, you're brilliant mate! This will tie in really nicely with PLJSON. Have you thought about allowing customization of the object key name "ROWSET"?

Morten Braten said...

@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 When I have the time, I will update my package with this option.

Matt said...

Let me guess jqGrid integration?? FYI that's what I'm working on too...

not using plugins though. I wouldn't mind having a chat if my guesstimate is correct :)

Morten Braten said...

@Matt: That's not a bad guess...!

If you want to discuss jQGrid further you can send me your email address in a comment (and then delete the comment immediately after to avoid making the address public).

Anonymous said...

Very nice and helpful post...
Thanks Morten

Saroj Kumar Nayak

Jonas Krogsboell said...

Hi Morten

Is it ok with you that I include your JSON code in (PL/JSON) in a slightly modified form?

Morten Braten said...

@Jonas: Sure, no problem, go ahead and use it! :-)

mnolan said...

Hi Morten

Thanks for posting the code, I've started using this. I encountered a problem with "sql_to_json" and bind values which were NULLs. I was getting this....

ORA-19202: Error occurred in XML processing
ORA-01008: not all variables bound

The fix was to change

for i in 1..p_param_names.count loop
dbms_xmlgen.setbindvalue (l_ctx, p_param_names(i), p_param_values(i),'');
end loop;


for i in 1..p_param_names.count loop
dbms_xmlgen.setbindvalue (l_ctx, p_param_names(i), nvl(p_param_values(i),''));
end loop;


Anonymous said...

Hi Morten

Do you have a fix for the XLST stylesheet when there's only one row, as we get a diffrenet JSON object structure?

Thanks in advance

Morten Braten said...

@Matt: Thanks for the feedback. I have added your fix to my source code repository, so it will be included in the next release of the jQGrid Integration Kit.

Morten Braten said...

@Anonymous/Matt: There is indeed a slightly different JSON output format when there is only one row.

I tested with the following:

l_clob clob;
l_clob := json_util_pkg.sql_to_json('select sysdate from dual');
dbms_output.put_line('One row: ' || substr(l_clob,1,32000));
l_clob := json_util_pkg.sql_to_json('select sysdate from dual union select sysdate - 1 from dual');
dbms_output.put_line('Two rows: ' || substr(l_clob,1,32000));


One row: {"ROWSET":{"ROW":{"SYSDATE":"28.07.2010"}}}
Two rows: {"ROWSET":[{"SYSDATE":"27.07.2010"},{"SYSDATE":"28.07.2010"}]}

I don't have a fix for the XLST at this point, but you can use the following JavaScript to handle the difference on the client side:

var rs = eval(('the JSON string here'));

if ("undefined" == typeof(rs.ROWSET.length))
for (i in rs.ROWSET)
rs.ROWSET = new Array(rs.ROWSET.ROW);

for (var i=0; i < rs.ROWSET.length; i++) {

This will convert a single row into an array of rows with one element.

mnolan said...
This comment has been removed by the author.
mnolan said...

Thanks mate for the javascript workaround, unfortunately I won't be able to use it....

Do you have any intentions of modifying the XSLT stylesheet in the near future?

I had a look at modifying it but it's out of my comfort zone as I'm new to using XLST...

That said I need this for a project I'm working on so I just need to know if I can "cheekily" rely on you for a fix or whether I'll have to roll up my sleeves and get my hands dirty :)


Morten Braten said...


Unfortunately, I'm no XLST expert either, and I didn't write the XSLT piece (see the source code for credits/license).

You might find it helpful to start with a Google search on "xml to json xslt". I suppose there are specialized forums out there somewhere which can help with this (or perhaps try ?).

Of course, let me know if you find a solution, so I can incorporate the fix into the codebase.

- Morten

Andrew said...

Excellent code. Thank you very much.

I also ran across the issue of a different JSON result for one row and multiple rows. I changed the ret.add_elem() line in the ref_cursor_to_json function to return ROWSET/ROW and it seemed to work.

if(l_num_rows = 1) then
declare ret json_list := json_list();
return ret;

andry said...

json is a very interesting language to be used. very good tutorial and can hopefully help me in building json in the application that I created for this lecture. thank you

Anonymous said...

Excellent library. Thx for sharing that.

Have you had any luck getting this to work on queries that return text columns with embedded special chars?

For example:

select 'Johnson & Johnson' as
company_name from dual;

generates an LPX-00242 parsing error.

Other than wrapping every column in the query with an encode stmt before submitting for conversion to JSON is there a way to handle this in the package?


Kias said...

Issue of a different JSON result for one row and multiple row, can by solved by modification of ref_cursor_to_json function. Like this:

if(l_num_rows = 1) then
ret json_list := json_list();
js json := json();
js.put('ROWSET', ret);
dbms_lob.trim(l_returnvalue, 0);
return l_returnvalue;
return l_returnvalue;
end if;

Manny said...

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+.

Morten Braten said...

@Manny: You could use the SYS.HTP package to print your CLOB in small chunks. You need to construct the JSON "markup" yourself in this case.

- Morten

STAN said...

Morten, this was a very well done piece of code. I seldom compile donated code flawlessly. Great job.

I'm trying to use your code as a bridge to Google Charts (Visualization) and the JSON format required is organized differently. Here's a short example:

"cols": [
"rows": [

I suspect the XSLT transform might make quick work of this, but I'm a total novice at that. Do you know of anyone else who has used your tool successfully in this way?

Anonymous said...

Hi Morten,
I am new to JSON, can you please tell me from where I can get the "get_xml_to_json_stylesheet" used in function "ref_cursor_to_json"..

Where can I download this?

Thanks in advance!


Morten Braten said...

@Bharat: As stated near the end of the blog post, the full source code is part of the Alexandria Library for PL/SQL and can be downloaded here:

- Morten

mtalat said...

dear morten

first thank you..nice job

when using
select SQL_TO_JSON('select code from table')from dual;

if code have null value come first record return element whit out { .

i cahange your code



new code commeted by name.

Mohammad Talat

Stefan Knecht said...

Has anyone used this on 12cR1 ?

I get odd results with even the basic:

select json_util_pkg.ref_cursor_to_json(cursor(select sysdate from dual)) from dual;



Marco Gralike said...

@Stefan Knecht

might be due to the JSON db implementation in (aka not visible but imlemented in earlier versions of 12.1).

SQL> select keyword
2 from v$reserved_words
3* where keyword like '%JSON%'


16 rows selected.

Anna said...

Wonder if it is possible to get JSON to ref cursor function ?

Avik Biswas said...

YOUR POST REALLY HELPED ME.Thanks for sharing this valuable information with us. Waiting for more cool stuff like this .

Anonymous said...

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:

Error starting at line 1 in command:
l_json CLOB;
OPEN l_cursor FOR
DECODE( COUNT(1), 1, MAX( cc.code ), 'MULT' ) || ' - ' ||
DECODE( COUNT(1), 1, MAX( ), 'Multiple Charge Codes' ) AS code_heading,
pjs.projectnumber || ' - ' || pjs.title AS project_heading,
FROM project.project pjs
WHERE pjs.projectnumber = pj.projectnumber ) AS projects
FROM project.project pj
INNER JOIN project.proposal p
ON p.proposalnumber = pj.proposalnumber
INNER JOIN project.vw_project_status_chg_period vpscp
ON vpscp.projectnumber = pj.projectnumber
AND vpscp.periodid = 244
AND vpscp.statusid = 'W'
INNER JOIN project.vw_proposal_alloc_period_RT vpap
ON vpap.proposalnumber = p.proposalnumber
AND vpap.periodid = 244
INNER JOIN project.chargecode cc
ON cc.code = vpap.code
INNER JOIN project.vw_proposal_unit_root vpur
ON vpur.proposalnumber = p.proposalnumber
AND vpur.branch_root = ( SELECT
TO_NUMBER( gp.value )
FROM basic.global_parameters gp
WHERE pj.typeid <> 'C'
GROUP BY pj.projectnumber, pj.title
ORDER BY pj.projectnumber;

l_json := json_util_pkg.ref_cursor_to_json( l_cursor );

Error report:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00601: Invalid token in: '*[count(../*[name(../*)=name(.)])=count(../*) and count(../*);1]'
ORA-06512: at "SYS.XMLTYPE", line 187
ORA-06512: at "ALEXANDRIA.JSON_UTIL_PKG", line 269
ORA-06512: at line 36
31011. 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.

Anonymous said...

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:

APEX Developer In Virgina said...

Quick question.. I have your package and am using it to build a json object that I need to pass to a restful web service (using a POST vs a GET request..). Can you show how I would pass this object via pl/sql and receive back a blob type file for download by the user of the procedure??

Thank you,

Tony Miller
LuvMuffin Software
Ruckersville, VA

Morten Braten said...

@Tony: If I understand your question correctly, you would first call JSON_UTIL_PKG to generate a JSON clob. Then you use APEX_WEB_SERVICE.MAKE_REST_REQUEST to call the web service, passing the JSON clob as the p_body parameter. You get a CLOB back (note: in Apex 5 there is actually a function called MAKE_REST_REQUEST_B that returns a BLOB instead of a CLOB). Then you convert the CLOB to a BLOB using SQL_UTIL_PKG.CLOB_TO_BLOB. And finally you pass the BLOB to the OWA_UTIL_PKG.DOWNLOAD_FILE procedure to send it to the user's browser.

Hope this helps,

- Morten

Anonymous said...

error occured also about g_json_null_object had to add it in package spcs instead of body

Anonymous said...

Is it works for Orcle 9i?

Sirius said...

Hi guys,

I'm trying to use the code but I have a question, I did this simple test

l_json clob;
l_json := json_util_pkg.sql_to_json ('select d.deptno, d.dname,
cursor (select e.*
from emp e
where e.deptno = d.deptno) as the_emps
from dept d');
dbms_output.put_line (substr(l_json, 1, 10000));

using my own tables but I see that l_json is declared as a clob but the function sql_to_json is returning a json_list, =| am I wrong?

Thanks for your response

Morten Braten said...

@Daniel: json_util_pkg.sql_to_json is indeed returning a clob, as you can see here:

- Morten

Sirius said...

you are right, I think I have a previous version mine says

function sql_to_json (p_sql in varchar2,
p_max_rows in number := null,
p_skip_rows in number := null) return xxtt_json_list
v_cur sys_refcursor;
open v_cur for p_sql;
return ref_cursor_to_json(v_cur, p_max_rows, p_skip_rows);

end sql_to_json;

thanks a lot =)