Return results of a sql query as JSON in oracle 12c
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;
/
12cR2 (available in the Oracle Cloud) supports this natively.
SQL> select JSON_ARRAY(EMPLOYEE_ID, FIRST_NAME,LAST_NAME) from HR.EMPLOYEES;
JSON_ARRAY(EMPLOYEE_ID,FIRST_NAME,LAST_NAME)
--------------------------------------------------------------------------------
[100,"Steven","King"]
[101,"Neena","Kochhar"]
or
SQL> select JSON_OBJECT('ID' is EMPLOYEE_ID , 'FirstName' is FIRST_NAME,'LastName' is LAST_NAME) from HR.EMPLOYEES;
JSON_OBJECT('ID'ISEMPLOYEE_ID,'FIRSTNAME'ISFIRST_NAME,'LASTNAME'ISLAST_NAME)
----------------------------------------------------------------------------
{"ID":100,"FirstName":"Steven","LastName":"King"}
{"ID":101,"FirstName":"Neena","LastName":"Kochhar"}