Los contenidos mostrados a continuación son basados en los artículos de Sean Stuber:
https://www.experts-exchange.com/articles/3043/How-to-FTP-with-Oracle-PL-SQL.html
https://www.experts-exchange.com/articles/8429/How-to-use-Access-Control-Lists-in-Oracle.html
Para este tutorial necesitamos:
- Un servidor FTP (recomiendo BYETHOST como FTP gratuito).
- Oracle Database 11g o superior.
Una vez tengamos nuestro servidor FTP configurado y validemos conexión, accedemos al usuario SYS de nuestra base de datos.
Crearemos un usuario en nuestra base para que este realice las peticiones a nuestro servidor FTP.
CREATE USER TEST_FTP IDENTIFIED BY CLAVE;
GRANT CONNECT, RESOURCE TO TEST_FTP;
ALTER USER TEST_FTP ACCOUNT UNLOCK;
Proporcionaremos los siguientes permisos a nuestro usuario recién creado.
GRANT ALL ON UTL_FILE TO TEST_FTP;
GRANT ALL ON UTL_TCP TO TEST_FTP;
A partir de Oracle 11g las conexiones de red se administran mediante ACL o listas de control. En versiones anteriores esto no era necesario. Esto es una medida de seguridad implementada por Oracle.
Crearemos una ACL para que nuestro usuario pueda realizar peticiones a internet.
BEGIN
--DBMS_NETWORK_ACL_ADMIN.drop_acl('my_ip_lookup_acl.xml');
DBMS_NETWORK_ACL_ADMIN.create_acl(
acl => 'my_ip_lookup_acl.xml',
description => 'ACL que permite a los usuarios hacer búsquedas de ip/nombre para cualquier host',
principal => 'TEST_FTP',
is_grant => TRUE,
privilege => 'connect'
);
DBMS_NETWORK_ACL_ADMIN.assign_acl('my_ip_lookup_acl.xml', '*');
COMMIT;
END;
Si deseas ahondar más en estos temas revisa los enlaces al princio del artículo.
Ahora nos conectamos con nuestro usuario TEST_FTP y crearemos el siguiente paquete.
CREATE OR REPLACE PACKAGE sdsftp
AS
-- .///.
-- (0 o)
---------------0000--(_)--0000---------------
--
-- Sean D. Stuber
-- sean.stuber@gmail.com
--
-- oooO Oooo
--------------( )-----( )---------------
-- \ ( ) /
-- \_) (_/
-- Only Passive data transfers are supported
-- Active connections aren't possible because UTL_TCP, as of 11gR2, doesn't support
-- the local_host and local_port parameters and fields of the open procedure and record
--
--
-- Sample usage
--
--DECLARE
-- v_conn sdsftp.connection;
--BEGIN
-- sdsftp.clear_log;
-- sdsftp.set_log_options(1);
-- v_conn := sdsftp.open(:HOST, :username, :pwd);
-- DBMS_OUTPUT.put_line(
-- '----------------------------------------------------------------------------'
-- );
-- DBMS_OUTPUT.put_line(sdsftp.get_clob(v_conn, 'test.txt'));
-- DBMS_OUTPUT.put_line(
-- '----------------------------------------------------------------------------'
-- );
-- sdsftp.close(v_conn);
--EXCEPTION
-- WHEN OTHERS THEN
-- sdsftp.close(v_conn);
-- RAISE;
--END;
TYPE connection IS RECORD
(
tcp UTL_TCP.connection,
account_info VARCHAR2(1000),
transfer_method VARCHAR2(1),
transfer_option VARCHAR2(1),
local_directory VARCHAR2(30),
last_reply VARCHAR2(32767)
);
TYPE file_list IS TABLE OF VARCHAR2(32767)
INDEX BY BINARY_INTEGER;
c_default_ftp_control_port CONSTANT INTEGER := 21;
c_default_transfer_method CONSTANT VARCHAR2(10) := 'ASCII';
c_default_verbose CONSTANT BOOLEAN := FALSE;
c_log_off CONSTANT INTEGER := 0;
c_log_dbms_output CONSTANT INTEGER := 1;
c_log_rolling_buffer CONSTANT INTEGER := 2;
c_log_client_info CONSTANT INTEGER := 4;
PROCEDURE open(p_server IN VARCHAR2,
p_username IN VARCHAR2,
p_password IN VARCHAR2,
p_connection OUT connection,
p_local_directory IN VARCHAR2 DEFAULT NULL,
p_remote_directory IN VARCHAR2 DEFAULT NULL,
p_trans_method IN VARCHAR2 DEFAULT c_default_transfer_method,
p_timeout IN INTEGER DEFAULT NULL,
p_port IN INTEGER DEFAULT c_default_ftp_control_port,
p_account_info IN VARCHAR2 DEFAULT NULL
);
FUNCTION open(p_server IN VARCHAR2,
p_username IN VARCHAR2,
p_password IN VARCHAR2,
p_local_directory IN VARCHAR2 DEFAULT NULL,
p_remote_directory IN VARCHAR2 DEFAULT NULL,
p_trans_method IN VARCHAR2 DEFAULT c_default_transfer_method,
p_timeout IN INTEGER DEFAULT NULL,
p_port IN INTEGER DEFAULT c_default_ftp_control_port,
p_account_info IN VARCHAR2 DEFAULT NULL
)
RETURN connection;
PROCEDURE close(p_connection IN OUT NOCOPY connection);
PROCEDURE set_verbose(p_verbose IN BOOLEAN);
FUNCTION get_verbose
RETURN BOOLEAN;
PROCEDURE write_to_log(v_text IN VARCHAR2, p_verbose IN BOOLEAN DEFAULT FALSE);
PROCEDURE set_log_options(p_log_options IN INTEGER);
FUNCTION get_log_options
RETURN INTEGER;
PROCEDURE clear_log;
FUNCTION get_log_text
RETURN VARCHAR2;
FUNCTION get_last_reply(p_connection IN OUT NOCOPY connection)
RETURN VARCHAR2;
PROCEDURE send_ftp_command(p_connection IN OUT NOCOPY connection,
p_command IN VARCHAR2,
p_arguments IN VARCHAR2 DEFAULT NULL,
p_account_info IN VARCHAR2 DEFAULT NULL
);
PROCEDURE read_reply(p_connection IN OUT NOCOPY connection);
PROCEDURE remote_rename(p_connection IN OUT NOCOPY connection,
p_old_name IN VARCHAR2,
p_new_name IN VARCHAR2
);
PROCEDURE remote_delete_file(p_connection IN OUT NOCOPY connection, p_file IN VARCHAR2);
PROCEDURE remote_create_directory(p_connection IN OUT NOCOPY connection,
p_directory IN VARCHAR2
);
PROCEDURE remote_delete_directory(p_connection IN OUT NOCOPY connection,
p_directory IN VARCHAR2
);
PROCEDURE set_transfer_method(p_connection IN OUT NOCOPY connection,
p_transfer_method IN VARCHAR2,
p_option IN VARCHAR2 DEFAULT NULL
);
FUNCTION get_transfer_method(p_connection IN OUT NOCOPY connection)
RETURN VARCHAR2;
PROCEDURE local_cd(p_connection IN OUT NOCOPY connection, p_directory IN VARCHAR2);
FUNCTION local_pwd(p_connection IN OUT NOCOPY connection)
RETURN VARCHAR2;
PROCEDURE remote_cd(p_connection IN OUT NOCOPY connection, p_directory IN VARCHAR2);
PROCEDURE remote_cdup(p_connection IN OUT NOCOPY connection);
FUNCTION remote_pwd(p_connection IN OUT NOCOPY connection)
RETURN VARCHAR2;
PROCEDURE put_clob(p_connection IN OUT NOCOPY connection,
p_local_clob IN OUT NOCOPY CLOB,
p_remote_file IN VARCHAR2,
p_trans_method IN VARCHAR2 DEFAULT NULL
);
PROCEDURE put_blob(p_connection IN OUT NOCOPY connection,
p_local_blob IN OUT NOCOPY BLOB,
p_remote_file IN VARCHAR2,
p_force_binary IN BOOLEAN DEFAULT TRUE
);
PROCEDURE get_clob(p_connection IN OUT NOCOPY connection,
p_remote_file IN VARCHAR2,
p_local_clob IN OUT NOCOPY CLOB,
p_trans_method IN VARCHAR2 DEFAULT NULL
);
FUNCTION get_clob(p_connection IN OUT NOCOPY connection,
p_remote_file IN VARCHAR2,
p_trans_method IN VARCHAR2 DEFAULT NULL
)
RETURN CLOB;
PROCEDURE get_blob(p_connection IN OUT NOCOPY connection,
p_remote_file IN VARCHAR2,
p_local_blob IN OUT NOCOPY BLOB,
p_force_binary IN BOOLEAN DEFAULT TRUE
);
FUNCTION get_blob(p_connection IN OUT NOCOPY connection,
p_remote_file IN VARCHAR2,
p_force_binary IN BOOLEAN DEFAULT TRUE
)
RETURN BLOB;
PROCEDURE put_file(p_connection IN OUT NOCOPY connection,
p_local_directory IN VARCHAR2,
p_local_file IN VARCHAR2,
p_remote_file IN VARCHAR2,
p_trans_method IN VARCHAR2 DEFAULT NULL
);
PROCEDURE put_file(p_connection IN OUT NOCOPY connection,
p_local_file IN UTL_FILE.file_type,
p_remote_file IN VARCHAR2,
p_trans_method IN VARCHAR2 DEFAULT NULL
);
PROCEDURE get_file(p_connection IN OUT NOCOPY connection,
p_remote_file IN VARCHAR2,
p_local_directory IN VARCHAR2,
p_local_file IN VARCHAR2,
p_trans_method IN VARCHAR2 DEFAULT NULL
);
PROCEDURE get_file(p_connection IN OUT NOCOPY connection,
p_remote_file IN VARCHAR2,
p_trans_method IN VARCHAR2 DEFAULT NULL
);
PROCEDURE get_file(p_connection IN OUT NOCOPY connection,
p_remote_file IN VARCHAR2,
p_local_file IN OUT NOCOPY UTL_FILE.file_type,
p_trans_method IN VARCHAR2 DEFAULT NULL
);
PROCEDURE get_file_list(p_connection IN OUT NOCOPY connection,
p_remote_path IN VARCHAR2,
p_list OUT file_list,
p_names_only IN BOOLEAN DEFAULT FALSE,
p_trans_method IN VARCHAR2 DEFAULT 'ASCII'
);
FUNCTION get_file_list(p_connection IN OUT NOCOPY connection,
p_remote_path IN VARCHAR2,
p_names_only IN BOOLEAN DEFAULT FALSE,
p_trans_method IN VARCHAR2 DEFAULT 'ASCII'
)
RETURN file_list;
END sdsftp;
/
CREATE OR REPLACE PACKAGE BODY sdsftp
AS
-- .///.
-- (0 o)
---------------0000--(_)--0000---------------
--
-- Sean D. Stuber
-- sean.stuber@gmail.com
--
-- oooO Oooo
--------------( )-----( )---------------
-- \ ( ) /
-- \_) (_/
-- 2010-09-16 Bug Fix: Buffer length in binary transfers set to null, meaning all.
-- Bug Fix: Files are closed in routines that generate their own file handle.
-- Routines that receive a file handle are left open.
-- Cosmetic: Minor formatting and variable name changes.
-- 2011-01-18 Enhancement: Added support for listing of remote directories
--
c_crlf CONSTANT CHAR(2) := CHR(13) || CHR(10);
-- Currently unused, would be used with the PORT command to do transfers
-- without passive mode.
c_default_ftp_data_port CONSTANT INTEGER := c_default_ftp_control_port - 1;
-- Per RFC 959, if account info (ACCT) is requested then a 332 code
-- should be returned from the PASS command instead of a Positive Completion
c_ftp_request_acct CONSTANT INTEGER := 332;
g_log_options INTEGER := c_log_dbms_output;
g_log_text VARCHAR2(32767) := NULL;
g_verbose BOOLEAN := c_default_verbose;
-- Parse an FTP reply string, extract the numeric code
FUNCTION reply_code(p_reply_text IN VARCHAR2)
RETURN INTEGER
IS
BEGIN
RETURN TO_NUMBER(SUBSTR(p_reply_text, 1, 3));
END reply_code;
-- When verbose mode is TRUE then additional information will be written to the logs.
-- If logging is turned off then verbose mode won't add anything.
PROCEDURE set_verbose(p_verbose IN BOOLEAN)
IS
BEGIN
g_verbose := NVL(p_verbose, c_default_verbose);
IF p_verbose
THEN
write_to_log('Switching logging to verbose mode', TRUE);
END IF;
END set_verbose;
-- Returns the current setting of verbose mode (true/false)
FUNCTION get_verbose
RETURN BOOLEAN
IS
BEGIN
RETURN g_verbose;
END get_verbose;
-- Turn off logging (options=0) or turn on different options (see c_log_xxxxx constants)
PROCEDURE set_log_options(p_log_options IN INTEGER)
IS
BEGIN
-- options big mask must be between 0 and sum of all possible log options.
IF p_log_options < 0
OR p_log_options > c_log_dbms_output + c_log_rolling_buffer + c_log_client_info
THEN
raise_application_error(
-20001,
'Invalid log options, must be between 0 and '
|| TO_CHAR(c_log_dbms_output + c_log_rolling_buffer + c_log_client_info),
TRUE);
END IF;
g_log_options := p_log_options;
END set_log_options;
-- Return current logging options
FUNCTION get_log_options
RETURN INTEGER
IS
BEGIN
RETURN g_log_options;
END get_log_options;
-- Clears (null) the logging buffers
PROCEDURE clear_log
IS
BEGIN
-- Clear the log buffer
g_log_text := NULL;
-- if logging to session client then clear that too
IF BITAND(g_log_options, c_log_client_info) > 0
THEN
DBMS_APPLICATION_INFO.set_client_info(NULL);
END IF;
END clear_log;
-- Return the current contents of the rolling log buffer
FUNCTION get_log_text
RETURN VARCHAR2
IS
BEGIN
RETURN g_log_text;
END get_log_text;
-- write the text to each logging option that is currently enabled
-- verbose comments will not be logged if verbose mode is off.
PROCEDURE write_to_log(v_text IN VARCHAR2, p_verbose IN BOOLEAN DEFAULT FALSE)
IS
BEGIN
-- If global verbose setting is ON (meaning log everything)
-- or if this text is not verbose then log it.
IF g_verbose OR NOT p_verbose
THEN
IF BITAND(g_log_options, c_log_dbms_output) > 0
THEN
DBMS_OUTPUT.put_line(v_text);
END IF;
IF BITAND(g_log_options, c_log_rolling_buffer) > 0
THEN
IF LENGTH(g_log_text) + LENGTH(c_crlf) + LENGTH(v_text) > 32767
THEN
g_log_text :=
SUBSTR(g_log_text, INSTR(g_log_text, c_crlf, LENGTH(v_text) + 2));
END IF;
g_log_text := g_log_text || v_text || c_crlf;
END IF;
IF BITAND(g_log_options, c_log_client_info) > 0
THEN
DBMS_APPLICATION_INFO.set_client_info(v_text);
END IF;
END IF;
END write_to_log;
-- Log in to the given server and return a connection object
PROCEDURE open(p_server IN VARCHAR2,
p_username IN VARCHAR2,
p_password IN VARCHAR2,
p_connection OUT connection,
p_local_directory IN VARCHAR2 DEFAULT NULL,
p_remote_directory IN VARCHAR2 DEFAULT NULL,
p_trans_method IN VARCHAR2 DEFAULT c_default_transfer_method,
p_timeout IN INTEGER DEFAULT NULL,
p_port IN INTEGER DEFAULT c_default_ftp_control_port,
p_account_info IN VARCHAR2 DEFAULT NULL
)
IS
v_connection connection;
BEGIN
write_to_log('opening connection to: ' || p_server || ':' || p_port, TRUE);
v_connection.tcp :=
UTL_TCP.open_connection(remote_host => p_server,
remote_port => p_port,
tx_timeout => p_timeout
);
read_reply(v_connection);
send_ftp_command(v_connection, 'USER', p_username, p_account_info);
send_ftp_command(v_connection, 'PASS', p_password, p_account_info);
-- If we haven't already been prompted for ACCT info
-- then send it now if we have it
IF p_account_info IS NOT NULL AND v_connection.account_info IS NULL
THEN
send_ftp_command(v_connection, 'ACCT', p_account_info);
v_connection.account_info := p_account_info;
END IF;
IF p_local_directory IS NOT NULL
THEN
local_cd(v_connection, p_local_directory);
END IF;
IF p_remote_directory IS NOT NULL
THEN
remote_cd(v_connection, p_remote_directory);
END IF;
set_transfer_method(v_connection, p_trans_method);
p_connection := v_connection;
END open;
-- Log in to the given server and return a connection object
FUNCTION open(p_server IN VARCHAR2,
p_username IN VARCHAR2,
p_password IN VARCHAR2,
p_local_directory IN VARCHAR2 DEFAULT NULL,
p_remote_directory IN VARCHAR2 DEFAULT NULL,
p_trans_method IN VARCHAR2 DEFAULT c_default_transfer_method,
p_timeout IN INTEGER DEFAULT NULL,
p_port IN INTEGER DEFAULT c_default_ftp_control_port,
p_account_info IN VARCHAR2 DEFAULT NULL
)
RETURN connection
IS
v_connection connection;
BEGIN
sdsftp.open(p_server => p_server,
p_username => p_username,
p_password => p_password,
p_connection => v_connection,
p_local_directory => p_local_directory,
p_remote_directory => p_remote_directory,
p_trans_method => p_trans_method,
p_timeout => p_timeout,
p_port => p_port,
p_account_info => p_account_info
);
RETURN v_connection;
END open;
-- Close the FTP, severs the TCP connection and clears the internal values
PROCEDURE close(p_connection IN OUT NOCOPY connection)
IS
BEGIN
write_to_log(
'closing connection: '
|| p_connection.tcp.remote_host
|| ':'
|| p_connection.tcp.remote_port,
TRUE);
send_ftp_command(p_connection, 'QUIT');
UTL_TCP.close_connection(p_connection.tcp);
p_connection.tcp := NULL;
p_connection.account_info := NULL;
p_connection.transfer_method := NULL;
p_connection.transfer_option := NULL;
p_connection.local_directory := NULL;
p_connection.last_reply := NULL;
write_to_log('connection closed', TRUE);
END close;
-- Request a separate connection on a different port for data transfer
-- PASV is the preferred transfer method opposed to PORT
-- per RFC 1123 (4.1.2.6) all servers must implement PASV.
FUNCTION get_passive_connection(p_connection IN OUT NOCOPY connection)
RETURN UTL_TCP.connection
IS
v_temp VARCHAR2(25);
v_host VARCHAR2(25);
v_port INTEGER;
v_pasv_connection UTL_TCP.connection;
BEGIN
write_to_log('requesting passive connection', TRUE);
send_ftp_command(p_connection, 'PASV');
-- PASV should respond with something of the form 227 zzzzzzzzzzzzzz (hhh,hhh,hhh,hhh,ppp,ppp)
-- where hhh,hhh,hhh,hhh is the host ip address, simply change ',' to '.' and it's ready to go
-- construct the port by taking the first part as the high byte of a 2-byte number (multiply by 256)
-- and the second part as the low byte of the 2-byte number (add it to the high byte)
-- per RFC1123 the host/port digits might not be enclosed in parentheses(),
-- therefore the parsing should be based on a scan of the digits themselves
v_temp := REGEXP_SUBSTR(p_connection.last_reply, '(\d{1,3},){5,5}\d{1,3}');
--
-- 9i and lower don't have regular expressions
-- so, we have to use other methods to strip out the host/port digits
-- v_temp :=
-- RTRIM(
-- LTRIM(
-- TRANSLATE(
-- SUBSTR(p_connection.last_reply, 5),
-- '0123456789,'
-- || TRANSLATE(SUBSTR(p_connection.last_reply, 5), CHR(0) || '0123456789,', CHR(0)),
-- '0123456789,'
-- ),
-- ','
-- ),
-- ','
-- );
-- v_temp should now look like this: 'hhh,hhh,hhh,hhh,ppp,ppp' (minus the quotes)
v_host := REPLACE(SUBSTR(v_temp, 1, INSTR(v_temp, ',', 1, 4) - 1), ',', '.');
v_temp := SUBSTR(v_temp, INSTR(v_temp, ',', 1, 4) + 1);
v_port :=
TO_NUMBER(SUBSTR(v_temp, 1, INSTR(v_temp, ',') - 1)) * 256
+ TO_NUMBER(SUBSTR(v_temp, INSTR(v_temp, ',') + 1));
write_to_log('opening passive connection', TRUE);
v_pasv_connection := UTL_TCP.open_connection(remote_host => v_host, remote_port => v_port);
RETURN v_pasv_connection;
EXCEPTION
WHEN OTHERS
THEN
write_to_log('ERROR in get_passive_connection');
write_to_log(' reply:' || p_connection.last_reply);
write_to_log(' host:' || v_host);
write_to_log(' port:' || v_port);
write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);
RAISE;
END get_passive_connection;
-- Return the last reply from the server (multi-line replies will be returned as a single string)
FUNCTION get_last_reply(p_connection IN OUT NOCOPY connection)
RETURN VARCHAR2
IS
BEGIN
RETURN p_connection.last_reply;
END;
-- Read a reply from the server, including multi-line replies, concatenating them into a single reply string
PROCEDURE read_reply(p_connection IN OUT NOCOPY connection)
IS
-- FTP Replies (per rfc959)
-- replies begin with 3 digit codes xyz
-- the from can be either single line or multi-line
-- if single line, then xyz text
-- if multi-line, then xyz-text, followed by any number of lines, followed by xyz text
-- thus a reply will always end with the xyz code, a space and optionally some text.
--
-- xyz single line reply
--
-- xyz-start of multi-line reply
-- some text
-- some more text
-- xyz end of multi-line reply
--
-- 1yz Positive Preliminary reply
-- 2yz Positive Completion reply
-- 3yz Positive Intermediate reply
-- 4yz Transient Negative Completion reply
-- 5yz Permanent Negative Completion reply
--
-- x0z Syntax
-- x1z Information
-- x2z Connections
-- x3z Authentication and accounting
-- x4z Unspecified as yet.
-- x5z File system
--
-- The third digit gives a finer gradation of meaning in each
-- of the function categories, specified by the second digit.
v_reply VARCHAR2(32767) := NULL;
v_start_code VARCHAR2(3);
v_temp VARCHAR2(32767);
BEGIN
v_temp := UTL_TCP.get_line(p_connection.tcp, TRUE);
IF SUBSTR(v_temp, 4, 1) = ' '
THEN
-- 3 digits and a space is a normal, one line response
v_reply := v_temp;
ELSIF SUBSTR(v_temp, 4, 1) = '-'
THEN
v_start_code := SUBSTR(v_temp, 1, 3);
LOOP
v_temp := UTL_TCP.get_line(p_connection.tcp, TRUE);
IF v_reply IS NULL
THEN
v_reply := v_temp;
ELSE
v_reply := v_reply || UTL_TCP.crlf || v_temp;
END IF;
EXIT WHEN v_temp LIKE v_start_code || ' %';
END LOOP;
ELSE
raise_application_error(-20001, 'Invalid FTP Protocol reply: ' || v_temp, TRUE);
END IF;
p_connection.last_reply := v_reply;
IF g_log_options > 0
THEN
write_to_log(v_reply);
END IF;
IF SUBSTR(v_reply, 1, 1) = '4'
THEN
raise_application_error(-20001, 'Transient error from FTP server: ' || v_reply, TRUE);
ELSIF SUBSTR(v_reply, 1, 1) = '5'
THEN
raise_application_error(-20001, 'Permanent error from FTP server: ' || v_reply, TRUE);
END IF;
EXCEPTION
WHEN UTL_TCP.end_of_input
THEN
NULL;
END read_reply;
-- Send raw FTP protocol command (such as USER, PASS, STOR, RETR, etc)
PROCEDURE send_ftp_command(p_connection IN OUT NOCOPY connection,
p_command IN VARCHAR2,
p_arguments IN VARCHAR2 DEFAULT NULL,
p_account_info IN VARCHAR2 DEFAULT NULL
)
IS
v_code INTEGER;
BEGIN
IF p_arguments IS NULL
THEN
v_code := UTL_TCP.write_line(p_connection.tcp, p_command);
ELSE
v_code := UTL_TCP.write_line(p_connection.tcp, p_command || ' ' || p_arguments);
END IF;
read_reply(p_connection);
IF reply_code(p_connection.last_reply) = c_ftp_request_acct
THEN
send_ftp_command(p_connection, 'ACCT', NVL(p_connection.account_info, p_account_info));
p_connection.account_info := p_account_info;
END IF;
EXCEPTION
WHEN OTHERS
THEN
write_to_log('ERROR in send_ftp_command(' || p_command || ')');
write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);
RAISE;
END send_ftp_command;
-- Rename a file or directory on the FTP server.
-- Note, this operation can be used to move files
PROCEDURE remote_rename(p_connection IN OUT NOCOPY connection,
p_old_name IN VARCHAR2,
p_new_name IN VARCHAR2
)
IS
BEGIN
write_to_log('renaming remote file: ' || p_old_name || ' to ' || p_new_name, TRUE);
send_ftp_command(p_connection, 'RNFR', p_old_name);
send_ftp_command(p_connection, 'RNTO', p_new_name);
EXCEPTION
WHEN OTHERS
THEN
write_to_log('ERROR in remote_rename(' || p_old_name || ',' || p_new_name || ')');
write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);
RAISE;
END remote_rename;
-- Delete a file on the FTP server.
PROCEDURE remote_delete_file(p_connection IN OUT NOCOPY connection, p_file IN VARCHAR2)
IS
BEGIN
write_to_log('deleting remote file: ' || p_file, TRUE);
send_ftp_command(p_connection, 'DELE', p_file);
EXCEPTION
WHEN OTHERS
THEN
write_to_log('ERROR in remote_delete_file(' || p_file || ')');
write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);
RAISE;
END remote_delete_file;
-- Create a directory on the FTP server.
PROCEDURE remote_create_directory(p_connection IN OUT NOCOPY connection,
p_directory IN VARCHAR2
)
IS
BEGIN
write_to_log('creating remote directory: ' || p_directory, TRUE);
send_ftp_command(p_connection, 'MKD', p_directory);
EXCEPTION
WHEN OTHERS
THEN
write_to_log('ERROR in remote_create_directory(' || p_directory || ')');
write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);
RAISE;
END remote_create_directory;
-- Delete a directory on the FTP server.
PROCEDURE remote_delete_directory(p_connection IN OUT NOCOPY connection,
p_directory IN VARCHAR2
)
IS
BEGIN
write_to_log('deleting remote directory: ' || p_directory, TRUE);
send_ftp_command(p_connection, 'RMD', p_directory);
EXCEPTION
WHEN OTHERS
THEN
write_to_log('ERROR in remote_delete_directory(' || p_directory || ')');
write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);
RAISE;
END remote_delete_directory;
-- Sets the transfer method, it will accept FTP protocol values of A, I, E
-- as well as the key words ASCII, IMAGE, EBCIDIC, BINARY, DEFAULT
-- The first three map to the protocol characters, BINARY is equivalent to I
-- DEFAULT will set the method equal to the default method defined in this package's
-- specification. If NULL, then no change will be made.
-- The A, E and I methods can also be parameterized if necessary (usually won't be)
-- The FTP protocol representation L is NOT supported at this time.
PROCEDURE set_transfer_method(p_connection IN OUT NOCOPY connection,
p_transfer_method IN VARCHAR2,
p_option IN VARCHAR2 DEFAULT NULL
)
IS
v_method VARCHAR2(10) := UPPER(SUBSTR(p_transfer_method, 1, 10));
v_option VARCHAR2(1) := UPPER(SUBSTR(p_option, 1, 1));
v_new_method VARCHAR2(3);
BEGIN
CASE
WHEN v_method IS NULL
THEN
-- Do nothing, if NULL is the new method, then just keep the current one
NULL;
WHEN v_method = 'DEFAULT'
THEN
v_new_method := SUBSTR(c_default_transfer_method, 1, 1);
WHEN v_method = 'BINARY'
THEN
v_new_method := 'I';
WHEN v_method IN ('A', 'ASCII', 'I', 'IMAGE', 'E', 'EBCDIC')
THEN
v_new_method := SUBSTR(v_method, 1, 1);
ELSE
raise_application_error(
-20001,
'SDSFTP.set_transfer_method INVALID method: ' || p_transfer_method,
TRUE);
END CASE;
IF v_new_method != NVL(p_connection.transfer_method, '---')
THEN
write_to_log('changing transfer method', TRUE);
IF v_option IN ('N', 'T', 'C')
THEN
send_ftp_command(p_connection, 'TYPE', v_new_method || ' ' || v_option);
ELSIF v_option IS NULL
THEN
send_ftp_command(p_connection, 'TYPE', v_new_method);
ELSE
raise_application_error(-20001,
'SDSFTP.set_transfer_method INVALID option: ' || p_option,
TRUE
);
END IF;
p_connection.transfer_method := v_new_method;
p_connection.transfer_option := v_option;
write_to_log('Transfer method changed', TRUE);
END IF;
END set_transfer_method;
-- Return the full name of the current transfer method for the given connection
-- Note, the FTP protocol representation L is NOT supported at this time.
FUNCTION get_transfer_method(p_connection IN OUT NOCOPY connection)
RETURN VARCHAR2
IS
BEGIN
RETURN CASE
WHEN p_connection.transfer_method = 'A' THEN 'ASCII'
WHEN p_connection.transfer_method = 'E' THEN 'EBCDIC'
WHEN p_connection.transfer_method = 'I' THEN 'IMAGE'
END;
END get_transfer_method;
-- Change the directory used locally for sending files from or retrieving files into
-- a directory can be an explicit path supported by utl_file_dir parameter
-- or it can be a directory object.
PROCEDURE local_cd(p_connection IN OUT NOCOPY connection, p_directory IN VARCHAR2)
IS
BEGIN
p_connection.local_directory := p_directory;
END local_cd;
-- Returns the current directory used locally
-- Unlike a server which will always have a current directory
-- the local client might be NULL if a local_cd has not occurred.
FUNCTION local_pwd(p_connection IN OUT NOCOPY connection)
RETURN VARCHAR2
IS
BEGIN
RETURN p_connection.local_directory;
END local_pwd;
-- Returns the current directory for the remote host
FUNCTION remote_pwd(p_connection IN OUT NOCOPY connection)
RETURN VARCHAR2
IS
v_temp VARCHAR2(32767);
BEGIN
write_to_log('Requesting remote directory', TRUE);
send_ftp_command(p_connection, 'PWD');
-- reply should be of the from 257 "directoryname"
-- strip the return code prefix and leading double quote
v_temp := SUBSTR(p_connection.last_reply, 6);
-- strip off trailing double-qoutes
v_temp := RTRIM(v_temp, '"');
-- double-qoutes within the name will be escaped with double-qoutes i.e. ""
-- un-escape any embedded double-quotes
v_temp := REPLACE(v_temp, '""', '"');
write_to_log('Remote directory is:' || v_temp, TRUE);
RETURN v_temp;
END remote_pwd;
-- Change to the given directory on the remote host
PROCEDURE remote_cd(p_connection IN OUT NOCOPY connection, p_directory IN VARCHAR2)
IS
BEGIN
write_to_log('Changing remote directory to: ' || p_directory, TRUE);
send_ftp_command(p_connection, 'CWD', p_directory);
write_to_log('Remote directory changed', TRUE);
END remote_cd;
-- Move up to the parent directory on the remote host
PROCEDURE remote_cdup(p_connection IN OUT NOCOPY connection)
IS
BEGIN
write_to_log('Changing to remote parent directory', TRUE);
send_ftp_command(p_connection, 'CDUP');
write_to_log('Changed to remote parent directory', TRUE);
END;
-- Given a local clob, send it to the remote host to be saved as a file
-- by default the current transfer method can be used or it can be
-- changed as part of this procedure
PROCEDURE put_clob(p_connection IN OUT NOCOPY connection,
p_local_clob IN OUT NOCOPY CLOB,
p_remote_file IN VARCHAR2,
p_trans_method IN VARCHAR2 DEFAULT NULL
)
IS
v_read_cnt INTEGER;
v_write_cnt INTEGER;
v_lob_length INTEGER;
v_index INTEGER;
v_chunk VARCHAR2(32767);
v_data UTL_TCP.connection;
BEGIN
set_transfer_method(p_connection, p_trans_method);
v_data := get_passive_connection(p_connection);
send_ftp_command(p_connection, 'STOR', p_remote_file);
write_to_log('storing file on: ' || v_data.remote_host || ':' || v_data.remote_port, TRUE);
v_lob_length := DBMS_LOB.getlength(p_local_clob);
v_index := 1;
WHILE v_index <= v_lob_length
LOOP
v_read_cnt := 32767;
DBMS_LOB.read(p_local_clob, v_read_cnt, v_index, v_chunk);
v_write_cnt := UTL_TCP.write_text(v_data, v_chunk, NULL);
UTL_TCP.flush(v_data);
v_index := v_index + v_write_cnt;
END LOOP;
write_to_log(
'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
TRUE);
UTL_TCP.close_connection(v_data);
read_reply(p_connection);
EXCEPTION
WHEN OTHERS
THEN
write_to_log('ERROR in put_clob(' || p_remote_file || ')');
write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);
write_to_log(
'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
TRUE);
UTL_TCP.close_connection(v_data);
RAISE;
END put_clob;
-- Given a local clob, send it to the remote host to be saved as a file
-- by default the current transfer method will be ignored
-- and the procedure will temporarily switch to Image/Binary transfer
-- if force_binary is FALSE then the current method will be used.
PROCEDURE put_blob(p_connection IN OUT NOCOPY connection,
p_local_blob IN OUT NOCOPY BLOB,
p_remote_file IN VARCHAR2,
p_force_binary IN BOOLEAN DEFAULT TRUE
)
IS
v_save_method VARCHAR2(1);
v_read_cnt INTEGER;
v_write_cnt INTEGER;
v_lob_length INTEGER;
v_index INTEGER;
v_chunk RAW(32767);
v_data UTL_TCP.connection;
BEGIN
IF p_force_binary AND p_connection.transfer_method != 'I'
THEN
v_save_method := p_connection.transfer_method;
write_to_log('Saving current transfer method: ' || v_save_method, TRUE);
write_to_log('Setting transfer method for BLOB binary transfer', TRUE);
set_transfer_method(p_connection, 'IMAGE');
END IF;
v_data := get_passive_connection(p_connection);
send_ftp_command(p_connection, 'STOR', p_remote_file);
write_to_log('storing file on: ' || v_data.remote_host || ':' || v_data.remote_port, TRUE);
v_lob_length := DBMS_LOB.getlength(p_local_blob);
v_index := 1;
WHILE v_index <= v_lob_length
LOOP
v_read_cnt := 32767;
DBMS_LOB.read(p_local_blob, v_read_cnt, v_index, v_chunk);
v_write_cnt := UTL_TCP.write_raw(v_data, v_chunk, NULL);
UTL_TCP.flush(v_data);
v_index := v_index + v_write_cnt;
END LOOP;
write_to_log(
'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
TRUE);
UTL_TCP.close_connection(v_data);
read_reply(p_connection);
IF p_force_binary AND v_save_method != 'I'
THEN
write_to_log('Restoring previous transfer method:' || v_save_method, TRUE);
set_transfer_method(p_connection, v_save_method, SUBSTR(v_save_method, 3, 1));
END IF;
EXCEPTION
WHEN OTHERS
THEN
write_to_log('ERROR in put_blob(' || p_remote_file || ')');
write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);
write_to_log(
'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
TRUE);
UTL_TCP.close_connection(v_data);
RAISE;
END put_blob;
-- Read a remote file directly into a local clob
-- there will be no OS file created locally by this
-- by default the current transfer method will be used
-- but it can be changed as part of this procedure call
PROCEDURE get_clob(p_connection IN OUT NOCOPY connection,
p_remote_file IN VARCHAR2,
p_local_clob IN OUT NOCOPY CLOB,
p_trans_method IN VARCHAR2 DEFAULT NULL
)
IS
v_char_cnt INTEGER;
v_chunk VARCHAR2(32767);
v_error VARCHAR(32767);
v_data UTL_TCP.connection;
BEGIN
v_data := get_passive_connection(p_connection);
set_transfer_method(p_connection, p_trans_method);
send_ftp_command(p_connection, 'RETR', p_remote_file);
write_to_log('retrieving file from: ' || v_data.remote_host || ':' || v_data.remote_port,
TRUE
);
LOOP
BEGIN
v_char_cnt := UTL_TCP.read_text(v_data, v_chunk, 32767);
DBMS_LOB.writeappend(p_local_clob, v_char_cnt, v_chunk);
EXCEPTION
WHEN UTL_TCP.end_of_input
THEN
EXIT;
WHEN OTHERS
THEN
RAISE;
END;
END LOOP;
write_to_log(
'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
TRUE);
UTL_TCP.close_connection(v_data);
read_reply(p_connection);
EXCEPTION
WHEN OTHERS
THEN
write_to_log('ERROR in get_clob(' || p_remote_file || ')');
write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);
write_to_log(
'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
TRUE);
UTL_TCP.close_connection(v_data);
RAISE;
END get_clob;
-- Read a remote file directly into a local clob
-- there will be no OS file created locally by this
-- by default the current transfer method will be used
-- but it can be changed as part of this procedure call
FUNCTION get_clob(p_connection IN OUT NOCOPY connection,
p_remote_file IN VARCHAR2,
p_trans_method IN VARCHAR2 DEFAULT NULL
)
RETURN CLOB
IS
v_clob CLOB;
BEGIN
DBMS_LOB.createtemporary(v_clob, TRUE);
get_clob(p_connection, p_remote_file, v_clob, p_trans_method);
RETURN v_clob;
EXCEPTION
WHEN OTHERS
THEN
IF DBMS_LOB.istemporary(v_clob) = 1
THEN
IF DBMS_LOB.ISOPEN(v_clob) = 1
THEN
DBMS_LOB.close(v_clob);
END IF;
DBMS_LOB.freetemporary(v_clob);
END IF;
RAISE;
END get_clob;
-- Read a remote file directly into a local blob
-- there will be no OS file created locally by this
-- by default the current transfer method will be ignored
-- and the procedure will temporarily switch to Image/Binary transfer
-- if force_binary is FALSE then the current method will be used.
PROCEDURE get_blob(p_connection IN OUT NOCOPY connection,
p_remote_file IN VARCHAR2,
p_local_blob IN OUT NOCOPY BLOB,
p_force_binary IN BOOLEAN DEFAULT TRUE
)
IS
v_save_method VARCHAR2(1);
v_byte_cnt INTEGER;
v_chunk RAW(32767);
v_error VARCHAR(32767);
v_data UTL_TCP.connection;
BEGIN
IF p_force_binary AND p_connection.transfer_method != 'I'
THEN
v_save_method := p_connection.transfer_method;
write_to_log('Saving current transfer method: ' || v_save_method, TRUE);
write_to_log('Setting transfer method for BLOB binary transfer', TRUE);
set_transfer_method(p_connection, 'IMAGE');
END IF;
v_data := get_passive_connection(p_connection);
send_ftp_command(p_connection, 'RETR', p_remote_file);
write_to_log('retrieving file from: ' || v_data.remote_host || ':' || v_data.remote_port,
TRUE
);
LOOP
BEGIN
v_byte_cnt := UTL_TCP.read_raw(v_data, v_chunk, 32767);
DBMS_LOB.writeappend(p_local_blob, v_byte_cnt, v_chunk);
EXCEPTION
WHEN UTL_TCP.end_of_input
THEN
EXIT;
WHEN OTHERS
THEN
RAISE;
END;
END LOOP;
write_to_log(
'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
TRUE);
UTL_TCP.close_connection(v_data);
read_reply(p_connection);
IF p_force_binary AND v_save_method != 'I'
THEN
write_to_log('Restoring previous transfer method:' || v_save_method, TRUE);
set_transfer_method(p_connection, v_save_method, SUBSTR(v_save_method, 3, 1));
END IF;
EXCEPTION
WHEN OTHERS
THEN
write_to_log('ERROR in get_blob(' || p_remote_file || ')');
write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);
write_to_log(
'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
TRUE);
UTL_TCP.close_connection(v_data);
RAISE;
END get_blob;
-- Read a remote file directly into a local blob
-- there will be no OS file created locally by this
-- by default the current transfer method will be ignored
-- and the procedure will temporarily switch to Image/Binary transfer
-- if force_binary is FALSE then the current method will be used.
FUNCTION get_blob(p_connection IN OUT NOCOPY connection,
p_remote_file IN VARCHAR2,
p_force_binary IN BOOLEAN DEFAULT TRUE
)
RETURN BLOB
IS
v_blob BLOB;
BEGIN
DBMS_LOB.createtemporary(v_blob, TRUE);
sdsftp.get_blob(p_connection, p_remote_file, v_blob, p_force_binary);
RETURN v_blob;
EXCEPTION
WHEN OTHERS
THEN
IF DBMS_LOB.istemporary(v_blob) = 1
THEN
IF DBMS_LOB.ISOPEN(v_blob) = 1
THEN
DBMS_LOB.close(v_blob);
END IF;
DBMS_LOB.freetemporary(v_blob);
END IF;
RAISE;
END get_blob;
-- Given a local file, send it to the remote host to be saved as a file
-- by default the current transfer method will be used but it can be
-- overridden. The procedure is overloaded 3 ways based on available values.
-- This version reads from a local utl_file pointer and sends that file
-- to the remote server.
-- with directory and local file, with utl_file pointer,
-- or just file names with current directory
PROCEDURE put_file(p_connection IN OUT NOCOPY connection,
p_local_file IN UTL_FILE.file_type,
p_remote_file IN VARCHAR2,
p_trans_method IN VARCHAR2 DEFAULT NULL
)
IS
v_byte_cnt INTEGER;
v_textchunk VARCHAR2(32767);
v_binchunk RAW(32767);
v_data UTL_TCP.connection;
BEGIN
set_transfer_method(p_connection, p_trans_method);
v_data := get_passive_connection(p_connection);
send_ftp_command(p_connection, 'STOR', p_remote_file);
write_to_log('writing file to: ' || v_data.remote_host || ':' || v_data.remote_port, TRUE);
LOOP
BEGIN
IF p_connection.transfer_method = 'I'
THEN
UTL_FILE.get_raw(file => p_local_file, buffer => v_binchunk, len => 32767);
v_byte_cnt := UTL_TCP.write_raw(v_data, v_binchunk, NULL);
ELSE
UTL_FILE.get_line(file => p_local_file, buffer => v_textchunk, len => 32767);
v_byte_cnt := UTL_TCP.write_text(v_data, v_textchunk, NULL);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT;
WHEN OTHERS
THEN
RAISE;
END;
END LOOP;
write_to_log(
'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
TRUE);
UTL_TCP.close_connection(v_data);
read_reply(p_connection);
EXCEPTION
WHEN OTHERS
THEN
write_to_log('ERROR in put_file(' || p_remote_file || ')');
write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);
write_to_log(
'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
TRUE);
UTL_TCP.close_connection(v_data);
RAISE;
END put_file;
-- Given a local file, send it to the remote host to be saved as a file
-- by default the current transfer method will be used but it can be
-- overridden. The procedure is overloaded 3 ways based on available values.
-- This version reads from a file from a local directory and sends that file
-- to the remote server.
PROCEDURE put_file(p_connection IN OUT NOCOPY connection,
p_local_directory IN VARCHAR2,
p_local_file IN VARCHAR2,
p_remote_file IN VARCHAR2,
p_trans_method IN VARCHAR2 DEFAULT NULL
)
IS
v_file UTL_FILE.file_type;
BEGIN
IF p_local_directory IS NULL
THEN
write_to_log(
'Local directory not set prior to get_file('
|| p_local_directory
|| ','
|| p_local_file
|| ')');
raise_application_error(
-20001,
'SDSFTP.PUT_FILE - Local directory must be specified to GET files',
TRUE);
END IF;
IF p_remote_file IS NULL
THEN
write_to_log(
'NULL file name used for put_file('
|| p_local_directory
|| ','
|| p_local_file
|| ')');
raise_application_error(-20001,
'SDSFTP.GET_FILE - Can not PUT a file into a NULL file name',
TRUE
);
END IF;
IF NVL(UPPER(p_trans_method), p_connection.transfer_method) IN ('BINARY', 'IMAGE', 'I')
THEN
v_file := UTL_FILE.fopen(p_local_directory, p_local_file, 'rb', 32767);
ELSE
v_file := UTL_FILE.fopen(p_local_directory, p_local_file, 'r', 32767);
END IF;
sdsftp.put_file(p_connection => p_connection,
p_local_file => v_file,
p_remote_file => p_remote_file,
p_trans_method => p_trans_method
);
UTL_FILE.fclose(v_file);
EXCEPTION
WHEN OTHERS
THEN
write_to_log('ERROR in put_file(' || p_remote_file || ')');
write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);
IF UTL_FILE.is_open(v_file)
THEN
UTL_FILE.fclose(v_file);
END IF;
RAISE;
END put_file;
-- Given a local file, send it to the remote host to be saved as a file
-- by default the current transfer method will be used but it can be
-- overridden. The procedure is overloaded 3 ways based on available values.
-- This version reads from a file from the current local directory.
PROCEDURE put_file(p_connection IN OUT NOCOPY connection,
p_local_file IN VARCHAR2,
p_trans_method IN VARCHAR2 DEFAULT NULL
)
IS
BEGIN
IF p_connection.local_directory IS NULL
THEN
write_to_log('Local directory not set prior to put_file(' || p_local_file || ')');
raise_application_error(
-20001,
'SDSFTP.PUT_FILE - Local directory must be specified to PUT files',
TRUE);
END IF;
sdsftp.put_file(p_connection => p_connection,
p_local_directory => p_connection.local_directory,
p_local_file => p_local_file,
p_remote_file => p_local_file,
p_trans_method => p_trans_method
);
END put_file;
-- Read remote file to local UTL_FILE file handle
-- by default the current transfer mode will be used but it can be changed
-- This is the driver function that all other "get_file" procedures invoke
PROCEDURE get_file(p_connection IN OUT NOCOPY connection,
p_remote_file IN VARCHAR2,
p_local_file IN OUT NOCOPY UTL_FILE.file_type,
p_trans_method IN VARCHAR2 DEFAULT NULL
)
IS
v_transfer_cnt INTEGER; -- bytes for raw, characters for Text
v_textchunk VARCHAR2(32767);
v_binchunk RAW(32767);
v_data UTL_TCP.connection;
BEGIN
set_transfer_method(p_connection, p_trans_method);
v_data := get_passive_connection(p_connection);
send_ftp_command(p_connection, 'RETR', p_remote_file);
write_to_log('retrieving file from: ' || v_data.remote_host || ':' || v_data.remote_port,
TRUE
);
LOOP
BEGIN
IF p_connection.transfer_method = 'I'
THEN
v_transfer_cnt := UTL_TCP.read_raw(v_data, v_binchunk, 32767);
UTL_FILE.put_raw(file => p_local_file, buffer => v_binchunk, autoflush => TRUE);
ELSE
v_transfer_cnt := UTL_TCP.read_text(v_data, v_textchunk, 32767);
UTL_FILE.put(file => p_local_file, buffer => v_textchunk);
END IF;
UTL_FILE.fflush(p_local_file);
EXCEPTION
WHEN UTL_TCP.end_of_input
THEN
EXIT;
WHEN OTHERS
THEN
RAISE;
END;
END LOOP;
write_to_log(
'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
TRUE);
UTL_TCP.close_connection(v_data);
read_reply(p_connection);
EXCEPTION
WHEN OTHERS
THEN
write_to_log('ERROR in get_file(' || p_remote_file || ')');
write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);
write_to_log(
'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
TRUE);
UTL_TCP.close_connection(v_data);
RAISE;
END get_file;
-- Read remote file to selected local directory and new local name
-- by default the current transfer mode will be used but it can be changed
PROCEDURE get_file(p_connection IN OUT NOCOPY connection,
p_remote_file IN VARCHAR2,
p_local_directory IN VARCHAR2,
p_local_file IN VARCHAR2,
p_trans_method IN VARCHAR2 DEFAULT NULL
)
IS
v_file UTL_FILE.file_type;
BEGIN
IF p_local_directory IS NULL
THEN
write_to_log('Local directory not set prior to get_file(' || p_remote_file || ')');
raise_application_error(
-20001,
'SDSFTP.GET_FILE - Local directory must be specified to GET files',
TRUE);
END IF;
IF p_local_file IS NULL
THEN
write_to_log('NULL file name used for get_file(' || p_remote_file || ')');
raise_application_error(-20001,
'SDSFTP.GET_FILE - Can not GET a file into a NULL file name',
TRUE
);
END IF;
IF NVL(UPPER(p_trans_method), p_connection.transfer_method) IN ('BINARY', 'IMAGE', 'I')
THEN
v_file := UTL_FILE.fopen(p_local_directory, p_local_file, 'wb', 32767);
ELSE
v_file := UTL_FILE.fopen(p_local_directory, p_local_file, 'w', 32767);
END IF;
sdsftp.get_file(p_connection => p_connection,
p_remote_file => p_remote_file,
p_local_file => v_file,
p_trans_method => p_trans_method
);
UTL_FILE.fclose(v_file);
EXCEPTION
WHEN OTHERS
THEN
write_to_log('ERROR in get_file(' || p_remote_file || ')');
write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);
IF UTL_FILE.is_open(v_file)
THEN
UTL_FILE.fclose(v_file);
END IF;
RAISE;
END get_file;
-- Read remote file to current local directory with the same name
-- by default the current transfer mode will be used but it can be changed
PROCEDURE get_file(p_connection IN OUT NOCOPY connection,
p_remote_file IN VARCHAR2,
p_trans_method IN VARCHAR2 DEFAULT NULL
)
IS
BEGIN
IF p_connection.local_directory IS NULL
THEN
write_to_log('Local directory not set prior to get_file(' || p_remote_file || ')');
raise_application_error(
-20001,
'SDSFTP.GET_FILE - Local directory must be specified to GET files',
TRUE);
END IF;
sdsftp.get_file(p_connection => p_connection,
p_remote_file => p_remote_file,
p_local_directory => p_connection.local_directory,
p_local_file => p_remote_file,
p_trans_method => p_trans_method
);
END get_file;
-- Given a path, return the list of files, but default it return the full
-- file information but that may be changed to just the file names themselves.
-- Supported transfer methods are ASCII and EBCDIC only.
-- The current transfer method will be restored after listing if it was
-- changed by this routine.
-- Note the FTP RFC does not specify what the format will be so all information
-- is returned simply as a collection of strings.
PROCEDURE get_file_list(p_connection IN OUT NOCOPY connection,
p_remote_path IN VARCHAR2,
p_list OUT file_list,
p_names_only IN BOOLEAN DEFAULT FALSE,
p_trans_method IN VARCHAR2 DEFAULT 'ASCII'
)
IS
v_transfer_cnt INTEGER; -- bytes for raw, characters for Text
v_textchunk VARCHAR2(32767);
v_binchunk RAW(32767);
v_data UTL_TCP.connection;
v_save_method VARCHAR2(1) := NULL;
v_index INTEGER := 1;
BEGIN
IF p_trans_method NOT IN ('A', 'ASCII', 'E', 'EBCDIC')
THEN
raise_application_error(-20001,
'SDSFTP.get_file_list INVALID method: ' || p_trans_method,
TRUE
);
END IF;
IF p_trans_method != get_transfer_method(p_connection)
THEN
v_save_method := p_connection.transfer_method;
write_to_log('Saving current transfer method: ' || v_save_method, TRUE);
write_to_log('Setting ' || p_trans_method || ' method for remote file listing', TRUE);
set_transfer_method(p_connection, p_trans_method);
END IF;
v_data := get_passive_connection(p_connection);
IF p_names_only
THEN
send_ftp_command(p_connection, 'NLST', p_remote_path);
ELSE
send_ftp_command(p_connection, 'LIST', p_remote_path);
END IF;
write_to_log(
'retrieving file list from: ' || v_data.remote_host || ':' || v_data.remote_port,
TRUE);
p_list.delete;
LOOP
BEGIN
v_transfer_cnt := UTL_TCP.read_line(v_data, v_textchunk, TRUE, FALSE);
p_list(v_index) := v_textchunk;
v_index := v_index + 1;
EXCEPTION
WHEN UTL_TCP.end_of_input
THEN
EXIT;
WHEN OTHERS
THEN
RAISE;
END;
END LOOP;
write_to_log(
'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
TRUE);
UTL_TCP.close_connection(v_data);
read_reply(p_connection);
IF v_save_method IS NOT NULL
THEN
set_transfer_method(p_connection, v_save_method);
END IF;
EXCEPTION
WHEN OTHERS
THEN
write_to_log('ERROR in get_file_list(' || p_remote_path || ')');
write_to_log(DBMS_UTILITY.format_error_stack || DBMS_UTILITY.format_error_backtrace);
write_to_log(
'closing passive connection: ' || v_data.remote_host || ':' || v_data.remote_port,
TRUE);
UTL_TCP.close_connection(v_data);
IF v_save_method IS NOT NULL
THEN
set_transfer_method(p_connection, v_save_method);
END IF;
RAISE;
END get_file_list;
-- Given a path, return the list of files, but default it return the full
-- file information but that may be changed to just the file names themselves.
-- Supported transfer methods are ASCII and EBCDIC only.
-- The current transfer method will be restored after listing if it was
-- changed by this routine.
-- Note the FTP RFC does not specify what the format will be so all information
-- is returned simply as a collection of strings.
FUNCTION get_file_list(p_connection IN OUT NOCOPY connection,
p_remote_path IN VARCHAR2,
p_names_only IN BOOLEAN DEFAULT FALSE,
p_trans_method IN VARCHAR2 DEFAULT 'ASCII'
)
RETURN file_list
IS
p_list file_list;
BEGIN
get_file_list(p_connection, p_remote_path, p_list, p_names_only, p_trans_method);
RETURN p_list;
END;
END sdsftp;
/
Para probar nuestra conectividad, intentaremos conectarnos a nuestro servidor FTP y mostrar en consola el contenido de un archivo.
En nuestro FTP, dicho archivo está en la ruta /htdocs/test/ y se llama test.txt
Creamos el siguiente bloque de código en nuestro usuario TEST_FTP con las credenciales de nuestro servidor.
set serveroutput on
DECLARE
v_conn sdsftp.connection;
v_list sdsftp.file_list;
ruta_ftp varchar2(100) := '/htdocs/test/';
archivo varchar2(100) := 'test.txt';
BEGIN
sdsftp.clear_log;
sdsftp.set_log_options(0);
v_conn := sdsftp.open(:host_ftp, :user_ftp, :pass_ftp);
DBMS_OUTPUT.put_line(
'----------------------------------------------------------------------------'
);
v_list := sdsftp.get_file_list(v_conn, ruta_ftp, true);
FOR i IN v_list.FIRST .. v_list.LAST
LOOP
DBMS_OUTPUT.put_line(v_list(i));
if(v_list(i) = archivo)
then
DBMS_OUTPUT.put_line('>>CONTENIDO DEL ARCHIVO<<');
DBMS_OUTPUT.put_line(sdsftp.get_clob(v_conn, ruta_ftp||archivo));
end if;
END LOOP;
DBMS_OUTPUT.put_line(
'----------------------------------------------------------------------------'
);
sdsftp.close(v_conn);
EXCEPTION
WHEN OTHERS
THEN
sdsftp.close(v_conn);
RAISE;
END;
El resultado es una impresión en consola del contenido del archivo.
Fragmentos de Código 