Returning query result as json array with Oracle 10G

Posted on July 27, 2014  •  2 minutes  • 243 words

I love Postgres SQL and one of my favorite feature is ability to return the query result as a JSON array string.

select array_to_json(array_agg(row_to_json(t)))
from (
  select * from my_table
) t

I wonder if I could do the same with Oracle.

Too bad the Oracle version at work is a bit rusty so it’s a bit more troublesome to get it work. We have to first, generate XML using DBMS_XMLGEN and then transform it to JSON using a XLTS stylesheet .

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
as
l_ctx         dbms_xmlgen.ctxhandle;
l_num_rows    pls_integer;
l_xml         xmltype;
l_json        xmltype;
l_returnvalue clob;
begin

-- generate JSON from REF Cursor

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();
else
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;

If you want to read more about this, head over to this blog post on ORA-00001 blogspot.

This utility is available as part of plsql-utils on Google Code along with many other cool PL/SQL utilities. You should check it out if you have time.