1. Requirements

2. Solution

2.1 The illustration of the process

By using the process in the following diagram,we can run a script in Server2 to export a blob field to local directory in Server1.

For transferring files,there is aDBMS_FILE_TRANSFER package containing put_file procedure "tocreate a copy of the file in the remote file system". However,since it requires "The size of the copied file must be a multiple of 512 bytes.",which is not realistic in reality. In other words,we may get an error showing the violation of the rule if we utilise the package/procedure to transfer a file with an arbitrarysize.

2.2 Steps

2.2.1 In Server1

1. Create a table in Server1.

   blob_export( id number, photoblob)

2. Insert some test data in the table.

3. Create a target directory in Server1,say '/tmp/photo_export'

2.2.2 In Server2

1. Create adblinkin Server2 to Server1.

2. Create a material view in Server2.
   The reason to create such a material view is because wecannot use LOB locators selected from remote tables (Error ORA-22992 if we select from the remote table).

3. Create a directory in Server2,say 'LOCAL_PHOTO_EXPORT' at '/tmp/photo_export'

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

4. Create a shell script in Server2 for sftppurpose. Please see the details in sftp.sh.

5. Create a java source in Server2 to executeoperatingsystem command.

   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) @H_646_403@ return true; else return false; } }; /

6. Create a package containing procedures to implement the requirement.

   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;

   Among the procedures,host_command is defined as the following:

   PROCEDURE host_command (p_command IN VARCHAR2)

   AS LANGUAGE JAVA

   NAME 'Host.executeCommand (java.lang.String)';

   which is to use the java source to execute anoperating system command.

   Regarding the package body,please see details in file_transfer-body.sql.
   In addition,among the procedures,REMOTE_BLOB_EXPOERT works for exporting the blob to a file in Server,SFTP works for transferring files from Server2 to Server1,and EXPORT_SFTP is the major entry to carry out the whole functionality.

7. Finally,run the procedure EXPORT_SFTP to carry out the whole functionality.

   set serveroutput on;

   call dbms_java.set_output(50);

   begin

   file_transfer.export_sftp;

   end;

Appendix

1. sftp.sh

#!/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

2.file_transfer-body.sql

</pre><pre name="code" class="sql">create or replace PACKAGE BODY 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_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';
      
      --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_EXPORT;
    
    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_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) := '';
      l_user        VARCHAR2(100) := '';
      l_passwd      VARCHAR2(100) := '';
      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_EXPORT(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;

References

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