--创建目录对象dir1并授权
create or replace directory dir1 as 'D:\';
grant read,write on directory dir1 to scott;
grant read on directory dir1 to public;

一、写入OS文件

1、单记录写入
create or replace procedure add_emp_to_file(name varchar2,filename varchar2)
is
 handle utl_file.file_type;
 emp_record emp%rowtype;
begin
 select * into emp_record from emp where lower(ename)=lower(name);
 handle:=utl_file.fopen('DIR1',filename,'a');
 utl_file.put_line(handle,emp_record.ename||' '||emp_record.sal||' '||emp_record.job);
 utl_file.fclose(handle);
end;
/
exec add_emp_to_file('scott','01.txt');

2、批量写入
create or replace procedure add_emp_to_file(filename varchar2)
is
 handle utl_file.file_type;
 type emp_table_type is table of scott.emp%rowtype index by binary_integer;
 emp_table emp_table_type;
begin
 select * bulk collect into emp_table from scott.emp;
 handle:=utl_file.fopen('DIR1',filename,'a');
 for i in 1..emp_table.count loop  
  utl_file.put_line(handle,emp_table(i).ename||' '||emp_table(i).sal||' '||emp_table(i).job);
 end loop;
 utl_file.fclose(handle);
end;
/
exec add_emp_to_file('01.txt');

3、批量写入Excel
create or replace procedure add_xls_file(filename varchar2)
is
 handle utl_file.file_type;
 type emp_table_type is table of scott.emp%rowtype index by binary_integer;
 emp_table emp_table_type;
 aa varchar2(10):='ENAME';
 bb varchar2(10):='SAL';
 cc varchar2(10):='JOB';
begin
 select * bulk collect into emp_table from scott.emp;
 handle:=utl_file.fopen('DIR1',filename,'W');
 utl_file.put_line(handle,aa||chr(9)||bb||chr(9)||cc);
 for i in 1..emp_table.count loop  
  utl_file.put_line(handle,emp_table(i).ename||chr(9)||emp_table(i).sal||chr(9)||emp_table(i).job);
 end loop;
 utl_file.fclose(handle);
end;
/
exec add_xls_file('01.xls');

二、读取OS文件

1、文本批量读取
create or replace procedure read_txt_file(files_name varchar2)
is
  files utl_file.file_type;
  files_text varchar2(1000);
  files_row int:=0;
begin
     files:=utl_file.fopen('DIR1',files_name,'r');
     loop
         files_row:=files_row+1;
         utl_file.get_line(files,files_text);
         dbms_output.put_line(files_text);        
     end loop;
     exception
              when no_data_found then return;
     utl_file.fclose(files);
end;
/
exec read_txt_file('emp.txt');

2、嵌套表批量读取
create or replace procedure get_file_content(files_name varchar2)
is
 type files_record_type is table of varchar2(100) index by binary_integer;
 files_record files_record_type;
 files utl_file.file_type;
 files_row int:=1;
begin
 files:=utl_file.fopen('DIR1',files_name,'r');
 loop
  files_row:=files_row+1;
  utl_file.get_line(files,files_record(files_row));
  dbms_output.put_line(files_record(files_row));        
 end loop;
 exception
  when no_data_found then return;
  utl_file.fclose(files);
end;
/
exec get_file_content('emp.txt');

3、文本指定长度批量读取
create or replace procedure read_nchar_file(files_name varchar2)
is
  files utl_file.file_type;
  files_text1 varchar2(1000);
  files_text2 varchar2(1000);
  files_text3 varchar2(1000);
  files_text4 varchar2(1000);
  files_row int:=0;
begin
     files:=utl_file.fopen_nchar('DIR1',files_name,'r');
     loop
         files_row:=files_row+1;
         utl_file.get_line_nchar(files,files_text1,3);
         utl_file.get_line_nchar(files,files_text2,4);
         utl_file.get_line_nchar(files,files_text3,2);
         utl_file.get_line_nchar(files,files_text4,3);
         dbms_output.put_line(files_text1||'组'||files_text2||'年'||files_text3||'月'||files_text4||'日');   
         insert into temp values(files_text1,files_text2,files_text3,files_text4);    
     end loop;
     exception
              when no_data_found then return;
     utl_file.fclose(files);
end;
/
exec read_nchar_file('02.txt');

4、嵌套表指定长度批量读取
create or replace procedure get_files_content(files_name varchar2)
is
 type files_record_type is table of varchar2(100) index by binary_integer;
 files_record1 files_record_type;
 files_record2 files_record_type;
 files_record3 files_record_type;
 files_record4 files_record_type;
 files utl_file.file_type;
 files_row int:=1;
begin
 files:=utl_file.fopen_nchar('DIR1',files_name,'r');
 loop
  files_row:=files_row+1;
  utl_file.get_line_nchar(files,files_record1(files_row),3);
  utl_file.get_line_nchar(files,files_record2(files_row),4);
  utl_file.get_line_nchar(files,files_record3(files_row),2);
  utl_file.get_line_nchar(files,files_record4(files_row),3);
  dbms_output.put_line(files_record1(files_row)||'组'||files_record2(files_row)||'年'||files_record3(files_row)||'月'||files_record4(files_row)||'日');   
  insert into temp values(files_record1(files_row),files_record2(files_row),files_record3(files_row),files_record4(files_row));   
 end loop;
 exception
  when no_data_found then return;
  utl_file.fclose(files);
end;
/
exec get_files_content('02.txt');

########################完整例子###########################

创建目录对象dir1并授权
create or replace directory dir1 as 'D:\';
grant read,write on directory dir1 to scott;

写入OS文件
set serveroutput on
create or replace procedure add_emp_to_file(filename varchar2)
is
 handle utl_file.file_type;
 type ename_table_type is table of char(10) index by binary_integer;
 ename_table ename_table_type;
 type sal_table_type is table of char(7) index by binary_integer;
 sal_table sal_table_type;
 type job_table_type is table of char(9) index by binary_integer;
 job_table job_table_type;
begin
 select ename,sal,job bulk collect into ename_table,sal_table,job_table from scott.emp;
 handle:=utl_file.fopen('DIR1',filename,'a');
 for i in 1..ename_table.count loop  
  utl_file.put_line(handle,ename_table(i)||','||sal_table(i)||','||job_table(i)||',');
 end loop;
 utl_file.fclose(handle);
end;
/
exec add_emp_to_file('01.txt');

读取OS文件
set serveroutput on
create or replace procedure get_files_content(files_name varchar2)
is
 type files_record_type is table of varchar2(50) index by binary_integer;
 files_record1 files_record_type;
 files_record2 files_record_type;
 files_record3 files_record_type;
 files utl_file.file_type;
 files_row int:=1;
begin
 files:=utl_file.fopen_nchar('DIR1',files_name,'r');
 loop
  files_row:=files_row+1;
  utl_file.get_line_nchar(files,files_record1(files_row),11);
  utl_file.get_line_nchar(files,files_record2(files_row),8);
  utl_file.get_line_nchar(files,files_record3(files_row),11);
  dbms_output.put_line(rtrim(files_record1(files_row),' ,')||rtrim(files_record2(files_row),' ,')||rtrim(files_record3(files_row),' ,'));
  insert into temp_emp values(rtrim(files_record1(files_row),' ,'),rtrim(files_record2(files_row),' ,'),rtrim(files_record3(files_row),' ,'));
 end loop;
 exception
  when no_data_found then return;
  utl_file.fclose(files);
end;
/
exec get_files_content('01.txt');