Background
I need to fetch a few thousands rows from Oracle and convert them to JSON for use in SlickGrid. Currently I am fetching the rows in PHP, converting it from ISO to UTF-8 with iconv and exporting to json with json_encode. The whole operation takes about 1 second on DB side and 5 seconds to generate JSON. It is way to long.
The question
I have read that Oracle 12c supports JSON, but I cannot find exactly what I need.
Is there a way to return the result of a standard sql query in a json format?
supposedly I would like to issue a query similar to this:
SELECT * from table AS JSON
and receive a valid json similar to this:
[{"col1": "value1", "col2": 2}, {"col1": "valueOfRow2", "col2": 3}]
An important thing is that I need to have the unicode sequences escaped for me, as I use ISO-8859-2 charset on the client side, and JSON have to be in either UTF-8 or have the sequences escaped.
Oracle 12c version 12.1.0.2 (the latest version as of 11.11.2014) adds JSON support: https://docs.oracle.com/database/121/NEWFT/chapter12102.htm#BGBGADCC
It's been available since October 17th. https://blogs.oracle.com/db/entry/oracle_database_12c_release_1
If you are unable to patch/work with that version there is an excellent package written by Lewis Cunningham and Jonas Krogsboell: PL/JSON * http://pljson.sourceforge.net/
It's an excellent package (I have used it in numerous database installations).
The examples included are good and cover most scenarios.
declare
ret json;
begin
ret := json_dyn.executeObject('select * from tab');
ret.print;
end;
/