(转)FTP From PL/SQL
来源:互联网 发布:数据库概念设计实例 编辑:程序博客网 时间:2024/05/17 07:59
(转载地址:http://www.oracle-base.com/articles/misc/ftp-from-plsql.php)
FTP From PL/SQL
Sometimes 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.
- Shell Script
- PL/SQL FTP API
- ACL for 11g
Shell Script
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
PL/SQL FTP API
The second approach uses a combination of the UTL_TCP and UTL_FILE packages to create a simple FTP API (ftp.pks,ftp.pkb). Once the API is loaded into the appropriate schema simple FTP commands can be initiated as follows.
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);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);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);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);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); 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;/-- Get a directory listing (file names only) 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.nlst(p_conn => l_conn, p_dir => '/u01/app/oracle', p_list => l_list); ftp.logout(l_conn); 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);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);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);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);END;/
The basic functions are implemented using LOBs to allow FTP without having to access files on the local filesystem. Theget
and put
procedures string these together to form a complete job using all the functions. If a straight forward FTP to, or from, the local filesystem is required it is more efficient to use theGET_DIRECT
and PUT_DIRECT
procedures as they avoid the temporary LOBs.
The current implementation has the following issues:
- The mput and mget operations are not supported directly, but can be implemented using a combination of the list/nlst and get/put operations.
- The implementation of binary transfers relies on
UTL_FILE
features only available in Oracle9i Release 2 upwards. - There is no support for ASCII mode in the
PUT_DIRECT
procedure.
Thanks to Hans van Doormalen for noticing I wasn't closing my passive connections. I do now :)
ACL for 11g
The introduction of Fine-Grained Access to Network Services in Oracle Database 11g Release 1 means you will need to configure an access control list (ACL) to allow UTL_TCP to access the network. The examples above work correctly with the following basic ACL. You will need to amend the FTP server details and username details to match your FTP server address and the Oracle username running the FTP API.
DECLARE l_acl_name VARCHAR2(30) := 'utl_tcp.xml'; l_ftp_server_ip VARCHAR2(20) := '192.168.0.131'; l_ftp_server_name VARCHAR2(20) := 'ftp.company.com'; l_username VARCHAR2(30) := 'TEST';BEGIN DBMS_NETWORK_ACL_ADMIN.create_acl ( acl => l_acl_name, description => 'Allow connections using UTL_TCP', principal => l_username, is_grant => TRUE, privilege => 'connect', start_date => SYSTIMESTAMP, end_date => NULL); COMMIT; DBMS_NETWORK_ACL_ADMIN.add_privilege ( acl => l_acl_name, principal => l_username, is_grant => FALSE, privilege => 'connect', position => NULL, start_date => NULL, end_date => NULL); COMMIT; DBMS_NETWORK_ACL_ADMIN.assign_acl ( acl => l_acl_name, host => l_ftp_server_ip, lower_port => NULL, upper_port => NULL); DBMS_NETWORK_ACL_ADMIN.assign_acl ( acl => l_acl_name, host => l_ftp_server_name, lower_port => NULL, upper_port => NULL); COMMIT;END;/
For more information see:
- Shell Commands From PL/SQL
- UTL_FILE Enhancements
- UTL_TCP
- UTL_FILE
- Fine-Grained Access to Network Services in Oracle Database 11g Release 1
Hope this helps. Regards Tim...
Back to the Top.
- (转)FTP From PL/SQL
- Send Emails from PL/SQL
- Send Emails from PL/SQL .
- Invoking Webservice from PL/SQL (UTL_DBWS&UTL_HTTP)
- Invoking Webservice from PL/SQL (UTL_DBWS&UTL_HTTP)
- Invoking Webservice from PL/SQL (UTL_DBWS&UTL_HTTP)
- Performing SQL Operations from PL/SQL
- Beginning PL/SQL: From Novice to Professional
- pl/sql developer快速输入select * from
- pl/sql developer快速输入select * from
- pl/sql developer快速输入select * from
- ORA-06502 assigning values from SQL to PL/SQL variables
- PL/SQL 自定义快捷键(比如输入s,直接就显示select * from)
- Oracle 9 中PL/SQL实现FTP功能的文件传输
- PL/SQL教程(转)
- 包(pl/sql)
- pl/sql(1)
- pl/sql(2)
- 可编辑和拖动的文本框
- 搜索二叉树转为有序双向链表
- 杭电 ACM 1007
- 【转】C语言声明((void ()( ) )0)( )
- C Runtime Library 的来历
- (转)FTP From PL/SQL
- Emacs常用快捷键
- 最好的教程竟是帮助文档
- Spring 注解@Component,@Service,@Controller,@Repository
- 《coredump问题原理探究》windows版6.1节无成员变量的类
- cocosd-x基础知识
- 基于串口调试助手软件的CDMA和GPRS模块打电话、发信息使用说明
- OS + Linux RHEL / RedHat Enterprise 5 / 6 / 6.3
- epoll