Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Convert a cursor into JSON?

Mike KutzApr 15 2022

Database: 21c ATP Free Tier
I'm looking for the JSON version of this (LiveSQL PL/SQL code for converting cursor to XML)
So, if I give a cursor like:

open cur for
    select column_name, data_type
    from user_tab_cols
    where table_name = 'EMP';

The function should return JSON (or json_object_t) that matches something like:

{ "ROWSET":[
        { "COLUMN_NAME":"EMPNO", "DATA_TYPE":"NUMBER" },
        { "COLUMN_NAME":"DEPTNO", "DATA_TYPE":"NUMBER" },
        { "COLUMN_NAME":"HIRE_DATE", "DATA_TYPE":"DATE" },
        ...
    ]
}

Thanks,
MK
This post has been answered by Stew Ashton on Apr 16 2022
Jump to Answer

Comments

mathguy

It seems to me that you want something like I show below. I am creating a JSON document from a random table (I chose one with a small number of columns, and I limited the row count to 4 to keep the output to a manageable size). The key name ROWSET is hardcoded, but the rest is automatic. Note the use of the wildcard * to stand for "all columns". This can also be used on a view rather than on a table. How you use it on a "cursor" (assuming you need one) I will let you figure out.
Check the documentation for the specific way in which Oracle converts data types from SQL to JSON.
Tested on Oracle 19 on LiveSQL.oracle.com; I only have 12.2 on my machine (on which this obviously fails).

select json_object(key 'ROWSET' value json_arrayagg(json_object(t.*))) as json_str
from   hr.countries t
where  rownum <= 4
;


JSON_STR
---------------------------------
{
"ROWSET" : [
{
"COUNTRY_ID" : "AR",
"COUNTRY_NAME" : "Argentina",
"REGION_ID" : 2
},
{
"COUNTRY_ID" : "AU",
"COUNTRY_NAME" : "Australia",
"REGION_ID" : 3
},
{
"COUNTRY_ID" : "BE",
"COUNTRY_NAME" : "Belgium",
"REGION_ID" : 1
},
{
"COUNTRY_ID" : "BR",
"COUNTRY_NAME" : "Brazil",
"REGION_ID" : 2
}
]
}
1 person found this helpful
Stew Ashton

I don't understand the question. The LiveSQL example produces output with data, whereas you seem to be asking for output that describes the columns. Which is it?

Stew Ashton
Answer

A similar question was asked here:
Is it possible to use JSON SQL functions on a weak ref cursor rather than a table - Ask TOM (0 Bytes)There were two answers:

  1. APEX_JSON
  2. Use native JSON support in Oracle SQL, which requires changing the REF CURSOR itself, or else replacing the REF CURSOR by a named subquery and using a SQL table macro.
    Another approach would be to roll your own generic function: convert the REF CURSOR to a DBMS_SQL cursor, then generate whatever JSON you want.
Marked as Answer by Mike Kutz · Apr 16 2022
1 person found this helpful
Mike Kutz

It looks like APEX_JSON is the tool I'm looking for.
Thanks to both of you.

Mike Kutz

Oh ... Interesting find:
The APEX_JSON trick works on LONG data types (read Data Dictionary)

1 - 5

Post Details

Added on Apr 15 2022
5 comments
4,420 views