I have been came across requirement for printing nested JSON in one of the application. Using APEX_JSON, there is no straight forward way – I can just print a cursor or ref-cursor to an output as nested JSON. So, I have to write a pl/sql block and manually build JSON output.
Purpose of this blog is to share a PL/SQL block which can build a nested JSON. Following is one sample PL/SQL block to print employee-manager-employee hierarchy.
DECLARE
l_return clob;
l_curr_level number := 1;
l_counter number;
l_empno emp.empno%type;
l_ename emp.ename%type;
l_job emp.job%type;
l_sal emp.sal%type;
l_level number;
l_is_open boolean := false;
l_emp_r emp%rowtype;
l_cursor sys_refcursor;
BEGIN
IF :P17_ROOT IS NOT NULL THEN
APEX_JSON.initialize_clob_output;
select *
into l_emp_r
from emp
where empno = :P17_ROOT;
APEX_JSON.open_object;
APEX_JSON.write(’empno’, l_emp_r.empno, true);
APEX_JSON.write(‘name’, l_emp_r.ename, true);
APEX_JSON.write(‘job’, l_emp_r.job, true);
APEX_JSON.write(‘sal’, l_emp_r.sal, true);
APEX_JSON.open_array(’employees’);
open l_cursor for
select level, empno, ename, job, sal
from emp
start with mgr = :P17_ROOT
connect by nocycle prior empno = mgr
order siblings by empno;
loop
fetch l_cursor
into l_level, l_empno, l_ename, l_job, l_sal;
EXIT WHEN l_cursor%notfound;
l_counter := l_level – l_curr_level;
if l_counter = 0 then
if l_is_open then
–dbms_output.put_line(‘}’);
APEX_JSON.close_object;
end if;
–dbms_output.put_line(‘{‘);
APEX_JSON.open_object;
l_is_open := true;
elsif l_counter = 1 then
–dbms_output.put_line(‘[‘);
APEX_JSON.open_array(’employees’);
–dbms_output.put_line(‘{‘);
APEX_JSON.open_object;
l_is_open := true;
elsif l_counter < 0 then
for j in 1..(l_counter*-1) loop
–dbms_output.put_line(‘}’);
APEX_JSON.close_object;
–dbms_output.put_line(‘]’);
APEX_JSON.close_array;
end loop;
–dbms_output.put_line(‘}’);
APEX_JSON.close_object;
–dbms_output.put_line(‘{‘);
APEX_JSON.open_object;
l_is_open := true;
end if;
APEX_JSON.write(’empno’, l_empno, true);
APEX_JSON.write(‘name’, l_ename, true);
APEX_JSON.write(‘job’, l_job, true);
APEX_JSON.write(‘sal’, l_sal, true);
l_curr_level := l_level;
end loop;
if l_curr_level > 1 then
for i in 2..l_curr_level loop
–dbms_output.put_line(‘}’);
APEX_JSON.close_object;
–dbms_output.put_line(‘]’);
APEX_JSON.close_array;
end loop;
end if;
if l_is_open then
–dbms_output.put_line(‘}’);
APEX_JSON.close_object;
end if;
APEX_JSON.close_array;
APEX_JSON.close_object;
l_return := APEX_JSON.get_clob_output;
APEX_JSON.free_output;
HTP.P(l_return);
END IF;
END;
Output
{
“empno”: 7839,
“name”: “KING”,
“job”: “PRESIDENT”,
“sal”: 5000,
“employees”: [{
“empno”: 7566,
“name”: “JONES”,
“job”: “MANAGER”,
“sal”: 2975,
“employees”: [{
“empno”: 7788,
“name”: “SCOTT”,
“job”: “ANALYST”,
“sal”: 3000,
“employees”: [{
“empno”: 7876,
“name”: “ADAMS”,
“job”: “CLERK”,
“sal”: 1100
}]
}, {
“empno”: 7902,
“name”: “FORD”,
“job”: “ANALYST”,
“sal”: 3000,
“employees”: [{
“empno”: 7369,
“name”: “SMITH”,
“job”: “CLERK”,
“sal”: 800
}]
}]
}, {
“empno”: 7698,
“name”: “BLAKE”,
“job”: “MANAGER”,
“sal”: 2850,
“employees”: [{
“empno”: 7499,
“name”: “ALLEN”,
“job”: “SALESMAN”,
“sal”: 1600
}, {
“empno”: 7521,
“name”: “WARD”,
“job”: “SALESMAN”,
“sal”: 1250
}, {
“empno”: 7654,
“name”: “MARTIN”,
“job”: “SALESMAN”,
“sal”: 1250
}, {
“empno”: 7844,
“name”: “TURNER”,
“job”: “SALESMAN”,
“sal”: 1500
}, {
“empno”: 7900,
“name”: “JAMES”,
“job”: “CLERK”,
“sal”: 950
}]
}, {
“empno”: 7782,
“name”: “CLARK”,
“job”: “MANAGER”,
“sal”: 2450,
“employees”: [{
“empno”: 7934,
“name”: “MILLER”,
“job”: “CLERK”,
“sal”: 1300
}]
}]
}
With this blog, I would also like to know if newer version of Oracle has extended the APEX_JSON or any other built-in feature using which I can build nested JSON in a simple way.
Hope this helps!
Regards,
Jaydip Bosamiya
jbosamiya@gmail.com
Leave a Reply