BLOB exports from Oracle (Option 2: dblink)
1. RequirementsRun a db script in Server2 to manipulate a blob column in a table in a db in Server1 and to export the column to a file in Server1.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 Steps2.2.1 In Server1
2.2.2 In Server2
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 "$passwdr"
expect sftp>
send "cd $remotedirr"
expect sftp>
send "lcd $localdirr"
expect sftp>
send "put $filenamer"
expect sftp>
send "exitr"
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
（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |