utl_file

create directory utl_path as '/home/oracle/';
grant read,write on directory utl_path to scott;
grant execute on utl_file to scott;

conn scott/tiger

declare
v_hand_file utl_file.file_type;
cursor cur_dept is select * from dept;
begin
v_hand_file := utl_file.fopen('UTL_PATH','dept'||trunc(sysdate)||'.txt','w',32767);
for v_cur_dept in cur_dept loop
utl_file.put_line(v_hand_file,v_cur_dept.deptno||','||v_cur_dept.dname||','||v_cur_dept.loc);
end loop;
utl_file.fclose(v_hand_file);
end;
/

create table dept_01 as select * from dept where 1=2;

declare
v_hand_file utl_file.file_type;
v_text varchar2(1000 char);
v_first number;
v_second number;
v_deptno dept_01.deptno%type;
v_dname dept_01.dname%type;
v_loc dept_01.loc%type;
begin
v_hand_file := utl_file.fopen('UTL_PATH','dept11-NOV-12.txt','r');
loop
begin
utl_file.get_line(v_hand_file,v_text);
exception
when no_data_found then
exit;
end;
dbms_output.put_line(v_text);
commit;
end loop;
end;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23382569/viewspace-761071/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23382569/viewspace-761071/