全面学习DBMS包之UTL_FILE

utl_file 包配合oracle的directory使我们在数据库中更加方便的对文件进行读写操作，下面看三个例子
1、写入外部文件

SQL> conn / as sysdba
已连接。
SQL> show user
USER 为 "SYS"


SQL> create or replace directory test_utl_file as 'f:/dump';

目录已创建。

SQL> grant execute on utl_file to jrgazx;

授权成功。

SQL> grant read,write on directory  test_utl_file to jrgazx;

授权成功。


SQL> 
SQL>  conn jrgazx/jrgazx
SQL>  declare 
  2   file utl_file.file_type;
  3   begin
  4   file :=utl_file.fopen('TEST_UTL_FILE','abc.txt','a');
  5   utl_file.put_line(file,'I am writing to file');
  6   utl_file.fclose(file);
  7   end;
  8  /

PL/SQL 过程已成功完成。

这个例子首先以SYSDBA用户登录创建directory并授权给jrgazx用户，再以jrgazx用户登录执行了一段PL/SQL代码，执行成功后，在F:/DUMP目录下就能看到生成的abc.txt
其内容为
I am writing to file
2、把USER_OBJECTS表输出到外部文件

SQL> CONN JRGAZX/JRGAZX
已连接。


SQL> create or replace procedure pro_test_writeInto_Osfile is
  2    vOutHandle utl_file.file_type;
  3    L_BUFFER   VARCHAR2(400);
  4  
  5  BEGIN
  6    vOutHandle := utl_file.fopen('TEST_UTL_FILE',
  7                                 'USER_OBJECTS' ||
  8                                 TO_CHAR(SYSDATE, 'YYYYMMDD') || '.txt',
  9                                 'W');
 10    L_BUFFER   := 'object_name,object_id,object_type,created,last_ddl_time,timestamp,status';
 11    FOR I IN (SELECT  OBJECT_NAME || ',' || OBJECT_ID || ',' ||
 12                     OBJECT_TYPE || ',' || TO_CHAR(CREATED, 'YYYY-MM-DD hh24:mi:ss') || ',' ||
 13                     TO_CHAR(LAST_DDL_TIME, 'YYYY-MM-DD hh24:mi:ss') || ',' || TIMESTAMP || ',' |
|
 14                     STATUS RESULT
 15                FROM USER_OBJECTS) LOOP
 16      UTL_FILE.PUT_LINE(vOutHandle, I.RESULT);
 17    END LOOP;
 18    utl_file.fclose(vOutHandle);
 19  exception
 20    when others then
 21      dbms_output.put_line('执行失败');
 22      utl_file.fclose(vOutHandle);
 23  END pro_test_writeInto_Osfile;
 24  /

过程已创建。

SQL> 
SQL> EXEC  pro_test_writeInto_Osfile();

PL/SQL 过程已成功完成。

三、从文件读取数据插入到数据库表

连接到: 
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table t_object
  2  as
  3  select * from user_objects where 1=2;

表已创建。

SQL> create or replace procedure pro_test_readfrom_Osfile(f_name varchar2)
  2  is
  3  vinhandle utl_file.file_type;
  4  v_text varchar2(500);
  5  begin
  6  if f_name is null then
  7  return;
  8  end if;
  9  vinhandle :=utl_file.fopen('TEST_UTL_FILE',f_name,'r');
 10  loop
 11  
 12  utl_file.get_line(vinhandle,v_text);
 13  insert into t_object (object_name,object_id,object_type,created,last_ddl_time,timestamp,status)

 14  values
 15  (
 16  substr(v_text,1,instr(v_text,',',1,1)-1), 
 17  substr(v_text,instr(v_text,',',1,1)+1,instr(v_text,',',1,2)-instr(v_text,',',1,1)-1),
 18  substr(v_text,instr(v_text,',',1,2)+1,instr(v_text,',',1,3)-instr(v_text,',',1,2)-1),
 19  to_date(substr(v_text,instr(v_text,',',1,3)+1,instr(v_text,',',1,4)-instr(v_text,',',1,3)-1),'y
yyy-mm-dd hh24:mi:ss'),
 20  to_date(substr(v_text,instr(v_text,',',1,4)+1,instr(v_text,',',1,5)-instr(v_text,',',1,4)-1),'y
yyy-mm-dd hh24:mi:ss'),
 21  substr(v_text,instr(v_text,',',1,5)+1,instr(v_text,',',1,6)-instr(v_text,',',1,5)-1),
 22  substr(v_text,-1,instr(v_text,',',1,1))
 23  );
 24  commit;
 25  end loop;
 26  end;
 27  
 28  
 29  
 30  
 31  
 32  
 33  
 34  
 35  
 36  
 37  
 38  
 39  
 40  /

过程已创建。

SQL> exec pro_test_readfrom_Osfile('USER_OBJECTS20090930.txt');

UTL_FILE包子过程说明：

异常产生及处理：