导出oracle文件,oracle导出数据到文件

create directory aaa as 'D:/dir';

grant read,write on directory aaa to aaaaa;

declare

handle utl_file.file_type;

buf varchar2(30000);

n_count number := 0;

i number :=1;

filename varchar2(32);

c varchar2(1);

cursor c_a

is

select

a.account_id, a.account, b.age, b.username

from account_base a, account_ext b

where a.account_id = b.account_id;

v_a c_a%rowtype;

begin

filename := to_char(to_date('2012-01-01','yyyy-mm-dd')) || '_test.txt';

handle :=utl_file.fopen('AAA',filename,'w', 50);

buf := '';

open c_a;

loop

fetch c_a into v_a;

exit when c_a%notfound;

if(mod(i,500) = 499) then

c := '';

else

c := chr(10);

end if;

buf :=buf || to_char(i) || '.'|| v_a.account_id||','||v_a.account|| ',' || v_a.age ||',' ||v_a.username || c;

i:=i +1;

if(mod(i,500) = 0) then

utl_file.put(handle,buf);

utl_file.new_line(handle);

buf :='';

dbms_output.put_line('aaaa');

end if;

end loop;

utl_file.put(handle,buf);

utl_file.new_line(handle);

utl_file.fclose(handle);

end;

/