从Oracle导出BLOB(方案2:dblink）

1. 需求

在服务器2（数据库服务器）操控服务器1（数据库服务器）上的一个含有blob字段的表格，导出该blob字段到服务器1的一个文件。

2. 方案

2.1 展示图

为了传输文件的目的，有一个DBMS_FILE_TRANSFER包，它含有put_file过程用于在远端文件系统创建一份拷贝。但是，它要求被复制的文件的大小必须是512字节的整数倍，我们很难保证每个文件都能达到这个要求。换句话说，当我们利用该过程来传输任意大小的文件时，我们会得到违背该条要求的错误信息。

2.2 步骤

2.2.1 在服务器1上

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

   blob_export(
   id number,
   photoblob)

   
   

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

3. 在服务器1上建立一个目录，比如 '/tmp/photo_export'

2.2.2 在服务器2上

1. 在服务器2上建立一个指向服务器1的dblink.

2. 在服务器2上建立一个material view.
   建立material view的原因是我们不能直接使用远程表格的LOB指针(Error ORA-22992).

3. 在服务器2上建立一个db directory,比如 'LOCAL_PHOTO_EXPORT' 指向 '/tmp/photo_export'

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

   
   
4. 在服务器2上创建一个shell脚本用于sftp.

5. #!/bin/expect 
   
   set timeout -1
   
   puts $argc
   
   if { $argc<6 } {
     puts "Usage $argv0 host user passwd localdir filename remotedir"
     exit 1
   }
   
   
   set host [lindex $argv 0]
   set user [lindex $argv 1]
   set passwd [lindex $argv 2]
   set localdir [lindex $argv 3]
   set filename [lindex $argv 4]
   set remotedir [lindex $argv 5]
   
   puts $host  
   puts $user
   puts $passwd
   puts $localdir
   puts $filename
   puts $remotedir
   
   spawn /usr/bin/sftp -oStrictHostkeyChecking=no -oCheckHostIP=no $user@$host
   expect *assword:
   
   send "$passwd\r"
   expect sftp>
   
   send "cd $remotedir\r"
   expect sftp>
   
   send "lcd $localdir\r"
   expect sftp>
   
   send "put $filename\r"
   expect sftp>
   
   send "exit\r"
   expect eof
   

   
   
   

6. 在服务器2上创建一个java source用于执行系统命令.

   CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Host" AS
   import java.io.*;
   public class Host {
     public static void executeCommand(String command) {
       try {
         String[] finalCommand;
         if (isWindows()) {
           finalCommand = new String[4];
           finalCommand[0] = "C:\\windows\\system32\\cmd.exe";
           finalCommand[1] = "/y";
           finalCommand[2] = "/c";
           finalCommand[3] = command;
         }
         else {
           finalCommand = new String[3];
           finalCommand[0] = "/bin/sh";
           finalCommand[1] = "-c";
           finalCommand[2] = command;
         }
         System.out.println(command);
          
         final Process pr = Runtime.getRuntime().exec(finalCommand);
         new Thread(new Runnable() {
           public void run() {
             try {
               BufferedReader br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
               String buff = null;
               while ((buff = br_in.readLine()) != null) {
                 System.out.println("Process out :" + buff);
                 try {Thread.sleep(100); } catch(Exception e) {}
               }
               br_in.close();
             }
             catch (IOException ioe) {
               System.out.println("Exception caught printing process output.");
               ioe.printStackTrace();
             }
           }
         }).start();
      
         new Thread(new Runnable() {
           public void run() {
             try {
               BufferedReader br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
               String buff = null;
               while ((buff = br_err.readLine()) != null) {
                 System.out.println("Process err :" + buff);
                 try {Thread.sleep(100); } catch(Exception e) {}
               }
               br_err.close();
             }
             catch (IOException ioe) {
               System.out.println("Exception caught printing process error.");
               ioe.printStackTrace();
             }
           }
         }).start();
       }
       catch (Exception ex) {
         System.out.println(ex.getLocalizedMessage());
       }
     }
      
     public static boolean isWindows() {
       if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1)
         return true;
       else
         return false;
     }
      
   };
   /

7. 创建一个package用于包含实现需求的存储过程。

   create or replace PACKAGE FILE_TRANSFER AS
    
       
      PROCEDURE REMOTE_BLOB_EXPOERT (P_LOCAL_DIRECTORY in varchar2);
       
      PROCEDURE SFTP(P_HOST in varchar2,P_USER in varchar2,P_PASSWD in varchar2,P_REMOTE_DIR in varchar2, P_LOCAL_DIR in varchar2, P_FILE_NAME in varchar2);
       
      PROCEDURE EXPORT_SFTP;
       
      PROCEDURE host_command (p_command  IN  VARCHAR2);
   END FILE_TRANSFER;

8. 其中, host_command过程定义如下:

   PROCEDURE host_command (p_command  IN  VARCHAR2)
       AS LANGUAGE JAVA
       NAME 'Host.executeCommand (java.lang.String)';

   它实际是调用刚建立的java source来执行系统命令。REMOTE_BLOB_EXPOERT 用于导出blob到文件，SFTP用于从服务器2传输文件到服务器1，EXPORT_SFTP是执行所有功能的入口。

     CREATE OR REPLACE PACKAGE BODY "CDU_IDM"."FILE_TRANSFER" AS
     /*
     purpose:export a remote blob column to a local directory
     before running this:
     1. a db link established;
     2. a material view is established;
     3. a local directory is established;
     */
     PROCEDURE REMOTE_BLOB_EXPOERT(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';
         
         --mv_blob_export is the material view getting a blob column via a db link 
         for rec in 
           (select *
           from mv_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_EXPOERT;
       
       PROCEDURE host_command (p_command  IN  VARCHAR2)
       AS LANGUAGE JAVA
       NAME 'Host.executeCommand (java.lang.String)';
   
       /*
       purpose: transfer a file by sftp
       before running this:
       1. a sftp shell script is established 
       */
       PROCEDURE SFTP(P_HOST in varchar2,P_USER in varchar2,P_PASSWD in varchar2,P_REMOTE_DIR in varchar2, P_LOCAL_DIR in varchar2, P_FILE_NAME in varchar2) AS
         l_sftp_prog       VARCHAR2(100) := '/tmp/photo_export/sftp.sh';
         l_sftp_command    VARCHAR2(500); 
       BEGIN
          l_sftp_command := l_sftp_prog || ' "' || p_host || '" "' || p_user || '" "' ||
                             p_passwd || '" "'|| p_local_dir || '" "' ||
                             p_file_name||'" "'|| p_remote_dir||'"' ;
    
         dbms_output.put_line(l_sftp_command);
    
         host_command(l_sftp_command);
    
         
       END SFTP;
       
       /*
       a combine procedure for usage
       */
       PROCEDURE EXPORT_SFTP AS
         l_LOCAL_DIRECTORY varchar2(30) :='LOCAL_PHOTO_EXPORT';
         l_host        VARCHAR2(100) := 'server1.xxx.com';
         l_user        VARCHAR2(100) := 'sftpuser';
         l_passwd      VARCHAR2(100) := 'password';
         l_remote_dir  VARCHAR(500) := '/tmp/photo_export';  
         l_local_dir   VARCHAR2(500) := '/tmp/photo_export';  
         
         c_photo_ext varchar2(5):='.png';
         
         l_file_name  varchar2(100):='*'||c_photo_ext;
       BEGIN
         REMOTE_BLOB_EXPOERT(l_LOCAL_DIRECTORY);
         sftp(l_host,l_user,l_passwd,l_remote_dir,l_local_dir,l_file_name);
       END EXPORT_SFTP;
     
   
   END FILE_TRANSFER;
   
   /
   

   
   

9. 最后，执行EXPORT_SFTP过程来实现整个功能.

   set serveroutput on;
   call dbms_java.set_output(50);
    
   begin
   file_transfer.export_sftp;
    
   end;

参考

1. Extract files from an Oracle BLOB fieldhttp://stackoverflow.com/questions/6332032/how-can-i-extract-files-from-an-oracle-blob-field
2. SFTP from PLSQL https://slobaray.com/2015/09/10/sftp-from-plsql/
3. What is SFTP, and how do I use it to transfer fileshttps://kb.iu.edu/d/akqg
4. Install Tclhttp://www.linuxfromscratch.org/blfs/view/svn/general/tcl.html
5. Install Expecthttp://www.linuxfromscratch.org/blfs/view/svn/general/expect.html