Oracle 中PLSQL的ftp应用
来源：互联网 发布：网红喵大仙的淘宝店 编辑：程序博客网 时间：2024/04/28 01:55
CREATE OR REPLACE PACKAGE BODY ftp AS-- ---------------------------------------------------------------------------- Name : http://www.oracle-base.com/dba/miscellaneous/ftp.pkb-- Author : Tim Hall-- Description : Basic FTP API. For usage notes see:-- http://www.oracle-base.com/articles/misc/ftp-from-plsql.php-- Requirements : http://www.oracle-base.com/dba/miscellaneous/ftp.pks-- License : Free for personal and commercial use.-- You can amend the code, but leave existing the headers, current-- amendments history and links intact.-- Copyright and disclaimer available here:-- http://www.oracle-base.com/misc/site-info.php#copyright-- Ammedments :-- When Who What-- =========== ======== =================================================-- 14-AUG-2003 Tim Hall Initial Creation-- 10-MAR-2004 Tim Hall Add convert_crlf procedure.-- Incorporate CRLF conversion functionality into-- put_local_ascii_data and put_remote_ascii_data-- functions.-- Make get_passive function visible.-- Added get_direct and put_direct procedures.-- 23-DEC-2004 Tim Hall The get_reply procedure was altered to deal with-- banners starting with 4 white spaces. This fix is-- a small variation on the resolution provided by-- Gary Mason who spotted the bug.-- 10-NOV-2005 Tim Hall Addition of get_reply after doing a transfer to-- pickup the 226 Transfer complete message. This-- allows gets and puts with a single connection.-- Issue spotted by Trevor Woolnough.-- 03-OCT-2006 Tim Hall Add list, rename, delete, mkdir, rmdir procedures.-- 12-JAN-2007 Tim Hall A final call to get_reply was added to the get_remote%-- procedures to allow multiple transfers per connection.-- 15-Jan-2008 Tim Hall login: Include timeout parameter (suggested by Dmitry Bogomolov).-- 21-Jan-2008 Tim Hall put_%: "l_pos < l_clob_len" to "l_pos <= l_clob_len" to prevent-- potential loss of one character for single-byte files or files-- sized 1 byte bigger than a number divisible by the buffer size-- (spotted by Michael Surikov).-- 23-Jan-2008 Tim Hall send_command: Possible solution for ORA-29260 errors included,-- but commented out (suggested by Kevin Phillips).-- 12-Feb-2008 Tim Hall put_local_binary_data and put_direct: Open file with "wb" for-- binary writes (spotted by Dwayne Hoban).-- 03-Mar-2008 Tim Hall list: get_reply call and close of passive connection added-- (suggested by Julian, Bavaria).-- 12-Jun-2008 Tim Hall A final call to get_reply was added to the put_remote%-- procedures, but commented out. If uncommented, it may cause the-- operation to hang, but it has been reported (morgul) to allow-- multiple transfers per connection.-- get_reply: Moved to pakage specification.-- 24-Jun-2008 Tim Hall get_remote% and put_remote%: Exception handler added to close the passive-- connection and reraise the error (suggested by Mark Reichman).-- 22-Apr-2009 Tim Hall get_remote_ascii_data: Remove unnecessary logout (suggested by John Duncan).-- get_reply and list: Handle 400 messages as well as 500 messages (suggested by John Duncan).-- logout: Added a call to UTL_TCP.close_connection, so not necessary to close-- any connections manually (suggested by Victor Munoz).-- get_local_*_data: Check for zero length files to prevent exception (suggested by Daniel)-- nlst: Added to return list of file names only (suggested by Julian and John Duncan)-- 05-Apr-2011 Tim Hall put_remote_ascii_data: Added comment on definition of l_amount. Switch to 10000 if you get-- ORA-06502 from this line. May give you unexpected result due to conversion. Better to use binary.-- 05-Oct-2013 Tim Hall list, nlst: Fixed bug where files beginning with '4' or '5' could cause error.-- 24-May-2014 Tim Hall Added license information.-- --------------------------------------------------------------------------g_reply t_string_table := t_string_table();g_binary BOOLEAN := TRUE;g_debug BOOLEAN := TRUE;g_convert_crlf BOOLEAN := TRUE;PROCEDURE debug (p_text IN VARCHAR2);-- --------------------------------------------------------------------------FUNCTION login (p_host IN VARCHAR2, p_port IN VARCHAR2, p_user IN VARCHAR2, p_pass IN VARCHAR2, p_timeout IN NUMBER := NULL) RETURN UTL_TCP.connection IS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection;BEGIN g_reply.delete; l_conn := UTL_TCP.open_connection(p_host, p_port, tx_timeout => p_timeout); get_reply (l_conn); send_command(l_conn, 'USER ' || p_user); send_command(l_conn, 'PASS ' || p_pass); RETURN l_conn;END;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------FUNCTION get_passive (p_conn IN OUT NOCOPY UTL_TCP.connection) RETURN UTL_TCP.connection IS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection; l_reply VARCHAR2(32767); l_host VARCHAR(100); l_port1 NUMBER(10); l_port2 NUMBER(10);BEGIN send_command(p_conn, 'PASV'); l_reply := g_reply(g_reply.last); l_reply := REPLACE(SUBSTR(l_reply, INSTR(l_reply, '(') + 1, (INSTR(l_reply, ')')) - (INSTR(l_reply, '('))-1), ',', '.'); l_host := SUBSTR(l_reply, 1, INSTR(l_reply, '.', 1, 4)-1); l_port1 := TO_NUMBER(SUBSTR(l_reply, INSTR(l_reply, '.', 1, 4)+1, (INSTR(l_reply, '.', 1, 5)-1) - (INSTR(l_reply, '.', 1, 4)))); l_port2 := TO_NUMBER(SUBSTR(l_reply, INSTR(l_reply, '.', 1, 5)+1)); l_conn := utl_tcp.open_connection(l_host, 256 * l_port1 + l_port2); return l_conn;END;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------PROCEDURE logout(p_conn IN OUT NOCOPY UTL_TCP.connection, p_reply IN BOOLEAN := TRUE) AS-- --------------------------------------------------------------------------BEGIN send_command(p_conn, 'QUIT', p_reply); UTL_TCP.close_connection(p_conn);END;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------PROCEDURE send_command (p_conn IN OUT NOCOPY UTL_TCP.connection, p_command IN VARCHAR2, p_reply IN BOOLEAN := TRUE) IS-- -------------------------------------------------------------------------- l_result PLS_INTEGER;BEGIN l_result := UTL_TCP.write_line(p_conn, p_command); -- If you get ORA-29260 after the PASV call, replace the above line with the following line. -- l_result := UTL_TCP.write_text(p_conn, p_command || utl_tcp.crlf, length(p_command || utl_tcp.crlf)); IF p_reply THEN get_reply(p_conn); END IF;END;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------PROCEDURE get_reply (p_conn IN OUT NOCOPY UTL_TCP.connection) IS-- -------------------------------------------------------------------------- l_reply_code VARCHAR2(3) := NULL;BEGIN LOOP g_reply.extend; g_reply(g_reply.last) := UTL_TCP.get_line(p_conn, TRUE); debug(g_reply(g_reply.last)); IF l_reply_code IS NULL THEN l_reply_code := SUBSTR(g_reply(g_reply.last), 1, 3); END IF; IF SUBSTR(l_reply_code, 1, 1) IN ('4', '5') THEN RAISE_APPLICATION_ERROR(-20000, g_reply(g_reply.last)); ELSIF (SUBSTR(g_reply(g_reply.last), 1, 3) = l_reply_code AND SUBSTR(g_reply(g_reply.last), 4, 1) = ' ') THEN EXIT; END IF; END LOOP;EXCEPTION WHEN UTL_TCP.END_OF_INPUT THEN NULL;END;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------FUNCTION get_local_ascii_data (p_dir IN VARCHAR2, p_file IN VARCHAR2) RETURN CLOB IS-- -------------------------------------------------------------------------- l_bfile BFILE; l_data CLOB;BEGIN DBMS_LOB.createtemporary (lob_loc => l_data, cache => TRUE, dur => DBMS_LOB.call); l_bfile := BFILENAME(p_dir, p_file); DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly); IF DBMS_LOB.getlength(l_bfile) > 0 THEN DBMS_LOB.loadfromfile(l_data, l_bfile, DBMS_LOB.getlength(l_bfile)); END IF; DBMS_LOB.fileclose(l_bfile); RETURN l_data;END;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------FUNCTION get_local_binary_data (p_dir IN VARCHAR2, p_file IN VARCHAR2) RETURN BLOB IS-- -------------------------------------------------------------------------- l_bfile BFILE; l_data BLOB;BEGIN DBMS_LOB.createtemporary (lob_loc => l_data, cache => TRUE, dur => DBMS_LOB.call); l_bfile := BFILENAME(p_dir, p_file); DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly); IF DBMS_LOB.getlength(l_bfile) > 0 THEN DBMS_LOB.loadfromfile(l_data, l_bfile, DBMS_LOB.getlength(l_bfile)); END IF; DBMS_LOB.fileclose(l_bfile); RETURN l_data;END;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------FUNCTION get_remote_ascii_data (p_conn IN OUT NOCOPY UTL_TCP.connection, p_file IN VARCHAR2) RETURN CLOB IS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection; l_amount PLS_INTEGER; l_buffer VARCHAR2(32767); l_data CLOB;BEGIN DBMS_LOB.createtemporary (lob_loc => l_data, cache => TRUE, dur => DBMS_LOB.call); l_conn := get_passive(p_conn); send_command(p_conn, 'RETR ' || p_file, TRUE); --logout(l_conn, FALSE); BEGIN LOOP l_amount := UTL_TCP.read_text (l_conn, l_buffer, 32767); DBMS_LOB.writeappend(l_data, l_amount, l_buffer); END LOOP; EXCEPTION WHEN UTL_TCP.END_OF_INPUT THEN NULL; WHEN OTHERS THEN NULL; END; UTL_TCP.close_connection(l_conn); get_reply(p_conn); RETURN l_data;EXCEPTION WHEN OTHERS THEN UTL_TCP.close_connection(l_conn); RAISE;END;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------FUNCTION get_remote_binary_data (p_conn IN OUT NOCOPY UTL_TCP.connection, p_file IN VARCHAR2) RETURN BLOB IS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection; l_amount PLS_INTEGER; l_buffer RAW(32767); l_data BLOB;BEGIN DBMS_LOB.createtemporary (lob_loc => l_data, cache => TRUE, dur => DBMS_LOB.call); l_conn := get_passive(p_conn); send_command(p_conn, 'RETR ' || p_file, TRUE); BEGIN LOOP l_amount := UTL_TCP.read_raw (l_conn, l_buffer, 32767); DBMS_LOB.writeappend(l_data, l_amount, l_buffer); END LOOP; EXCEPTION WHEN UTL_TCP.END_OF_INPUT THEN NULL; WHEN OTHERS THEN NULL; END; UTL_TCP.close_connection(l_conn); get_reply(p_conn); RETURN l_data;EXCEPTION WHEN OTHERS THEN UTL_TCP.close_connection(l_conn); RAISE;END;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------PROCEDURE put_local_ascii_data (p_data IN CLOB, p_dir IN VARCHAR2, p_file IN VARCHAR2) IS-- -------------------------------------------------------------------------- l_out_file UTL_FILE.file_type; l_buffer VARCHAR2(32767); l_amount BINARY_INTEGER := 32767; l_pos INTEGER := 1; l_clob_len INTEGER;BEGIN l_clob_len := DBMS_LOB.getlength(p_data); l_out_file := UTL_FILE.fopen(p_dir, p_file, 'w', 32767); WHILE l_pos <= l_clob_len LOOP DBMS_LOB.read (p_data, l_amount, l_pos, l_buffer); IF g_convert_crlf THEN l_buffer := REPLACE(l_buffer, CHR(13), NULL); END IF; UTL_FILE.put(l_out_file, l_buffer); UTL_FILE.fflush(l_out_file); l_pos := l_pos + l_amount; END LOOP; UTL_FILE.fclose(l_out_file);EXCEPTION WHEN OTHERS THEN IF UTL_FILE.is_open(l_out_file) THEN UTL_FILE.fclose(l_out_file); END IF; RAISE;END;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------PROCEDURE put_local_binary_data (p_data IN BLOB, p_dir IN VARCHAR2, p_file IN VARCHAR2) IS-- -------------------------------------------------------------------------- l_out_file UTL_FILE.file_type; l_buffer RAW(32767); l_amount BINARY_INTEGER := 32767; l_pos INTEGER := 1; l_blob_len INTEGER;BEGIN l_blob_len := DBMS_LOB.getlength(p_data); l_out_file := UTL_FILE.fopen(p_dir, p_file, 'wb', 32767); WHILE l_pos <= l_blob_len LOOP DBMS_LOB.read (p_data, l_amount, l_pos, l_buffer); UTL_FILE.put_raw(l_out_file, l_buffer, TRUE); UTL_FILE.fflush(l_out_file); l_pos := l_pos + l_amount; END LOOP; UTL_FILE.fclose(l_out_file);EXCEPTION WHEN OTHERS THEN IF UTL_FILE.is_open(l_out_file) THEN UTL_FILE.fclose(l_out_file); END IF; RAISE;END;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------PROCEDURE put_remote_ascii_data (p_conn IN OUT NOCOPY UTL_TCP.connection, p_file IN VARCHAR2, p_data IN CLOB) IS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection; l_result PLS_INTEGER; l_buffer VARCHAR2(32767); l_amount BINARY_INTEGER := 32767; -- Switch to 10000 (or use binary) if you get ORA-06502 from this line. l_pos INTEGER := 1; l_clob_len INTEGER;BEGIN l_conn := get_passive(p_conn); send_command(p_conn, 'STOR ' || p_file, TRUE); l_clob_len := DBMS_LOB.getlength(p_data); WHILE l_pos <= l_clob_len LOOP DBMS_LOB.READ (p_data, l_amount, l_pos, l_buffer); IF g_convert_crlf THEN l_buffer := REPLACE(l_buffer, CHR(13), NULL); END IF; l_result := UTL_TCP.write_text(l_conn, l_buffer, LENGTH(l_buffer)); UTL_TCP.flush(l_conn); l_pos := l_pos + l_amount; END LOOP; UTL_TCP.close_connection(l_conn); -- The following line allows some people to make multiple calls from one connection. -- It causes the operation to hang for me, hence it is commented out by default. -- get_reply(p_conn);EXCEPTION WHEN OTHERS THEN UTL_TCP.close_connection(l_conn); RAISE;END;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------PROCEDURE put_remote_binary_data (p_conn IN OUT NOCOPY UTL_TCP.connection, p_file IN VARCHAR2, p_data IN BLOB) IS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection; l_result PLS_INTEGER; l_buffer RAW(32767); l_amount BINARY_INTEGER := 32767; l_pos INTEGER := 1; l_blob_len INTEGER;BEGIN l_conn := get_passive(p_conn); send_command(p_conn, 'STOR ' || p_file, TRUE); l_blob_len := DBMS_LOB.getlength(p_data); WHILE l_pos <= l_blob_len LOOP DBMS_LOB.READ (p_data, l_amount, l_pos, l_buffer); l_result := UTL_TCP.write_raw(l_conn, l_buffer, l_amount); UTL_TCP.flush(l_conn); l_pos := l_pos + l_amount; END LOOP; UTL_TCP.close_connection(l_conn); -- The following line allows some people to make multiple calls from one connection. -- It causes the operation to hang for me, hence it is commented out by default. -- get_reply(p_conn);EXCEPTION WHEN OTHERS THEN UTL_TCP.close_connection(l_conn); RAISE;END;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------PROCEDURE get (p_conn IN OUT NOCOPY UTL_TCP.connection, p_from_file IN VARCHAR2, p_to_dir IN VARCHAR2, p_to_file IN VARCHAR2) AS-- --------------------------------------------------------------------------BEGIN IF g_binary THEN put_local_binary_data(p_data => get_remote_binary_data (p_conn, p_from_file), p_dir => p_to_dir, p_file => p_to_file); ELSE put_local_ascii_data(p_data => get_remote_ascii_data (p_conn, p_from_file), p_dir => p_to_dir, p_file => p_to_file); END IF;END;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------PROCEDURE put (p_conn IN OUT NOCOPY UTL_TCP.connection, p_from_dir IN VARCHAR2, p_from_file IN VARCHAR2, p_to_file IN VARCHAR2) AS-- --------------------------------------------------------------------------BEGIN IF g_binary THEN put_remote_binary_data(p_conn => p_conn, p_file => p_to_file, p_data => get_local_binary_data(p_from_dir, p_from_file)); ELSE put_remote_ascii_data(p_conn => p_conn, p_file => p_to_file, p_data => get_local_ascii_data(p_from_dir, p_from_file)); END IF; get_reply(p_conn);END;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------PROCEDURE get_direct (p_conn IN OUT NOCOPY UTL_TCP.connection, p_from_file IN VARCHAR2, p_to_dir IN VARCHAR2, p_to_file IN VARCHAR2) IS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection; l_out_file UTL_FILE.file_type; l_amount PLS_INTEGER; l_buffer VARCHAR2(32767); l_raw_buffer RAW(32767);BEGIN l_conn := get_passive(p_conn); send_command(p_conn, 'RETR ' || p_from_file, TRUE); IF g_binary THEN l_out_file := UTL_FILE.fopen(p_to_dir, p_to_file, 'wb', 32767); ELSE l_out_file := UTL_FILE.fopen(p_to_dir, p_to_file, 'w', 32767); END IF; BEGIN LOOP IF g_binary THEN l_amount := UTL_TCP.read_raw (l_conn, l_raw_buffer, 32767); UTL_FILE.put_raw(l_out_file, l_raw_buffer, TRUE); ELSE l_amount := UTL_TCP.read_text (l_conn, l_buffer, 32767); IF g_convert_crlf THEN l_buffer := REPLACE(l_buffer, CHR(13), NULL); END IF; UTL_FILE.put(l_out_file, l_buffer); END IF; UTL_FILE.fflush(l_out_file); END LOOP; EXCEPTION WHEN UTL_TCP.END_OF_INPUT THEN NULL; WHEN OTHERS THEN NULL; END; UTL_FILE.fclose(l_out_file); UTL_TCP.close_connection(l_conn);EXCEPTION WHEN OTHERS THEN IF UTL_FILE.is_open(l_out_file) THEN UTL_FILE.fclose(l_out_file); END IF; RAISE;END;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------PROCEDURE put_direct (p_conn IN OUT NOCOPY UTL_TCP.connection, p_from_dir IN VARCHAR2, p_from_file IN VARCHAR2, p_to_file IN VARCHAR2) IS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection; l_bfile BFILE; l_result PLS_INTEGER; l_amount PLS_INTEGER := 32767; l_raw_buffer RAW(32767); l_len NUMBER; l_pos NUMBER := 1; ex_ascii EXCEPTION;BEGIN IF NOT g_binary THEN RAISE ex_ascii; END IF; l_conn := get_passive(p_conn); send_command(p_conn, 'STOR ' || p_to_file, TRUE); l_bfile := BFILENAME(p_from_dir, p_from_file); DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly); l_len := DBMS_LOB.getlength(l_bfile); WHILE l_pos <= l_len LOOP DBMS_LOB.READ (l_bfile, l_amount, l_pos, l_raw_buffer); debug(l_amount); l_result := UTL_TCP.write_raw(l_conn, l_raw_buffer, l_amount); l_pos := l_pos + l_amount; END LOOP; DBMS_LOB.fileclose(l_bfile); UTL_TCP.close_connection(l_conn);EXCEPTION WHEN ex_ascii THEN RAISE_APPLICATION_ERROR(-20000, 'PUT_DIRECT not available in ASCII mode.'); WHEN OTHERS THEN IF DBMS_LOB.fileisopen(l_bfile) = 1 THEN DBMS_LOB.fileclose(l_bfile); END IF; RAISE;END;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------PROCEDURE help (p_conn IN OUT NOCOPY UTL_TCP.connection) AS-- --------------------------------------------------------------------------BEGIN send_command(p_conn, 'HELP', TRUE);END;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------PROCEDURE ascii (p_conn IN OUT NOCOPY UTL_TCP.connection) AS-- --------------------------------------------------------------------------BEGIN send_command(p_conn, 'TYPE A', TRUE); g_binary := FALSE;END;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------PROCEDURE binary (p_conn IN OUT NOCOPY UTL_TCP.connection) AS-- --------------------------------------------------------------------------BEGIN send_command(p_conn, 'TYPE I', TRUE); g_binary := TRUE;END;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------PROCEDURE list (p_conn IN OUT NOCOPY UTL_TCP.connection, p_dir IN VARCHAR2, p_list OUT t_string_table) AS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection; l_list t_string_table := t_string_table(); l_reply_code VARCHAR2(3) := NULL;BEGIN l_conn := get_passive(p_conn); send_command(p_conn, 'LIST ' || p_dir, TRUE); BEGIN LOOP l_list.extend; l_list(l_list.last) := UTL_TCP.get_line(l_conn, TRUE); debug(l_list(l_list.last)); IF l_reply_code IS NULL THEN l_reply_code := SUBSTR(l_list(l_list.last), 1, 3); END IF; IF (SUBSTR(l_reply_code, 1, 1) IN ('4', '5') AND SUBSTR(l_reply_code, 4, 1) = ' ') THEN RAISE_APPLICATION_ERROR(-20000, l_list(l_list.last)); ELSIF (SUBSTR(g_reply(g_reply.last), 1, 3) = l_reply_code AND SUBSTR(g_reply(g_reply.last), 4, 1) = ' ') THEN EXIT; END IF; END LOOP; EXCEPTION WHEN UTL_TCP.END_OF_INPUT THEN NULL; END; l_list.delete(l_list.last); p_list := l_list; utl_tcp.close_connection(l_conn); get_reply (p_conn);END;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------PROCEDURE nlst (p_conn IN OUT NOCOPY UTL_TCP.connection, p_dir IN VARCHAR2, p_list OUT t_string_table) AS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection; l_list t_string_table := t_string_table(); l_reply_code VARCHAR2(3) := NULL;BEGIN l_conn := get_passive(p_conn); send_command(p_conn, 'NLST ' || p_dir, TRUE); BEGIN LOOP l_list.extend; l_list(l_list.last) := UTL_TCP.get_line(l_conn, TRUE); debug(l_list(l_list.last)); IF l_reply_code IS NULL THEN l_reply_code := SUBSTR(l_list(l_list.last), 1, 3); END IF; IF (SUBSTR(l_reply_code, 1, 1) IN ('4', '5') AND SUBSTR(l_reply_code, 4, 1) = ' ') THEN RAISE_APPLICATION_ERROR(-20000, l_list(l_list.last)); ELSIF (SUBSTR(g_reply(g_reply.last), 1, 3) = l_reply_code AND SUBSTR(g_reply(g_reply.last), 4, 1) = ' ') THEN EXIT; END IF; END LOOP; EXCEPTION WHEN UTL_TCP.END_OF_INPUT THEN NULL; END; l_list.delete(l_list.last); p_list := l_list; utl_tcp.close_connection(l_conn); get_reply (p_conn);END;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------PROCEDURE rename (p_conn IN OUT NOCOPY UTL_TCP.connection, p_from IN VARCHAR2, p_to IN VARCHAR2) AS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection;BEGIN l_conn := get_passive(p_conn); send_command(p_conn, 'RNFR ' || p_from, TRUE); send_command(p_conn, 'RNTO ' || p_to, TRUE); logout(l_conn, FALSE);END rename;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------PROCEDURE delete (p_conn IN OUT NOCOPY UTL_TCP.connection, p_file IN VARCHAR2) AS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection;BEGIN l_conn := get_passive(p_conn); send_command(p_conn, 'DELE ' || p_file, TRUE); logout(l_conn, FALSE);END delete;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------PROCEDURE mkdir (p_conn IN OUT NOCOPY UTL_TCP.connection, p_dir IN VARCHAR2) AS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection;BEGIN l_conn := get_passive(p_conn); send_command(p_conn, 'MKD ' || p_dir, TRUE); logout(l_conn, FALSE);END mkdir;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------PROCEDURE rmdir (p_conn IN OUT NOCOPY UTL_TCP.connection, p_dir IN VARCHAR2) AS-- -------------------------------------------------------------------------- l_conn UTL_TCP.connection;BEGIN l_conn := get_passive(p_conn); send_command(p_conn, 'RMD ' || p_dir, TRUE); logout(l_conn, FALSE);END rmdir;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------PROCEDURE convert_crlf (p_status IN BOOLEAN) AS-- --------------------------------------------------------------------------BEGIN g_convert_crlf := p_status;END;-- ---------------------------------------------------------------------------- --------------------------------------------------------------------------PROCEDURE debug (p_text IN VARCHAR2) IS-- --------------------------------------------------------------------------BEGIN IF g_debug THEN DBMS_OUTPUT.put_line(SUBSTR(p_text, 1, 255)); END IF;END;-- --------------------------------------------------------------------------END ftp;/SHOW ERRORS一个简单的例子测试（参考网络学习）
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;/ 0 0
- Oracle 中PLSQL的ftp应用
- oracle--PLSQL的应用实例
- plsql中循环的应用
- Perl中ftp的应用
- plsql中&字符的应用以及处理
- Oracle 和 PLSQL中支持的数据类型
- oracle中PLSQL语句
- Oracle中plsql游标
- 应用服务器未安装oracle，安装PLSQL的方法
- Oracle中PLSQL中if语句的写法
- Oracle中PLSQL中一个例外的写法
- oracle的PLSQL基础
- oracle+plsql的配置
- oracle的plsql使用中常见的问题整理
- plsql中查看Oracle版本
- plsql中配置oracle客户端
- 在PLSQL工具中如何调试oracle的触发器
- windows中PLSQL/Developer、Oracle InstantClient的安装与配置
- linux内核的生成过程, vmlinux调试分析
- 【140817】类似红警画面的VC游戏源码，一个模块
- ISO给UIImageView增加点击事件
- Dividing+POJ+01背包问题
- 我的太鼓
- Oracle 中PLSQL的ftp应用
- srvbuildres: error while loading shared libraries: libXm.so.3
- IOS单例模式(Singleton)介绍
- 【索引】Maths - Simple Geometry
- 我的太鼓达人2
- « AFNetworking 图片的本地缓存问题 Get application bundle seed ID in iOS » AFNetworking 使用总结 （用法+JSON解析）
- poj 1681 极角排序（只能向左拐的虫子）
- 记录重要的资源网站
- Maltab 坐标轴精度设置
