rem ----------------------------------------------------------------------- rem Filename: ftpclient.sql rem Purpose: PL/SQL FTP Client rem Date: 19-Nov-2003 rem Author: Russ Johnson, Braun Consulting rem ----------------------------------------------------------------------- CREATE OR REPLACE PACKAGE BRNC_FTP_PKG AS /** * * PL/SQL FTP Client * * Created by: Russ Johnson, Braun Consulting * * www.braunconsult.com * * OVERVIEW * -------------------- * This package uses the standard packages UTL_FILE and UTL_TCP to perform * client-side FTP functionality (PUT and GET) for text files as defined in * the World Wide Web Consortium's RFC 959 document - http://www.w3.org/Protocols/rfc959/ * The procedures and functions in this package allow single or multiple file transfer using * standard TCP/IP connections. * * LIMITATIONS * -------------------- * Currently the API is limited to transfer of ASCII text files only. This is * primarily because UTL_FILE only supports text I/O, but also because the original * design was for creating text files from data in the Oracle database, then transferring the file to a remote host. * Furthermore, the API does not support SSH/Secure FTP or connection through a proxy server. * Keep in mind that FTP passes the username/password combo in plain text over TCP/IP. * * DB versions - 8i (8.1.x) and above. 8.0.x may work if it has the SYS.UTL_TCP package. * * * Note: Since UTL_FILE is used for the client-side I/O, this package is also limited to * transfer of files that exist in directories available to UTL_FILE for read/write. * These directories are defined by the UTL_FILE_DIR parameter in the init.ora file. * * USAGE * -------------------- * Three functions are available for FTP - PUT, GET, and FTP_MULTIPLE. FTP_MULTIPLE takes * a table of records that define the files to be transferred (filename, directory, etc.). * That table can have 1 record or multiple records. The PUT and GET functions are included * for convenience to FTP one file at a time. PUT and GET return true if the file is transferred * successfully and false if it fails. FTP_MULTIPLE returns true if no batch-level errors occur * (such as an invalid host, refused connection, or invalid login information). It also takes the * table of file records IN and passes it back OUT. Each record contains individual error information. * * EXAMPLE * -------------------- * Transfer multiple files - 1 GET and 2 PUT from a Windows machine to a host (assuming UNIX here). * Display any errors that occur. * DECLARE * * v_username VARCHAR2(40) := 'rjohnson'; * v_password VARCHAR2(40) := 'password'; * v_hostname VARCHAR2(255) := 'ftp.oracle.com'; * v_error_message VARCHAR2(1000); * b_put BOOLEAN; * t_files BRNC_FTP_PKG.t_ftp_rec; -- Declare our table of file records * * BEGIN * * t_files(1).localpath := 'd:\oracle\utl_file\outbound'; * t_files(1).filename := 'myfile1.txt'; * t_files(1).remotepath := '/home/oracle/text_files'; * t_files(1).transfer_mode := 'PUT'; * * t_files(2).localpath := 'd:\oracle\utl_file\inbound'; * t_files(2).filename := 'incoming_file.xml'; * t_files(2).remotepath := '/home/oracle/xml_files'; * t_files(2).transfer_mode := 'GET'; * * t_files(3).localpath := 'd:\oracle\utl_file\outbound'; * t_files(3).filename := 'myfile2.txt'; * t_files(3).remotepath := '/home'; * t_files(3).transfer_mode := 'PUT'; * * b_put := BRNC_FTP_PKG.FTP_MULTIPLE(v_error_message, * t_files, * v_username, * v_password, * v_hostname); * IF b_put = TRUE * THEN * FOR i IN t_files.FIRST..t_files.LAST * LOOP * IF t_files.EXISTS(i) * THEN * DBMS_OUTPUT.PUT_LINE(t_files(i).status||' | '|| * t_files(i).error_message||' | '|| * to_char(t_files(i).bytes_transmitted)||' | '|| * to_char(t_files(i).trans_start,'YYYY-MM-DD HH:MI:SS')||' | '|| * to_char(t_files(i).trans_end,'YYYY-MM-DD HH:MI:SS')); * END IF; * END LOOP; * ELSE * DBMS_OUTPUT.PUT_LINE(v_error_message); * END IF; * * EXCEPTION * WHEN OTHERS * THEN * DBMS_OUTPUT.PUT_LINE(SQLERRM); * END; * * CREDITS * -------------------- * The W3C's RFC 959 that describes the FTP process. * * http://www.w3c.org * * Much of the PL/SQL code in this package was based on Java code written by * Bruce Blackshaw of Enterprise Distributed Technologies Ltd. None of that code * was copied, but the objects and methods greatly helped my understanding of the * FTP Client process. * * http://www.enterprisedt.com * * VERSION HISTORY * -------------------- * 1.0 11/19/2002 Unit-tested single and multiple transfers between disparate hosts. * * */ /** * Exceptions * */ ctrl_exception EXCEPTION; data_exception EXCEPTION; /** * Constants - FTP valid response codes * */ CONNECT_CODE CONSTANT PLS_INTEGER := 220; USER_CODE CONSTANT PLS_INTEGER := 331; LOGIN_CODE CONSTANT PLS_INTEGER := 230; PWD_CODE CONSTANT PLS_INTEGER := 257; PASV_CODE CONSTANT PLS_INTEGER := 227; CWD_CODE CONSTANT PLS_INTEGER := 250; TSFR_START_CODE1 CONSTANT PLS_INTEGER := 125; TSFR_START_CODE2 CONSTANT PLS_INTEGER := 150; TSFR_END_CODE CONSTANT PLS_INTEGER := 226; QUIT_CODE CONSTANT PLS_INTEGER := 221; SYST_CODE CONSTANT PLS_INTEGER := 215; TYPE_CODE CONSTANT PLS_INTEGER := 200; /** * FTP File record datatype * * Elements: * localpath - full directory name in which the local file resides or will reside * Windows: 'd:\oracle\utl_file' * UNIX: '/home/oracle/utl_file' * filename - filename and extension for the file to be received or sent * changing the filename for the PUT or GET is currently not allowed * Examples: 'myfile.dat' 'myfile20021119.xml' * remotepath - full directory name in which the local file will be sent or the * remote file exists. Should be in UNIX format regardless of FTP server - '/one/two/three' * filetype - reserved for future use, ignored in code * transfer_mode - 'PUT' or 'GET' * status - status of the transfer. 'ERROR' or 'SUCCESS' * error_message - meaningful (hopefully) error message explaining the reason for failure * bytes_transmitted - how many bytes were sent/received * trans_start - date/time the transmission started * trans_end - date/time the transmission ended * */ TYPE r_ftp_rec IS RECORD(localpath VARCHAR2(255), filename VARCHAR2(255), remotepath VARCHAR2(255), filetype VARCHAR2(20), transfer_mode VARCHAR2(5), status VARCHAR2(40), error_message VARCHAR2(255), bytes_transmitted NUMBER, trans_start DATE, trans_end DATE); /** * FTP File Table - used to store many files for transfer * */ TYPE t_ftp_rec IS TABLE of r_ftp_rec INDEX BY BINARY_INTEGER; /** * Internal convenience procedure for creating passive host IP address * and port number. * */ PROCEDURE CREATE_PASV(p_pasv_cmd IN VARCHAR2, p_pasv_host OUT VARCHAR2, p_pasv_port OUT NUMBER); /** * Function used to validate FTP server responses based on the * code passed in p_code. Reads single or multi-line responses. * */ FUNCTION VALIDATE_REPLY(p_ctrl_con IN OUT UTL_TCP.CONNECTION, p_code IN PLS_INTEGER, p_reply OUT VARCHAR2) RETURN BOOLEAN; /** * Function used to validate FTP server responses based on the * code passed in p_code. Reads single or multi-line responses. * Overloaded because some responses can have 2 valid codes. * */ FUNCTION VALIDATE_REPLY(p_ctrl_con IN OUT UTL_TCP.CONNECTION, p_code1 IN PLS_INTEGER, p_code2 IN PLS_INTEGER, p_reply OUT VARCHAR2) RETURN BOOLEAN; /** * Procedure that handles the actual data transfer. Meant * for internal package use. Returns information about the * actual transfer. * */ PROCEDURE TRANSFER_ASCII(u_ctrl_con IN OUT UTL_TCP.CONNECTION, p_localpath IN VARCHAR2, p_filename IN VARCHAR2, p_pasv_host IN VARCHAR2, p_pasv_port IN PLS_INTEGER, p_transfer_mode IN VARCHAR2, v_status OUT VARCHAR2, v_error_message OUT VARCHAR2, n_bytes_transmitted OUT NUMBER, d_trans_start OUT DATE, d_trans_end OUT DATE); /** * Function to handle FTP of many files. * Returns TRUE if no batch-level errors occur. * Returns FALSE if a batch-level error occurs. * * Parameters: * * p_error_msg - error message for batch level errors * p_files - BRNC_FTP_PKG.t_ftp_rec table type. Accepts * list of files to be transferred (may be any combination of PUT or GET) * returns the table updated with transfer status, error message, * bytes_transmitted, transmission start date/time and transmission end * date/time * p_username - username for FTP server * p_password - password for FTP server * p_hostname - hostname or IP address of server Ex: 'ftp.oracle.com' or '127.0.0.1' * p_port - port number to connect on. FTP is usually on 21, but this may be overridden * if the server is configured differently. * */ FUNCTION FTP_MULTIPLE(p_error_msg OUT VARCHAR2, p_files IN OUT t_ftp_rec, p_username IN VARCHAR2, p_password IN VARCHAR2, p_hostname IN VARCHAR2, p_port IN PLS_INTEGER DEFAULT 21) RETURN BOOLEAN; /** * Convenience function for single-file PUT * * Parameters: * p_localpath - full directory name in which the local file resides or will reside * Windows: 'd:\oracle\utl_file' * UNIX: '/home/oracle/utl_file' * p_filename - filename and extension for the file to be received or sent * changing the filename for the PUT or GET is currently not allowed * Examples: 'myfile.dat' 'myfile20021119.xml' * p_remotepath - full directory name in which the local file will be sent or the * remote file exists. Should be in UNIX format regardless of FTP server - '/one/two/three' * p_username - username for FTP server * p_password - password for FTP server * p_hostname - FTP server IP address or host name Ex: 'ftp.oracle.com' or '127.0.0.1' * v_status - status of the transfer. 'ERROR' or 'SUCCESS' * v_error_message - meaningful (hopefully) error message explaining the reason for failure * n_bytes_transmitted - how many bytes were sent/received * d_trans_start - date/time the transmission started * d_trans_end - date/time the transmission ended * p_port - port number to connect to, default is 21 * p_filetype - always set to 'ASCII', reserved for future use, ignored in code * */ FUNCTION PUT(p_localpath IN VARCHAR2, p_filename IN VARCHAR2, p_remotepath IN VARCHAR2, p_username IN VARCHAR2, p_password IN VARCHAR2, p_hostname IN VARCHAR2, v_status OUT VARCHAR2, v_error_message OUT VARCHAR2, n_bytes_transmitted OUT NUMBER, d_trans_start OUT DATE, d_trans_end OUT DATE, p_port IN PLS_INTEGER DEFAULT 21, p_filetype IN VARCHAR2 := 'ASCII') RETURN BOOLEAN; /** * Convenience function for single-file GET * * Parameters: * p_localpath - full directory name in which the local file resides or will reside * Windows: 'd:\oracle\utl_file' * UNIX: '/home/oracle/utl_file' * p_filename - filename and extension for the file to be received or sent * changing the filename for the PUT or GET is currently not allowed * Examples: 'myfile.dat' 'myfile20021119.xml' * p_remotepath - full directory name in which the local file will be sent or the * remote file exists. Should be in UNIX format regardless of FTP server - '/one/two/three' * p_username - username for FTP server * p_password - password for FTP server * p_hostname - FTP server IP address or host name Ex: 'ftp.oracle.com' or '127.0.0.1' * v_status - status of the transfer. 'ERROR' or 'SUCCESS' * v_error_message - meaningful (hopefully) error message explaining the reason for failure * n_bytes_transmitted - how many bytes were sent/received * d_trans_start - date/time the transmission started * d_trans_end - date/time the transmission ended * p_port - port number to connect to, default is 21 * p_filetype - always set to 'ASCII', reserved for future use, ignored in code * */ FUNCTION GET(p_localpath IN VARCHAR2, p_filename IN VARCHAR2, p_remotepath IN VARCHAR2, p_username IN VARCHAR2, p_password IN VARCHAR2, p_hostname IN VARCHAR2, v_status OUT VARCHAR2, v_error_message OUT VARCHAR2, n_bytes_transmitted OUT NUMBER, d_trans_start OUT DATE, d_trans_end OUT DATE, p_port IN PLS_INTEGER DEFAULT 21, p_filetype IN VARCHAR2 := 'ASCII') RETURN BOOLEAN; END BRNC_FTP_PKG; / CREATE OR REPLACE PACKAGE BODY BRNC_FTP_PKG AS /***************************************************************************** ** Create the passive host IP and port number to connect to ** *****************************************************************************/ PROCEDURE CREATE_PASV(p_pasv_cmd IN VARCHAR2, p_pasv_host OUT VARCHAR2, p_pasv_port OUT NUMBER) IS v_pasv_cmd VARCHAR2(30) := p_pasv_cmd; --Host and port to connect to for data transfer n_port_dec NUMBER; n_port_add NUMBER; BEGIN p_pasv_host := REPLACE(SUBSTR(v_pasv_cmd,1,INSTR(v_pasv_cmd,',',1,4)-1),',','.'); n_port_dec := TO_NUMBER(SUBSTR(v_pasv_cmd,INSTR(v_pasv_cmd,',',1,4)+1,(INSTR(v_pasv_cmd,',',1,5)-(INSTR(v_pasv_cmd,',',1,4)+1)))); n_port_add := TO_NUMBER(SUBSTR(v_pasv_cmd,INSTR(v_pasv_cmd,',',1,5)+1,LENGTH(v_pasv_cmd)-INSTR(v_pasv_cmd,',',1,5))); p_pasv_port := (n_port_dec*256) + n_port_add; EXCEPTION WHEN OTHERS THEN --DBMS_OUTPUT.PUT_LINE(SQLERRM); RAISE; END CREATE_PASV; /***************************************************************************** ** Read a single or multi-line reply from the FTP server and validate ** it against the code passed in p_code. ** ** Return TRUE if reply code matches p_code, FALSE if it doesn't or error ** occurs ** ** Send full server response back to calling procedure *****************************************************************************/ FUNCTION VALIDATE_REPLY(p_ctrl_con IN OUT UTL_TCP.CONNECTION, p_code IN PLS_INTEGER, p_reply OUT VARCHAR2) RETURN BOOLEAN IS n_code VARCHAR2(3) := p_code; n_byte_count PLS_INTEGER; v_msg VARCHAR2(255); n_line_count PLS_INTEGER := 0; BEGIN LOOP v_msg := UTL_TCP.GET_LINE(p_ctrl_con); n_line_count := n_line_count + 1; IF n_line_count = 1 THEN p_reply := v_msg; ELSE p_reply := p_reply || SUBSTR(v_msg,4); END IF; EXIT WHEN INSTR(v_msg,'-',1,1) <> 4; END LOOP; IF to_number(SUBSTR(p_reply,1,3)) = n_code THEN RETURN TRUE; ELSE RETURN FALSE; END IF; EXCEPTION WHEN OTHERS THEN p_reply := SQLERRM; RETURN FALSE; END VALIDATE_REPLY; /***************************************************************************** ** Reads a single or multi-line reply from the FTP server ** ** Return TRUE if reply code matches p_code1 or p_code2, ** FALSE if it doesn't or error occurs ** ** Send full server response back to calling procedure *****************************************************************************/ FUNCTION VALIDATE_REPLY(p_ctrl_con IN OUT UTL_TCP.CONNECTION, p_code1 IN PLS_INTEGER, p_code2 IN PLS_INTEGER, p_reply OUT VARCHAR2) RETURN BOOLEAN IS v_code1 VARCHAR2(3) := to_char(p_code1); v_code2 VARCHAR2(3) := to_char(p_code2); v_msg VARCHAR2(255); n_line_count PLS_INTEGER := 0; BEGIN LOOP v_msg := UTL_TCP.GET_LINE(p_ctrl_con); n_line_count := n_line_count + 1; IF n_line_count = 1 THEN p_reply := v_msg; ELSE p_reply := p_reply || SUBSTR(v_msg,4); END IF; EXIT WHEN INSTR(v_msg,'-',1,1) <> 4; END LOOP; IF to_number(SUBSTR(p_reply,1,3)) IN(v_code1,v_code2) THEN RETURN TRUE; ELSE RETURN FALSE; END IF; EXCEPTION WHEN OTHERS THEN p_reply := SQLERRM; RETURN FALSE; END VALIDATE_REPLY; /***************************************************************************** ** Handles actual data transfer. Responds with status, error message, and ** transfer statistics. ** ** Potential errors could be with connection or file i/o ** *****************************************************************************/ PROCEDURE TRANSFER_ASCII(u_ctrl_con IN OUT UTL_TCP.CONNECTION, p_localpath IN VARCHAR2, p_filename IN VARCHAR2, p_pasv_host IN VARCHAR2, p_pasv_port IN PLS_INTEGER, p_transfer_mode IN VARCHAR2, v_status OUT VARCHAR2, v_error_message OUT VARCHAR2, n_bytes_transmitted OUT NUMBER, d_trans_start OUT DATE, d_trans_end OUT DATE) IS u_data_con UTL_TCP.CONNECTION; u_filehandle UTL_FILE.FILE_TYPE; v_tsfr_mode VARCHAR2(3) := p_transfer_mode; v_mode VARCHAR2(1); v_tsfr_cmd VARCHAR2(10); v_buffer VARCHAR2(32767); v_localpath VARCHAR2(255) := p_localpath; v_filename VARCHAR2(255) := p_filename; v_host VARCHAR2(20) := p_pasv_host; n_port PLS_INTEGER := p_pasv_port; n_bytes NUMBER; v_msg VARCHAR2(255); v_reply VARCHAR2(1000); v_err_status VARCHAR2(20) := 'ERROR'; BEGIN /** Initialize some of our OUT variables **/ v_status := 'SUCCESS'; v_error_message := ' '; n_bytes_transmitted := 0; IF UPPER(v_tsfr_mode) = 'PUT' THEN v_mode := 'r'; v_tsfr_cmd := 'STOR '; ELSIF UPPER(v_tsfr_mode) = 'GET' THEN v_mode := 'w'; v_tsfr_cmd := 'RETR '; END IF; /** Open data connection on Passive host and port **/ u_data_con := UTL_TCP.OPEN_CONNECTION(v_host,n_port); /** Open the local file to read and transfer data **/ u_filehandle := UTL_FILE.FOPEN(v_localpath,v_filename,v_mode); /** Send the STOR command to tell the server we're going to upload a file **/ n_bytes := UTL_TCP.WRITE_LINE(u_ctrl_con,v_tsfr_cmd||v_filename); IF VALIDATE_REPLY(u_ctrl_con,TSFR_START_CODE1,TSFR_START_CODE2,v_reply) = FALSE THEN RAISE ctrl_exception; END IF; d_trans_start := SYSDATE; IF UPPER(v_tsfr_mode) = 'PUT' THEN LOOP BEGIN UTL_FILE.GET_LINE(u_filehandle,v_buffer); EXCEPTION WHEN NO_DATA_FOUND THEN EXIT; END; n_bytes := UTL_TCP.WRITE_LINE(u_data_con,v_buffer); n_bytes_transmitted := n_bytes_transmitted + n_bytes; END LOOP; ELSIF UPPER(v_tsfr_mode) = 'GET' THEN LOOP BEGIN v_buffer := UTL_TCP.GET_LINE(u_data_con,TRUE); /** Sometimes the TCP/IP buffer sends null data **/ /** we only want to receive the actual data **/ IF v_buffer IS NOT NULL THEN UTL_FILE.PUT_LINE(u_filehandle,v_buffer); n_bytes := LENGTH(v_buffer); n_bytes_transmitted := n_bytes_transmitted + n_bytes; END IF; EXCEPTION WHEN UTL_TCP.END_OF_INPUT THEN EXIT; END; END LOOP; END IF; /** Flush the buffer on the data connection **/ --UTL_TCP.FLUSH(u_data_con); d_trans_end := SYSDATE; /** Close the file **/ UTL_FILE.FCLOSE(u_filehandle); /** Close the Data Connection **/ UTL_TCP.CLOSE_CONNECTION(u_data_con); /** Verify the transfer succeeded **/ IF VALIDATE_REPLY(u_ctrl_con,TSFR_END_CODE,v_reply) = FALSE THEN RAISE ctrl_exception; END IF; EXCEPTION WHEN ctrl_exception THEN v_status := v_err_status; v_error_message := v_reply; IF UTL_FILE.IS_OPEN(u_filehandle) THEN UTL_FILE.FCLOSE(u_filehandle); END IF; UTL_TCP.CLOSE_CONNECTION(u_data_con); WHEN UTL_FILE.invalid_path THEN v_status := v_err_status; v_error_message := 'Directory '||v_localpath||' is not available to UTL_FILE. Check the init.ora file for valid UTL_FILE directories.'; UTL_TCP.CLOSE_CONNECTION(u_data_con); WHEN UTL_FILE.invalid_operation THEN v_status := v_err_status; IF UPPER(v_tsfr_mode) = 'PUT' THEN v_error_message := 'The file '||V_filename||' in the directory '||v_localpath||' could not be opened for reading.'; ELSIF UPPER(v_tsfr_mode) = 'GET' THEN v_error_message := 'The file '||V_filename||' in the directory '||v_localpath||' could not be opened for writing.'; END IF; IF UTL_FILE.IS_OPEN(u_filehandle) THEN UTL_FILE.FCLOSE(u_filehandle); END IF; UTL_TCP.CLOSE_CONNECTION(u_data_con); WHEN UTL_FILE.read_error THEN v_status := v_err_status; v_error_message := 'The system encountered an error while trying to read '||v_filename||' in the directory '||v_localpath; IF UTL_FILE.IS_OPEN(u_filehandle) THEN UTL_FILE.FCLOSE(u_filehandle); END IF; UTL_TCP.CLOSE_CONNECTION(u_data_con); WHEN UTL_FILE.write_error THEN v_status := v_err_status; v_error_message := 'The system encountered an error while trying to write to '||v_filename||' in the directory '||v_localpath; IF UTL_FILE.IS_OPEN(u_filehandle) THEN UTL_FILE.FCLOSE(u_filehandle); END IF; UTL_TCP.CLOSE_CONNECTION(u_data_con); WHEN UTL_FILE.internal_error THEN v_status := v_err_status; v_error_message := 'The UTL_FILE package encountered an unexpected internal system error.'; IF UTL_FILE.IS_OPEN(u_filehandle) THEN UTL_FILE.FCLOSE(u_filehandle); END IF; UTL_TCP.CLOSE_CONNECTION(u_data_con); WHEN OTHERS THEN v_status := v_err_status; v_error_message := SQLERRM; IF UTL_FILE.IS_OPEN(u_filehandle) THEN UTL_FILE.FCLOSE(u_filehandle); END IF; UTL_TCP.CLOSE_CONNECTION(u_data_con); END TRANSFER_ASCII; /***************************************************************************** ** Handles connection to host and FTP of multiple files ** Files can be any combination of PUT and GET ** *****************************************************************************/ FUNCTION FTP_MULTIPLE(p_error_msg OUT VARCHAR2, p_files IN OUT t_ftp_rec, p_username IN VARCHAR2, p_password IN VARCHAR2, p_hostname IN VARCHAR2, p_port IN PLS_INTEGER DEFAULT 21) RETURN BOOLEAN IS v_username VARCHAR2(30) := p_username; v_password VARCHAR2(30) := p_password; v_hostname VARCHAR2(30) := p_hostname; n_port PLS_INTEGER := p_port; u_ctrl_con UTL_TCP.CONNECTION; n_byte_count PLS_INTEGER; n_first_index NUMBER; v_msg VARCHAR2(250); v_reply VARCHAR2(1000); v_pasv_host VARCHAR2(20); n_pasv_port NUMBER; invalid_transfer EXCEPTION; BEGIN p_error_msg := 'FTP Successful'; --Assume the overall transfer will succeed /** Attempt to connect to the host machine **/ u_ctrl_con := UTL_TCP.OPEN_CONNECTION(v_hostname,n_port); IF VALIDATE_REPLY(u_ctrl_con,CONNECT_CODE,v_reply) = FALSE THEN RAISE ctrl_exception; END IF; /** Send username **/ n_byte_count := UTL_TCP.WRITE_LINE(u_ctrl_con,'USER '||v_username); IF VALIDATE_REPLY(u_ctrl_con,USER_CODE,v_reply) = FALSE THEN RAISE ctrl_exception; END IF; /** Send password **/ n_byte_count := UTL_TCP.WRITE_LINE(u_ctrl_con,'PASS '||v_password); IF VALIDATE_REPLY(u_ctrl_con,LOGIN_CODE,v_reply) = FALSE THEN RAISE ctrl_exception; END IF; /** We should be logged in, time to transfer all files **/ FOR i IN p_files.FIRST..p_files.LAST LOOP IF p_files.EXISTS(i) THEN BEGIN /** Change to the remotepath directory **/ n_byte_count := UTL_TCP.WRITE_LINE(u_ctrl_con,'CWD '||p_files(i).remotepath); IF VALIDATE_REPLY(u_ctrl_con,CWD_CODE,v_reply) = FALSE THEN RAISE ctrl_exception; END IF; /** Switch to IMAGE mode **/ n_byte_count := UTL_TCP.WRITE_LINE(u_ctrl_con,'TYPE I'); IF VALIDATE_REPLY(u_ctrl_con,TYPE_CODE,v_reply) = FALSE THEN RAISE ctrl_exception; END IF; /** Get a Passive connection to use for data transfer **/ n_byte_count := UTL_TCP.WRITE_LINE(u_ctrl_con,'PASV'); IF VALIDATE_REPLY(u_ctrl_con,PASV_CODE,v_reply) = FALSE THEN RAISE ctrl_exception; END IF; CREATE_PASV(SUBSTR(v_reply,INSTR(v_reply,'(',1,1)+1,INSTR(v_reply,')',1,1)-INSTR(v_reply,'(',1,1)-1),v_pasv_host,n_pasv_port); /** Transfer Data **/ IF UPPER(p_files(i).transfer_mode) = 'PUT' THEN TRANSFER_ASCII(u_ctrl_con, p_files(i).localpath, p_files(i).filename, v_pasv_host, n_pasv_port, p_files(i).transfer_mode, p_files(i).status, p_files(i).error_message, p_files(i).bytes_transmitted, p_files(i).trans_start, p_files(i).trans_end); ELSIF UPPER(p_files(i).transfer_mode) = 'GET' THEN TRANSFER_ASCII(u_ctrl_con, p_files(i).localpath, p_files(i).filename, v_pasv_host, n_pasv_port, p_files(i).transfer_mode, p_files(i).status, p_files(i).error_message, p_files(i).bytes_transmitted, p_files(i).trans_start, p_files(i).trans_end); ELSE RAISE invalid_transfer; -- Raise an exception here END IF; EXCEPTION WHEN ctrl_exception THEN p_files(i).status := 'ERROR'; p_files(i).error_message := v_reply; WHEN invalid_transfer THEN p_files(i).status := 'ERROR'; p_files(i).error_message := 'Invalid transfer method. Use PUT or GET.'; END; END IF; END LOOP; /** Send QUIT command **/ n_byte_count := UTL_TCP.WRITE_LINE(u_ctrl_con,'QUIT'); /** Don't need to validate QUIT, just close the connection **/ UTL_TCP.CLOSE_CONNECTION(u_ctrl_con); RETURN TRUE; EXCEPTION WHEN ctrl_exception THEN p_error_msg := v_reply; UTL_TCP.CLOSE_ALL_CONNECTIONS; RETURN FALSE; WHEN OTHERS THEN p_error_msg := SQLERRM; UTL_TCP.CLOSE_ALL_CONNECTIONS; RETURN FALSE; END FTP_MULTIPLE; /***************************************************************************** ** Convenience function for single-file PUT ** Formats file information for FTP_MULTIPLE function and calls it. ** *****************************************************************************/ FUNCTION PUT(p_localpath IN VARCHAR2, p_filename IN VARCHAR2, p_remotepath IN VARCHAR2, p_username IN VARCHAR2, p_password IN VARCHAR2, p_hostname IN VARCHAR2, v_status OUT VARCHAR2, v_error_message OUT VARCHAR2, n_bytes_transmitted OUT NUMBER, d_trans_start OUT DATE, d_trans_end OUT DATE, p_port IN PLS_INTEGER DEFAULT 21, p_filetype IN VARCHAR2 := 'ASCII') RETURN BOOLEAN IS t_files t_ftp_rec; v_username VARCHAR2(30) := p_username; v_password VARCHAR2(50) := p_password; v_hostname VARCHAR2(100) := p_hostname; n_port PLS_INTEGER := p_port; v_err_msg VARCHAR2(255); b_ftp BOOLEAN; BEGIN t_files(1).localpath := p_localpath; t_files(1).filename := p_filename; t_files(1).remotepath := p_remotepath; t_files(1).filetype := p_filetype; t_files(1).transfer_mode := 'PUT'; b_ftp := FTP_MULTIPLE(v_err_msg, t_files, v_username, v_password, v_hostname, n_port); IF b_ftp = FALSE THEN v_status := 'ERROR'; v_error_message := v_err_msg; RETURN FALSE; ELSIF b_ftp = TRUE THEN v_status := t_files(1).status; v_error_message := t_files(1).error_message; n_bytes_transmitted := t_files(1).bytes_transmitted; d_trans_start := t_files(1).trans_start; d_trans_end := t_files(1).trans_end; RETURN TRUE; END IF; EXCEPTION WHEN OTHERS THEN v_status := 'ERROR'; v_error_message := SQLERRM; RETURN FALSE; --DBMS_OUTPUT.PUT_LINE(SQLERRM); END PUT; /***************************************************************************** ** Convenience function for single-file GET ** Formats file information for FTP_MULTIPLE function and calls it. ** *****************************************************************************/ FUNCTION GET(p_localpath IN VARCHAR2, p_filename IN VARCHAR2, p_remotepath IN VARCHAR2, p_username IN VARCHAR2, p_password IN VARCHAR2, p_hostname IN VARCHAR2, v_status OUT VARCHAR2, v_error_message OUT VARCHAR2, n_bytes_transmitted OUT NUMBER, d_trans_start OUT DATE, d_trans_end OUT DATE, p_port IN PLS_INTEGER DEFAULT 21, p_filetype IN VARCHAR2 := 'ASCII') RETURN BOOLEAN IS t_files t_ftp_rec; v_username VARCHAR2(30) := p_username; v_password VARCHAR2(50) := p_password; v_hostname VARCHAR2(100) := p_hostname; n_port PLS_INTEGER := p_port; v_err_msg VARCHAR2(255); b_ftp BOOLEAN; BEGIN t_files(1).localpath := p_localpath; t_files(1).filename := p_filename; t_files(1).remotepath := p_remotepath; t_files(1).filetype := p_filetype; t_files(1).transfer_mode := 'GET'; b_ftp := FTP_MULTIPLE(v_err_msg, t_files, v_username, v_password, v_hostname, n_port); IF b_ftp = FALSE THEN v_status := 'ERROR'; v_error_message := v_err_msg; RETURN FALSE; ELSIF b_ftp = TRUE THEN v_status := t_files(1).status; v_error_message := t_files(1).error_message; n_bytes_transmitted := t_files(1).bytes_transmitted; d_trans_start := t_files(1).trans_start; d_trans_end := t_files(1).trans_end; RETURN TRUE; END IF; EXCEPTION WHEN OTHERS THEN v_status := 'ERROR'; v_error_message := SQLERRM; RETURN FALSE; --DBMS_OUTPUT.PUT_LINE(SQLERRM); END GET; END BRNC_FTP_PKG; /