oracle读写文件--利用utl_file包对磁盘文件的读写操作

摘要:

用户提出一个需求,即ORACLE中的一个表存储了照片信息,字段类型为BLOB,要求能导出成文件形式. 本想写个C#程序来做,后来想起ORACLE有很多包,功能很好很强大,于是网上参考了些文章完成了. 主要是用了ORACLE的两个包:UTL_FILE和DBMS_LOB.

实现过程:

第一步：以管理员用户登陆设置可操作目录

 

-- CREATE DIRECTORY privilege is granted only to SYS and SYSTEM by default.
create   or   replace  directory BLOBDIR  as   ' D:\PIC ' ;
grant   read ,write  on  directory BLOBDIR  to  sharedb;
GRANT   EXECUTE   ON  utl_file  TO  sharedb;
select   *   from  ALL_DIRECTORIES;

 

第二步：普通用户登陆,编写存储过程

 

CREATE OR REPLACE PROCEDURE GET_PIC_BLOB (i_xh VARCHAR2) IS
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob BLOB;
l_blob_len INTEGER;

BEGIN
SELECT PIC INTO L_BLOB FROM TB_ZP WHERE PSNNO = i_xh;
 l_blob_len := DBMS_LOB.GETLENGTH(l_blob);
 l_file := UTL_FILE.FOPEN('BLOBDIR',i_xh || '.jpg','WB',32767);
 WHILE l_pos < l_blob_len LOOP
    DBMS_LOB.READ (l_blob, l_amount, l_pos, l_buffer);
    UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE);
    l_pos := l_pos + l_amount;
 END LOOP;
 UTL_FILE.FCLOSE(l_file);

EXCEPTION
 
 --WHEN NO_DATA_FOUND THEN
  --DBMS_OUTPUT.put_line('no data : ' || i_xh);
 WHEN OTHERS THEN
  IF UTL_FILE.IS_OPEN(l_file) THEN
   UTL_FILE.FCLOSE(l_file);
  RAISE;
  END IF;
END GET_PIC_BLOB;

 

第三步：编写PL/SQL 块,循环执行该存储过程

 

declare  
     cursor  cur_01  is  
         select  xh  from  xs_xsjbk  where  rownum  <=   5000  ;

begin
     for  rec_01  in  cur_01 loop        
        GET_PIC_BLOB(rec_01.xh);
     end  loop;   
end ;

 

测试结果.取了5000条数据,其中有照片信息的为3407条.用时1分12秒,感觉还可以.

总结:

1.由管理员创建可访问目录和授权给普通用户比较重要,一开始没有注意,总是报非法路径错误,搞了较长时间在这上面.

2.存储过程中的NO_DATA_FOUND异常本来是屏显输出无照片的学号信息,但是实际运行时出错,原因是DBMS_OUTPUT.put_line打印条数过多,于是注释掉了.

3.Oracle本身提供了大量使用的包,如UTL_HTTP,DBMS_OUTPUT等,分别封装了不同的功能,进行大量的应用程序开发的可能,从而拓展了Oracle的功能.