1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34
| EXEC SYS.DBMS_RANDOM.SEED(0);
create table big_table (c1 NUMBER , c2 NUMBER , c3 VARCHAR2(10), c4 date );
INSERT /*+ APPEND */ INTO big_table
SELECT LEVEL c1,
ROUND(SYS.DBMS_RANDOM.NORMAL) c2,
SYS.DBMS_RANDOM.STRING('x',10) c3,
sysdate+ROUND(SYS.DBMS_RANDOM.NORMAL) c4
FROM DUAL
CONNECT BY LEVEL <= 1e6;
COMMIT;
create or replace function aggr return clob is
ttext varchar2(32767);
ret clob;
begin
ret:='col1;col2
';
for c in (select c1
, c2
, c3
from big_table
where c2=0)
loop
ttext:=trim(to_char(c.c1))||';'||trim(to_char(c.c2))||';'||c.c3||';
';
dbms_lob.writeappend(ret,length(ttext),ttext);
end loop;
return ret;
end;
/ |
Partager