如何使用FTP进行Oracle导入

发布网友发布时间：2022-04-29 22:20

共2个回答

懂视网时间：2022-04-08 13:57

http://blog.csdn.net/mashengwang/article/details/5982663

CREATE OR REPLACE DIRECTORY my_docs AS ‘/u01/app/oracle/‘;
SET SERVEROUTPUT ON SIZE 1000000
@c:/ftp.pks
@c:/ftp.pkb

-- Retrieve an ASCII file from a remote FTP server.
DECLARE
l_conn UTL_TCP.connection;
BEGIN
l_conn := ftp.login(‘ftp.company.com‘, ‘21‘, ‘ftpuser‘, ‘ftppassword‘);
ftp.ascii(p_conn => l_conn);
ftp.get(p_conn      => l_conn,
          p_from_file => ‘/u01/app/oracle/test.txt‘,
          p_to_dir    => ‘MY_DOCS‘,
          p_to_file   => ‘test_get.txt‘);
ftp.logout(l_conn);
utl_tcp.close_all_connections;
END;
/

-- Send an ASCII file to a remote FTP server.
DECLARE
l_conn UTL_TCP.connection;
BEGIN
l_conn := ftp.login(‘ftp.company.com‘, ‘21‘, ‘ftpuser‘, ‘ftppassword‘);
ftp.ascii(p_conn => l_conn);
ftp.put(p_conn      => l_conn,
          p_from_dir => ‘MY_DOCS‘,
          p_from_file => ‘test_get.txt‘,
          p_to_file   => ‘/u01/app/oracle/test_put.txt‘);
ftp.logout(l_conn);
utl_tcp.close_all_connections;
END;
/

-- Retrieve a binary file from a remote FTP server.
DECLARE
l_conn UTL_TCP.connection;
BEGIN
l_conn := ftp.login(‘ftp.company.com‘, ‘21‘, ‘ftpuser‘, ‘ftppassword‘);
ftp.binary(p_conn => l_conn);
ftp.get(p_conn      => l_conn,
          p_from_file => ‘/u01/app/oracle/product/9.2.0.1.0/sysman/reporting/gif/jobs.gif‘,
          p_to_dir    => ‘MY_DOCS‘,
          p_to_file   => ‘jobs_get.gif‘);
ftp.logout(l_conn);
utl_tcp.close_all_connections;
END;
/

-- Send a binary file to a remote FTP server.
DECLARE
l_conn UTL_TCP.connection;
BEGIN
l_conn := ftp.login(‘ftp.company.com‘, ‘21‘, ‘ftpuser‘, ‘ftppassword‘);
ftp.binary(p_conn => l_conn);
ftp.put(p_conn      => l_conn,
          p_from_dir => ‘MY_DOCS‘,
          p_from_file => ‘jobs_get.gif‘,
          p_to_file   => ‘/u01/app/oracle/jobs_put.gif‘);
ftp.logout(l_conn);
utl_tcp.close_all_connections;
END;
/

-- Get a directory listing from a remote FTP server.
DECLARE
l_conn UTL_TCP.connection;
l_list ftp.t_string_table;
BEGIN
l_conn := ftp.login(‘ftp.company.com‘, ‘21‘, ‘ftpuser‘, ‘ftppassword‘);
ftp.list(p_conn   => l_conn,
           p_dir   => ‘/u01/app/oracle‘,
           p_list => l_list);
ftp.logout(l_conn);
utl_tcp.close_all_connections;

IF l_list.COUNT > 0 THEN
    FOR i IN l_list.first .. l_list.last LOOP
      DBMS_OUTPUT.put_line(i || ‘: ‘ || l_list(i));
    END LOOP;
END IF;
END;
/

-- Rename a file on a remote FTP server.
DECLARE
l_conn UTL_TCP.connection;
BEGIN
l_conn := ftp.login(‘ftp.company.com‘, ‘21‘, ‘ftpuser‘, ‘ftppassword‘);
ftp.rename(p_conn => l_conn,
p_from => ‘/u01/app/oracle/dba/shutdown‘,
p_to => ‘/u01/app/oracle/dba/shutdown.old‘);
ftp.logout(l_conn);
utl_tcp.close_all_connections;
END;
/

-- Delete a file on a remote FTP server.
DECLARE
l_conn UTL_TCP.connection;
BEGIN
l_conn := ftp.login(‘ftp.company.com‘, ‘21‘, ‘ftpuser‘, ‘ftppassword‘);
ftp.delete(p_conn => l_conn,
p_file => ‘/u01/app/oracle/dba/temp.txt‘);
ftp.logout(l_conn);
utl_tcp.close_all_connections;
END;
/

-- Create a directory on a remote FTP server.
DECLARE
l_conn UTL_TCP.connection;
BEGIN
l_conn := ftp.login(‘ftp.company.com‘, ‘21‘, ‘ftpuser‘, ‘ftppassword‘);
ftp.mkdir(p_conn => l_conn,
p_dir => ‘/u01/app/oracle/test‘);
ftp.logout(l_conn);
utl_tcp.close_all_connections;
END;
/

-- Remove a directory from a remote FTP server.
DECLARE
l_conn UTL_TCP.connection;
BEGIN
l_conn := ftp.login(‘ftp.company.com‘, ‘21‘, ‘ftpuser‘, ‘ftppassword‘);
ftp.rmdir(p_conn => l_conn,
p_dir => ‘/u01/app/oracle/test‘);
ftp.logout(l_conn);
utl_tcp.close_all_connections;
END;
/

ORACLE中的FTP例子代码

标签：

热心网友时间：2022-04-08 11:05

如何使用FTP进行Oracle导入
你可以写一个线程来监控你备份出来的文件所在的目录，如果发现了新备份，就调用FTP操作机制，然后上传到远程文件服务器