从Oracle导出BLOB(方案1:客户端）

1. 需求

在服务器2（数据库客户端）上运行一个数据库脚本来操纵在服务器1（数据库服务器）的数据库中的一个BLOB字段，导出这个字段到服务器2的一个文件中。

2. 解决方案

2.1 方案展示图

2.2 步骤

2.2.1 服务器1上

1. 在服务器1上建立一个含有blob字段的表。

   blob_export(
   id number,
   photo blob)

   
   

2. 向该表插入一些blob数据.

3. 建立一个服务器1的操作系统目录，比如 '/tmp/tmp/photo_export'

4. 利用sshfs把刚建立的目录Mount成服务器2的一个远程目录，比如用如下命令行：

   sshfs  user@server1:/tmp/photo_export /tmp/tmp/photo_export

   注: 在Redhat, 需要用: sudo yum install sshfs

5. 在服务器1建立一个数据库的directory,关联到刚创建的本地操作系统目录。比如'LOCAL_PHOTO_EXPORT_2' 到 '/tmp/tmp/photo_export'

   CREATE OR REPLACE DIRECTORY LOCAL_PHOTO_EXPORT_2 AS '/tmp/tmp/photo_export';
   GRANT all ON DIRECTORY LOCAL_PHOTO_EXPORT_2 TO user;

6. 创建一个存储过程来实现blob导出，代码如下：

   create or replace PACKAGE BODY FILE_TRANSFER AS  
     PROCEDURE REMOTE_BLOB_EXPORT(P_LOCAL_DIRECTORY in varchar2) AS
       l_id number;
       l_photo_len number;
       l_photo blob;
    
       l_file      UTL_FILE.FILE_TYPE;
       l_buffer    RAW(32767);
       l_amount    BINARY_INTEGER := 32767;
       l_pos       NUMBER := 1;
    
       c_photo_ext varchar2(5);
       l_file_name varchar2(30);
       BEGIN
         c_photo_ext :='.png';
    
    
         for rec in
           (
           
           select * from blob_export
           )
         loop
             l_id:=rec.id;
             l_photo:=rec.photo;
             l_photo_len := DBMS_LOB.getlength(l_photo);
    
             l_file_name := to_char(l_id)||c_photo_ext;
    
             --open file
             l_file := UTL_FILE.fopen(P_LOCAL_DIRECTORY,l_file_name,'wb',32767);
    
             --write file
             WHILE l_pos < l_photo_len LOOP
               DBMS_LOB.read(l_photo, l_amount, l_pos, l_buffer);
               UTL_FILE.put_raw(l_file, l_buffer, TRUE);
               l_pos := l_pos + l_amount;
             END LOOP;
    
             -- Close the file.
             UTL_FILE.fclose(l_file);
         end loop;
       END REMOTE_BLOB_EXPORT;
    
    
   END FILE_TRANSFER;

2.2.2 在服务器2

1. 在上一节第4步（mount）前，我们需要创建一个操作系统目录，比如'/tmp/photo_export'

2. 用如下命令登陆sqlplus.请替换相应部分，比如用户名，密码，服务器地址等。

   sqlplus "user/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=server2.xxx.com)(Port=1521))(CONNECT_DATA=(SID=service1)))"
   

   
   

3. 在sqlplus执行blob导出的存储过程。

   execute   file_transfer.remote_blob_export('LOCAL_PHOTO_EXPORT_2');

扩展

判断文件是否存在

<span style="font-size:14px;">function is_file_exist(p_local_directory in varchar2, p_file_name in varchar2)
    return char
    as
    l_exists boolean;
    l_file_len number;
    l_block number;
    begin
      UTL_FILE.FGETATTR(p_local_directory, p_file_name,l_exists,l_file_len,l_block);
      if l_exists=true then
        return 'Y';
      else
        return 'N';
      end if;
    end is_file_exist;</span>

select file_transfer.is_file_exist('LOCAL_PHOTO_EXPORT_2','a.png') from dual;

删除文件

<span style="font-size:14px;">procedure remove_file(p_local_directory in varchar2, p_file_name in varchar2)
   as
   begin
     if is_file_exist(p_local_directory, p_file_name)='Y' then
      utl_file.fremove(p_local_directory,p_file_name);   
     end if;
   end remove_file;</span>

execute   file_transfer.remove_file('LOCAL_PHOTO_EXPORT_2','a.png');

参考

1. How To Use SSHFS to Mount Remote File Systems Over SSHhttps://www.digitalocean.com/community/tutorials/how-to-use-sshfs-to-mount-remote-file-systems-over-ssh
2. Extract files from an Oracle BLOB fieldhttp://stackoverflow.com/questions/6332032/how-can-i-extract-files-from-an-oracle-blob-field
3. UTL_FILEhttps://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm