4 Replies Latest reply on Jul 26, 2017 3:00 AM by Jeremy Treague

    Generating JSON from refcursor in R12.2 without APEX_JSON

    Jeremy Treague

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