function get_jsonTG(p_query  in varchar2) return clob is
l_cursor number;
l_status number;
l_desc_tab dbms_sql.desc_tab;
  l_cols number;
  sp varchar2(4000);
lclob clob ;
  l_columnValue varchar2(2000) ;
  nb binary_integer;
  l_separator varchar2(1);
begin
DBMS_LOB.CREATETEMPORARY(lclob,TRUE, DBMS_LOB.SESSION);
nb:=0;
sp:='['||chr(13)||chr(10);
nb:=nvl(dbms_lob.getlength(lclob),0)+1;
dbms_lob.write(lclob,length(sp),nb,sp);
l_cursor := dbms_sql.open_cursor;
dbms_sql.parse( l_cursor, p_query, dbms_sql.native );


dbms_sql.describe_columns( l_cursor, l_cols, l_desc_tab );

    for i in 1 .. l_cols loop
        begin
            dbms_sql.define_column( l_cursor, i,
                                    l_columnValue, 2000 );
           l_cols := i;
      
        end;
    end loop;


    dbms_sql.define_column( l_cursor, 1, l_columnValue,
                            2000 );
  l_status := dbms_sql.execute(l_cursor);
 
    LOOP
        EXIT WHEN ( dbms_sql.fetch_rows(l_cursor) <= 0 );
  l_separator := ',';
         sp:='{';
        FOR i IN 1 .. l_cols LOOP
            dbms_sql.column_value( l_cursor, i, l_columnValue );
        --  s:=s||l_desc_tab(i).col_name||'/'||l_columnValue||';';
        if i=l_cols then
         l_separator := '';
        end if;
        if(l_desc_tab(i).col_name='PARENTID') then
        l_desc_tab(i).col_name:='_parentId';
        end if;
            sp:=sp||'"'||l_desc_tab(i).col_name||'":"'||l_columnValue||'"'||l_separator;
      
        END LOOP;
        sp:=sp||'}'||chr(13)||chr(10);
         nb:=nvl(dbms_lob.getlength(lclob),0)+1;
          dbms_lob.write(lclob,length(sp),nb,sp);
          sp:='';
      
    END LOOP;
    dbms_sql.close_cursor(l_cursor);
   sp:=']';
  nb:=nvl(dbms_lob.getlength(lclob),0)+1;
dbms_lob.write(lclob,length(sp),nb,sp);
return(lclob);

end get_jsonTG;