1. 需求
2. 方案
2.1 展示图
为了传输文件的目的,有一个DBMS_FILE_TRANSFER包,它含有put_file过程用于在远端文件系统创建一份拷贝。但是,它要求被复制的文件的大小必须是512字节的整数倍,我们很难保证每个文件都能达到这个要求。换句话说,当我们利用该过程来传输任意大小的文件时,我们会得到违背该条要求的错误信息。
2.2 步骤
2.2.1 在服务器1上
-
在服务器1上创建一个含有blob字段的表格.
blob_export( id number, photoblob)
-
向该表格插入一些数据.
-
在服务器1上建立一个目录,比如 '/tmp/photo_export'
2.2.2 在服务器2上
-
在服务器2上建立一个指向服务器1的dblink.
-
在服务器2上建立一个material view.
建立material view的原因是我们不能直接使用远程表格的LOB指针(Error ORA-22992). -
在服务器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;
- 在服务器2上创建一个shell脚本用于sftp.
#!/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上创建一个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; } }; /
-
创建一个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;
-
其中, 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; /
-
最后,执行EXPORT_SFTP过程来实现整个功能.
set serveroutput on; call dbms_java.set_output(50); begin file_transfer.export_sftp; end;
参考
- Extract files from an Oracle BLOB field http://stackoverflow.com/questions/6332032/how-can-i-extract-files-from-an-oracle-blob-field
- SFTP from PLSQL https://slobaray.com/2015/09/10/sftp-from-plsql/
- What is SFTP, and how do I use it to transfer files https://kb.iu.edu/d/akqg
- Install Tcl http://www.linuxfromscratch.org/blfs/view/svn/general/tcl.html
- Install Expect http://www.linuxfromscratch.org/blfs/view/svn/general/expect.html