Generating JSON from refcursor in R12.2 without APEX_JSON

Hi Jeremy,

Jeremy Treague wrote:

 

Below is an example of how JSON could be generated based on the content of a cursor through the use of the APEX_JSON package that is part of APEX 5:

https://oracle-base.com/articles/misc/apex_json-package-generate-and-parse-json-documents-in-oracle#ref-cursor-to-json

In v12.2 of the Oracle Database there are new types and functions introduced for working with JSON data:

https://oracle-base.com/articles/12c/sql-json-functions-12cr2

Has anyone had experience with generating JSON in 12.2 based on a cursor through the use of these new functions (i.e. without installing APEX 5 and using APEX_JSON package)? If so, can you share an example?

Thus far I have been able to do it by opening the cursor and iterating over values to construct an instance of the JSON_OBJECT_T type. But I would like to know if there if there is a way to do this without manually constructing this type - similar to how it could be done using a call such as: APEX_JSON.write('employees', l_cursor);

You can use PL/JSON utility (PL/SQL utility for JSON) if you don't want to use the APEX_JSON package provided with Oracle APEX 5+.

Refer: https://jsao.io/2015/07/relational-to-json-with-pljson/

NOTE: There JSON_DYN package provided in this utility which takes query as input and produce JSON. But it does not support REFCURSOR.

Also following are the series of blog posts done by Dan McGhan on the same (which will help you):

• https://jsao.io/2015/07/relational-to-json-in-oracle-database/
• https://jsao.io/2015/07/relational-to-json-with-node-js/
• https://jsao.io/2015/07/relational-to-json-with-ords/
• https://jsao.io/2015/07/relational-to-json-with-apex_json/
• https://jsao.io/2015/07/relational-to-json-with-pljson/

Hope this helps!

Regards,

Kiran

The new ORDS Auto-PLSQL feature automatically generates JSON for your REFCURSOR output as well.

This is a procedure that gives employees a raise and returns a REFCURSOR of the employees, example and code here.