create or replace type agg_json as object ( t_varchar2 varchar2(32767) , t_clob clob , static function odciaggregateinitialize( sctx in out agg_json ) return number , member function odciaggregateiterate ( self in out agg_json , a_val dbmsoutput_linesarray ) return number , member function odciaggregateterminate ( self in out agg_json , returnvalue out clob , flags in number ) return number , member function odciaggregatemerge ( self in out agg_json , ctx2 in out agg_json ) return number , static function json_obj( p_obj dbmsoutput_linesarray ) return varchar2 , static function escape_json( p_val varchar2 ) return varchar2 ) / create or replace type body agg_json is static function odciaggregateinitialize( sctx in out agg_json ) return number is begin sctx := agg_json( null, null ); return odciconst.success; end; -- member function odciaggregateiterate ( self in out agg_json , a_val dbmsoutput_linesarray ) return number is procedure add_val( p_val varchar2 ) is begin if nvl( lengthb( self.t_varchar2 ), 0 ) + lengthb( p_val ) <= 4000 -- Strange limit, the max size of self.t_varchar2 is 29993 -- If you exceeds this number you get ORA-22813: operand value exceeds system limits -- with 29993 you get JSON-output as large 58894 bytes -- with 4000 you get JSON-output as large 1063896 bytes, probably max more then self.t_varchar2 := self.t_varchar2 || p_val; else if self.t_clob is null then dbms_lob.createtemporary( self.t_clob, true, dbms_lob.call ); end if; dbms_lob.writeappend( self.t_clob, length( self.t_varchar2 ), self.t_varchar2 ); self.t_varchar2 := p_val; end if; end; begin add_val( chr(10) || ',' ); add_val( '{' ); if a_val is not null and a_val.count > 1 and mod( a_val.count, 3 ) = 0 then for i in 1 .. a_val.count / 3 loop add_val( case when i > 1 then ',' end || '"' || agg_json.escape_json( a_val( 3 * i - 2 ) ) || '":' ); if upper( a_val( 3 * i ) ) != 'S' then add_val( a_val( 3 * i - 1 ) ); else add_val( '"' || agg_json.escape_json( a_val( 3 * i - 1 ) ) || '"' ); end if; end loop; end if; add_val( '}' ); return odciconst.success; end; -- member function odciaggregateterminate ( self in out agg_json , returnvalue out clob , flags in number ) return number is begin if self.t_clob is null then dbms_lob.createtemporary( self.t_clob, true, dbms_lob.call ); end if; if self.t_varchar2 is not null then dbms_lob.writeappend( self.t_clob, length( self.t_varchar2 ), self.t_varchar2 ); end if; if dbms_lob.substr( self.t_clob, 1, 1 ) is null then dbms_lob.writeappend( self.t_clob, 1, '[' ); else dbms_lob.write( self.t_clob, 2, 1, ' [' ); end if; dbms_lob.writeappend( self.t_clob, 1, ']' ); returnvalue := self.t_clob; return odciconst.success; end; -- member function odciaggregatemerge ( self in out agg_json , ctx2 in out agg_json ) return number is begin if self.t_clob is null then dbms_lob.createtemporary( self.t_clob, true, dbms_lob.call ); end if; if self.t_varchar2 is not null then dbms_lob.writeappend( self.t_clob, length( self.t_varchar2 ), self.t_varchar2 ); end if; if ctx2.t_clob is not null then dbms_lob.append( self.t_clob, ctx2.t_clob ); dbms_lob.freetemporary( ctx2.t_clob ); end if; if ctx2.t_varchar2 is not null then dbms_lob.writeappend( self.t_clob, length( ctx2.t_varchar2 ), ctx2.t_varchar2 ); ctx2.t_varchar2 := null; end if; return odciconst.success; end; -- static function json_obj( p_obj dbmsoutput_linesarray ) return varchar2 is t_json varchar2(32767); begin t_json := '{'; if p_obj is not null and p_obj.count > 0 and mod( p_obj.count, 3 ) = 0 then for i in 1 .. p_obj.count / 3 loop t_json := t_json || case when i > 1 then ',' end || '"' || escape_json( p_obj( 3 * i - 2 ) ) || '":'; if upper( p_obj( 3 * i ) ) != 'S' then t_json := t_json || p_obj( 3 * i - 1 ); else t_json := t_json || '"' || escape_json( p_obj( 3 * i - 1 ) ) || '"'; end if; end loop; end if; t_json := t_json || '}'; return t_json; end; -- static function escape_json( p_val varchar2 ) return varchar2 is begin return replace( replace( replace( replace( replace( replace( replace( replace( replace( asciistr( p_val ) , '\', '\u' ) , '"', '\"' ) , '\u005C', '\\' ) , '/', '\/' ) , chr(8), '\b' ) , chr(12), '\f' ) , chr(10), '\n' ) , chr(13), '\r' ) , chr(9), '\t' ); end; end; / create or replace function json_agg( agg dbmsoutput_linesarray ) return clob parallel_enable aggregate using agg_json; / select json_agg( dbmsoutput_linesarray( 'id', level, 'n' , 'name', level, '' , 'test', 'test' || level, '' ) ) from dual connect by level <= 3 / select agg_json.json_obj ( dbmsoutput_linesarray( 'company' , json_agg( dbmsoutput_linesarray( 'name', d.dname, '' , 'identifier', d.deptno, '' , 'location', d.loc, '' , 'employees', json_agg( dbmsoutput_linesarray( 'name', e.ename, '' , 'job', e.job, '' , 'salary', e.sal, 'n' , 'manager', nvl( ( select agg_json.json_obj( dbmsoutput_linesarray( 'name', m.ename, '' , 'salary', m.sal, 'n' , 'hiredate', to_char( m.hiredate, 'DD-MM-YYYY' ), '' ) ) from emp m where m.empno = e.mgr ), '{}' ), 'o' , 'hiredate', to_char( e.hiredate, 'DD-MM-YYYY' ), '' ) ), 'a' ) ) , 'a' ) ) from dept d , emp e where d.deptno = e.deptno group by d.dname, d.deptno, d.loc /