APEX_JSON - How to build Nested JSON
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 ...
