FTP

Dicas do Oracle Forms Builder - Blocos, Itens, LOV, Canvas, Triggers, comandos, PLL, d2kwutil, FMB, Alert, menus, etc
Responder
Deathão
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 57
Registrado em: Qui, 16 Jun 2005 11:31 am
Localização: Assis
Contato:
--
Júnior

fala Pessoal e vai mais uma do meu caminhão de dúvidas aí!!!


gostaria de saber se existem alguma forma de mandar um arquivo para o FTP..

se tiver algum exemplo passar pra nóis aí.. :P


forte abraço...

té a próxima, que não será muito demorada!!
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Bem... Creio que a forma mais simples de você enviar algo por FTP é usar o próprio comando FTP.

Tipo dá um HOST no seu form que chama o comando FTP.
Algo assim:

Selecionar tudo

host('ftp -s:envia.txt', NO_SCREEN);
O envia.txt fica mais ou menos assim:

Selecionar tudo

open
ftp.seu_ftp.com.br
seu_login
sua_senha
put 
seu_arquivo_local
seu_arquivo_destino
close 
bye
Existe também uma outra forma que eu nunca testei... Mas aí vai pra quem quiser testar. É claro, avisa a galera se funcionou!!! :-)

Selecionar tudo

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;
/
Editado pela última vez por dr_gori em Qui, 30 Jun 2005 10:19 am, em um total de 1 vez.
Deathão
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 57
Registrado em: Qui, 16 Jun 2005 11:31 am
Localização: Assis
Contato:
--
Júnior

:shock: cara***, que "códigão"... hahahaha

valeu Dr...
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 8 visitantes