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

最新推荐文章于 2024-10-29 15:07:10 发布

iteye_16407

最新推荐文章于 2024-10-29 15:07:10 发布

阅读量482

点赞数

文章标签： java shell runtime

1. 需求

在服务器2（数据库服务器）操控服务器1（数据库服务器）上的一个含有blob字段的表格，导出该blob字段到服务器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 fieldhttp://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 fileshttps://kb.iu.edu/d/akqg
Install Tclhttp://www.linuxfromscratch.org/blfs/view/svn/general/tcl.html
Install Expecthttp://www.linuxfromscratch.org/blfs/view/svn/general/expect.html