Skip to Main Content
  • Questions
  • Generating JSON data for data in given table dynamically

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, Prabhakar.

Asked: May 06, 2020 - 5:46 am UTC

Answered by: Chris Saxon - Last updated: May 11, 2020 - 5:14 am UTC

Category: PL/SQL - Version: 11G

Viewed 100+ times

You Asked

My goal is to generate the data in JSON format from one table. The table might have 10 millions records. Another challenge is I am not aware of the number of columns and column names of the table for which I need to generate the JSON.

My DB server is oracle 11G. But the solution need to work on 10G too.

Below is my solution.

1. I generate the JSON string with column name and bind variable by looping through the columns collection of cursor - This is achieved
2. Then loop through the rows and filled the values of bind variables with column data - This is not working for dynamic columns

Below is sample code. I have table named CITY having columns "NAme" and "STATE"

It would be helpful if you guide me on below issue or point to any other solution. Thanks in advance


DECLARE

   -- cursor
  CURSOR c_city IS SELECT  *  FROM archival.cities ;
  
   -- record    
   r_city c_city%ROWTYPE;
   final_text varchar(2000):='';
   final_str varchar(2000):='';
BEGIN
  
  
  final_text:='select ''{name:''||:1||'',state:''||:2||''}'' as  from dual';  --This string i am able to generate it and hence not giving the code for it.
  
  OPEN c_city;

  LOOP
    FETCH  c_city  INTO r_city;
    EXIT WHEN c_city%NOTFOUND;
     execute immediate final_text  into final_str using r_city.name,r_city.state; --Here is the problem. I want r_city.name,r_city.state to be dynamically generated and use it to replace the values. 
        
    DBMS_OUTPUT.PUT_LINE(final_str);

   
  END LOOP;

  CLOSE c_city;
END;



and we said...

Pre-12c the easiest way is:

- Install APEX
- Use APEX_JSON

You can pass this a ref cursor and it'll automatically convert the results to JSON:

declare
  cur sys_refcursor;
begin
  
  open cur for
    select employee_id as "employeeId", 
           first_name || ' ' || last_name as "empName"
    from   hr.employees
    where  department_id = 10;

  apex_json.initialize_clob_output;

  apex_json.open_object;
  apex_json.write ( 'employees', cur );
  apex_json.close_object;

  dbms_output.put_line(apex_json.get_clob_output);
  apex_json.free_output;
  
  open cur for
    select department_name "departmentName",
           cursor (
             select employee_id "employeeId" 
             from   hr.employees e
             where  e.department_id = d.department_id
           ) "employees"
    from   hr.departments d
    where  department_id in ( 10, 20 );

  apex_json.initialize_clob_output;

  apex_json.open_object;
  apex_json.write ( 'departments', cur );
  apex_json.close_object;

  dbms_output.put_line(apex_json.get_clob_output);
  apex_json.free_output;
    
end;
/

{
"employees":[
{
"employeeId":200
,"empName":"Jennifer Whalen"
}
]
}

{
"departments":[
  {"departmentName":"Administration","employees":[{"employeeId":200}]},
  {"departmentName":"Marketing","employees":[{"employeeId":201},{"employeeId":202}]}]
}

and you rated our response

  (3 ratings)

Reviews

Useful tip for json generation

May 09, 2020 - 6:55 am UTC

Reviewer: Prabhakar Bhosale

HI Chris,
Thanks for details and tips for JSON generation. Will definitely try out this one. Thx

Regards
Prabhakar

Steps to install APEX on 12.1

May 09, 2020 - 5:09 pm UTC

Reviewer: A reader

Hi Chris,
Could you please share steps to install APEX on Oracle 12.1 Enterprise edition.
Connor McDonald

Followup  

May 11, 2020 - 5:14 am UTC

1) download it (just search for application express downloads)
2) conn as sysdba to your pluggable
3) @apexins SYSAUX SYSAUX TEMP /i/

And of course, as always, carefully read the installation docs before starting anything

using SQLCL

May 12, 2020 - 4:03 am UTC

Reviewer: Rajeshwaran, Jeyabal

other option to make use of SQL Developer command line (SQLCL) - and make use of "sqlformat" option like this:

SQL> set sqlformat json

SQL> select * from dept;
{"results":[{"columns":[{"name":"DEPTNO","type":"NUMBER"},{"name":"DNAME","type":"VARCHAR2"},{"name":"LOC","type":"VARCHAR2"}],"items":
[
{"deptno":10,"dname":"ACCOUNTING","loc":"NEW YORK"}
,{"deptno":20,"dname":"RESEARCH","loc":"DALLAS"}
,{"deptno":30,"dname":"SALES","loc":"CHICAGO"}
,{"deptno":40,"dname":"OPERATIONS","loc":"BOSTON"}
]}]}

SQL>  select employee_id as "employeeId",
  2             first_name || ' ' || last_name as "empName"
  3      from   hr.employees
  4      where  department_id = 10;
{"results":[{"columns":[{"name":"employeeId","type":"NUMBER"},{"name":"empName","type":"VARCHAR2"}],"items":
[
{"employeeid":200,"empname":"Jennifer Whalen"}
]}]}

SQL> select department_name "departmentName",
  2             cursor (
  3               select employee_id "employeeId"
  4               from   hr.employees e
  5               where  e.department_id = d.department_id
  6             ) "employees"
  7      from   hr.departments d
  8      where  department_id in ( 10, 20 )
  9  /
{"results":[{"columns":[{"name":"departmentName","type":"VARCHAR2"},{"name":"employees","type":"REFCURSOR"}],"items":
[
{"departmentname":"Administration","employees":[
{"employeeid":200}
]}
,{"departmentname":"Marketing","employees":[
{"employeeid":201}
,{"employeeid":202}
]}
]}]}

SQL>

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.