EXECUTE IMMEDIATE l_sql BULK COLLECT INTO l_recs;
   FOR i IN l_recs.FIRST..l_recs.LAST LOOP
      l_content_text := l_content_text||chr(10)||l_recs(i);           
   END LOOP;

EXECUTE IMMEDIATE l_sql BULK COLLECT INTO l_recs;

   FOR i IN l_recs.FIRST..l_recs.LAST LOOP
      l_content_text := l_content_text||chr(10)||l_recs(i);   
        
   END LOOP;

declare
    c       CLOB;
    l_c     CLOB;
    cnt     PLS_INTEGER:=1;
    cnt1    PLS_INTEGER:=1000;
    l       PLS_INTEGER;
   
begin 
  
   l := dbms_utility.get_time();
   For i in 1..500 loop
       
       SELECT string_agg(ename) into c
       FROM (
       select rownum recno, ename
       from teststr   
       )
       WHERE recno between cnt and cnt1;
       cnt :=cnt1+1;
       cnt1:= cnt1+1000;
       l_c := l_c||chr(10)||c;          
     
   END LOOP;
  l := dbms_utility.get_time()-l;  
  dbms_output.put_line(l);
end;

Declare
 
l_clob        CLOB;
l_time        PLS_INTEGER;
l_cnt PLS_INTEGER:=0;

begin
 
   l_time := dbms_utility.get_time();
   dbms_output.put_line('Start Time: '||to_char(sysdate,'dd mm yyyy hh24:mi:ss')); 
   -- Initialize
   DBMS_LOB.CREATETEMPORARY(l_clob,true);
    -- Extract the data
   For rec in (select ename from teststr)
   loop
       -- Transform the data
       l_cnt:= l_cnt+1;
       dbms_lob.append(l_clob, to_clob(l_cnt)||rec.ename||chr(10));
   END LOOP;
 
   -- Load/Store the data
   INSERT INTO cl
   values (l_clob);
 
  dbms_lob.freetemporary(l_clob);
  COMMIT;
 
  l_time := dbms_utility.get_time()-l_time;
  dbms_output.put_line(l_time);
  dbms_output.put_line('End Time: '||to_char(sysdate,'dd mm yyyy hh24:mi:ss'));
end;
/