Oracle 9 中PL/SQL实现FTP功能的文件传输 SQL, Oracle, FTP, 文件, 功能

纵横四海

FTP From PL/SQLSometimes it's preferable to trigger FTP jobs directly from PL/SQL rather than rely on CRON or AT. This article contains a brief description of the two methods I use.

The first method relies on a java stored procedure, described in Shell Commands From PL/SQL, which can be used to trigger a shell script to perform the transfer. The shell script may look like the following:

#! /bin/ksh# Move to appropriate directory on local servercd /extracts# FTP all files in directoryftp -inv ftp.company.com <<EOFuser ftpuser ftppassword# Move to appropriate directory on remote server.cd /loadsasciimput *.*byeEOF

The second approach uses a combination of the UTL_TCP and UTL_FILE packages to create a simple FTP API
( http://www.oracle-base.com/dba/miscellaneous/ftp.pks,
http://www.oracle-base.com/dba/miscellaneous/ftp.pkb).
Once the API is loaded into the appropriate schema simple FTP commands can be initiated as follows:

纵横四海

相关代码：

1. 
   
2. CREATE OR REPLACE DIRECTORY my_docs AS '/u01/app/oracle/';
   
3. SET SERVEROUTPUT ON SIZE 1000000@c:\ftp.pks@c:\ftp.pkb
   
4. -- Retrieve an ASCII file from a remote FTP server.
   
5. DECLARE  l_conn  UTL_TCP.connection;
   
6. BEGIN  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');  
   
7. ftp.ascii(p_conn => l_conn);  
   
8. 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');  
   
9. ftp.logout(l_conn);  utl_tcp.close_all_connections;END;
   
10. /
    
11. -- Send an ASCII file to a remote FTP server.
    
12. DECLARE  l_conn  UTL_TCP.connection;
    
13. BEGIN  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');  
    
14. ftp.ascii(p_conn => l_conn);  
    
15. 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');  
    
16. ftp.logout(l_conn);  utl_tcp.close_all_connections;END;
    
17. /
    
18. -- Retrieve a binary file from a remote FTP server.
    
19. DECLARE  l_conn  UTL_TCP.connection;
    
20. BEGIN  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');  
    
21. 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');  
    
22. ftp.logout(l_conn);  utl_tcp.close_all_connections;END;
    
23. /
    
24. -- Send a binary file to a remote FTP server.
    
25. DECLARE  l_conn  UTL_TCP.connection;
    
26. BEGIN  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');  
    
27. 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);  
    
28. utl_tcp.close_all_connections;END;
    
29. /
    
30. -- Get a directory listing from a remote FTP server.
    
31. DECLARE  l_conn  UTL_TCP.connection;  l_list  ftp.t_string_table;
    
32. BEGIN  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');  
    
33. ftp.list(p_conn   => l_conn,           p_dir   => '/u01/app/oracle',           p_list  => l_list);  
    
34. ftp.logout(l_conn);  utl_tcp.close_all_connections;   
    
35. IF l_list.COUNT > 0 THEN    FOR i IN l_list.first .. l_list.last LOOP      
    
36. DBMS_OUTPUT.put_line(i || ': ' || l_list(i));   
    
37. END LOOP;  
    
38. END IF;
    
39. END;
    
40. /
    
41. -- Rename a file on a remote FTP server.
    
42. DECLARE  l_conn  UTL_TCP.connection;
    
43. BEGIN  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');  
    
44. ftp.rename(p_conn => l_conn,             p_from => '/u01/app/oracle/dba/shutdown',             p_to   => '/u01/app/oracle/dba/shutdown.old');  
    
45. ftp.logout(l_conn);  utl_tcp.close_all_connections;
    
46. END;
    
47. 
    
48. /-- Delete a file on a remote FTP server.
    
49. DECLARE  l_conn  UTL_TCP.connection;
    
50. BEGIN  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');  
    
51. ftp.delete(p_conn => l_conn,             p_file => '/u01/app/oracle/dba/temp.txt');  
    
52. ftp.logout(l_conn);  utl_tcp.close_all_connections;END;
    
53. 
    
54. 
    
55. /-- Create a directory on a remote FTP server.
    
56. DECLARE  l_conn  UTL_TCP.connection;BEGIN  
    
57. l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');  
    
58. ftp.mkdir(p_conn => l_conn,            p_dir => '/u01/app/oracle/test');  
    
59. ftp.logout(l_conn);  utl_tcp.close_all_connections;
    
60. END;
    
61. 
    
62. /-- Remove a directory from a remote FTP server.
    
63. DECLARE  l_conn  UTL_TCP.connection;
    
64. BEGIN  l_conn := ftp.login('ftp.company.com', '21', 'ftpuser', 'ftppassword');  ftp.rmdir(p_conn => l_conn,            p_dir  => '/u01/app/oracle/test');  
    
65. f tp.logout(l_conn);  utl_tcp.close_all_connections;
    
66. END;
    
67. /

复制代码

apple_zhang

Thanks for sharing ^_^
Can't understand method 1;
And will have a try about approch 2 when I get the opportunity...
It must be very practical,TKS again ：）

sunweijie22

thank you for your sharing ,

san

学习了，谢谢，好东东。。

qi_shi2005

下了,谢谢楼主

ccxxx

automate things ... thanks for sharing