/*----------------------------------------------------------------------------*/
/*  Program Name: FTP_PKG.pls                                                 */
/*  Program Date: 05/16/06                                                    */
/*  Author:       Dennis J. Paulus                                            */
/*  Description:  This stored procedure will provide the ability to FTP       */
/*                files using PL/SQL.                                         */
/*                                                                            */
/*  Parameters:                                                               */
/*     1. p_put_get_cmd         - 'PUT' (send file); 'GET' (receive file)     */
/*     2. p_local_directory     - Local Directory (must be setup as a DB dir) */
/*     3. p_local_filename      - Local Filename to put or get                */
/*     4. p_remote_server       - Remote Server name or IP Address            */
/*     5. p_remote_port         - Remote Server port (Default: 21)            */
/*     6. p_remote_directory    - Remote Server Directory                     */
/*     7. p_remote_filename     - Remote Server Filename to put or get        */
/*     8. p_remote_username     - Remote Server Username (used to login)      */
/*     9. p_remote_password     - Remote Server Password (used to login)      */
/*    10. p_remote_trans_method - 'ASCII' or 'BINARY' (Default: ASCII)        */
/*    11. x_ret_value   - Procedure return message                            */
/*    12. x_ret_status  - Procedure return status ('ERROR' or 'SUCCESS')      */
/*                                                                            */
/*  Requirements:                                                             */
/*    1. p_local_directory:  This parameter must exist as a database          */
/*                           directory in the table ALL_DIRECTORIES. This can */
/*                           be done by using the CREATE DIRECTORY command.   */
/*    2. Unix Permissions:   The correct permissions are required in order    */
/*                           to read and write files. The files will have     */
/*                           an owner of 'oracle' and the directory must      */
/*                           have group read/write access.                    */
/*                           (eg. chmod 775 *)                                */
/*                                                                            */
/*  Modification History:                                                     */
/*    Name:             Date:     Description of Change:                      */
/*    Dennis J. Paulus  05/16/06  Created.                                    */
/*----------------------------------------------------------------------------*/


/*--- Package Specification for FTP_PKG ---*/
create or replace package FTP_PKG as

procedure ftp (p_put_get_cmd          in  varchar2,
               p_local_directory      in  varchar2,
               p_local_filename       in  varchar2,
               p_remote_server        in  varchar2,
               p_remote_port          in  pls_integer default 21,
               p_remote_directory     in  varchar2,
               p_remote_filename      in  varchar2,
               p_remote_username      in  varchar2,
               p_remote_password      in  varchar2,
               p_remote_trans_method  in  varchar2    default 'ASCII',
               x_ret_value            out varchar2,
               x_ret_status           out varchar2);


procedure put_ascii_file  (p_data_connection      in  utl_tcp.connection,
                           p_file                 in  utl_file.file_type,
                           x_remote_file_size     out number,
                           x_ret_value            out varchar2,
                           x_ret_status           out varchar2);


procedure get_ascii_file  (p_data_connection      in  utl_tcp.connection,
                           p_file                 in  utl_file.file_type,
                           x_remote_file_size     out number,
                           x_ret_value            out varchar2,
                           x_ret_status           out varchar2);

procedure put_binary_file (p_data_connection      in  utl_tcp.connection,
                           p_local_directory      in  varchar2,
                           p_local_filename       in  varchar2,
                           x_remote_file_size     out number,
                           x_ret_value            out varchar2,
                           x_ret_status           out varchar2) ;

procedure get_binary_file (p_data_connection      in  utl_tcp.connection,
                           p_local_file           in  utl_file.file_type,
                           x_remote_file_size     out number,
                           x_ret_value            out varchar2,
                           x_ret_status           out varchar2);

end FTP_PKG;
/

/*--- Package Body for FTP_PKG ---*/
create or replace package body FTP_PKG as


procedure ftp (p_put_get_cmd          in  varchar2,
               p_local_directory      in  varchar2,
               p_local_filename       in  varchar2,
               p_remote_server        in  varchar2,
               p_remote_port          in  pls_integer  default 21,
               p_remote_directory     in  varchar2,
               p_remote_filename      in  varchar2,
               p_remote_username      in  varchar2,
               p_remote_password      in  varchar2,
               p_remote_trans_method  in  varchar2     default 'ASCII',
               x_ret_value            out varchar2,
               x_ret_status           out varchar2) is


/*-- Define Required Variables --*/
l_control_connection    utl_tcp.connection;
l_data_connection       utl_tcp.connection;
l_file                  utl_file.file_type;
l_binary_data           blob;
l_block_size            binary_integer   := 0;
l_max_block_size        binary_integer   := 32767;
l_bytes                 number           := 0;
l_total_bytes           number           := 0;
l_local_file_size       number           := 0;
l_remote_file_size      number           := 0;
l_local_directory       varchar2(100)    := null;
l_file_data             varchar2(32767)  := null;
l_ftp_msg               varchar2(100)    := null;
l_error_message         varchar2(100)    := null;
l_trans_method          varchar2(100)    := null;
l_local_read_write_cmd  varchar2(1)      := null;
l_send_recieve_cmd      varchar2(4)      := null;
l_ret_value             varchar2(100)    := null;
l_ret_status            varchar2(100)    := null;
l_pasv_string           varchar2(100)    := null;
l_pasv_local            varchar2(100)    := null;
l_pasv_port_dec         number           := 0;
l_pasv_port_add         number           := 0;
l_pasv_port             pls_integer      := 0;
l_exists_flag           boolean          := FALSE;
ftp_error               exception;
db_directory_missing    exception;


begin


    /*-- Step 1. Verify Local Directory - a valid Database Directory must exist for the local directory --*/
    begin
        select directory_name
        into   l_local_directory
        from   all_directories
        where  directory_name = p_local_directory;
        exception when no_data_found then
           begin
               select directory_name
               into   l_local_directory
               from   all_directories
               where  directory_path = p_local_directory;
               exception when no_data_found then
                      raise db_directory_missing;
           end;
    end;



   /*-- Step 2. Set Variables dependent on put or get commands --*/
   if upper(p_put_get_cmd) = 'PUT' then
      l_send_recieve_cmd     := 'STOR';    --> FTP PUT
      l_local_read_write_cmd := 'r';       --> File READ
   else
      l_send_recieve_cmd     := 'RETR';    --> FTP GET
      l_local_read_write_cmd := 'w';       --> File WRITE
   end if;



   /*-- Step 3. Open "Control" Connection with FTP Server --*/
   l_control_connection := utl_tcp.open_connection (p_remote_server, 21, null, null, null, null, null, null, null);
   l_ftp_msg            := utl_tcp.get_line        (l_control_connection);

   if substr(l_ftp_msg,1,3) != '220' then
      l_error_message := 'Could not Open FTP Control Connection.';
      raise ftp_error;
   end if;



   /*-- Step 4. Send Username command to FTP Server --*/
   l_bytes   := utl_tcp.write_line (l_control_connection, 'USER '|| p_remote_username ||utl_tcp.crlf);
   l_ftp_msg := utl_tcp.get_line   (l_control_connection);

   if substr(l_ftp_msg,1,3) != '331' then
      l_error_message := 'FTP Username is invalid or not accepted.';
      raise ftp_error;
   end if;



   /*-- Step 5. Send Password command to FTP Server --*/
   l_bytes   := utl_tcp.write_line (l_control_connection, 'PASS '|| p_remote_password ||utl_tcp.crlf);
   l_ftp_msg := utl_tcp.get_line   (l_control_connection);

   if substr(l_ftp_msg,1,3) != '230' then
      l_error_message := 'FTP Password is invalid or not accepted.';
      raise ftp_error;
   end if;



   /*-- Step 6. Send Change Directory command to FTP Server --*/
   l_bytes   := utl_tcp.write_line (l_control_connection, 'CWD ' || p_remote_directory ||utl_tcp.crlf);
   l_ftp_msg := utl_tcp.get_line   (l_control_connection);

   if substr(l_ftp_msg,1,3) != '250' then
      l_error_message := 'Change Directory command failed or not accepted.';
      raise ftp_error;
   end if;



   /*-- Step 7. Send File Type command to FTP Server --*/
   if p_remote_trans_method = 'ASCII' then
      l_trans_method := 'TYPE A';  --> ASCII
   else
      l_trans_method := 'TYPE I';  --> BINARY
   end if;

   l_bytes   := utl_tcp.write_line (l_control_connection, l_trans_method ||utl_tcp.crlf);
   l_ftp_msg := utl_tcp.get_line   (l_control_connection);

   if substr(l_ftp_msg,1,3) != '200' then
      l_error_message := 'File Type command failed or not accepted.';
      raise ftp_error;
   end if;



   /*-- Step 8. Send Passive Mode command to FTP Server --*/
   l_bytes   := utl_tcp.write_line (l_control_connection, 'PASV'||utl_tcp.crlf);
   l_ftp_msg := utl_tcp.get_line   (l_control_connection);

   if substr(l_ftp_msg,1,3) != '227' then
      l_error_message := 'Passive Mode command failed or not accepted.';
      raise ftp_error;
   end if;



   /*-- Step 9. Based on Return FTP Message, determine Passive Connection Information --*/
   l_pasv_string   := substr(l_ftp_msg,instr(l_ftp_msg,'(',1,1)+1,instr(l_ftp_msg,')',1,1)-instr(l_ftp_msg,'(',1,1)-1);
   l_pasv_local    := replace(substr(l_pasv_string,1,instr(l_pasv_string,',',1,4)-1),',','.');
   l_pasv_port_dec := to_number(substr(l_pasv_string,instr(l_pasv_string,',',1,4)+1,(instr(l_pasv_string,',',1,5)-(instr(l_pasv_string,',',1,4)+1))));
   l_pasv_port_add := to_number(substr(l_pasv_string,instr(l_pasv_string,',',1,5)+1,length(l_pasv_string)-instr(l_pasv_string,',',1,5)));
   l_pasv_port     := (l_pasv_port_dec * 256) + l_pasv_port_add;



   /*-- Step 10. Open a "Passive" (Data) Connection --*/
   l_data_connection := utl_tcp.open_connection (l_pasv_local, l_pasv_port, null, null, null, null, null, null, null);



   /*-- Step 11. Send Command to SEND or RECIEVE data and the Filename to read or write --*/
   l_bytes   := utl_tcp.write_line (l_control_connection, l_send_recieve_cmd ||' '|| p_remote_filename ||utl_tcp.crlf);
   l_ftp_msg := utl_tcp.get_line   (l_control_connection);

   if substr(l_ftp_msg,1,3) != '150' then
      l_error_message := 'FTP '||l_send_recieve_cmd||' command failed or not accepted.';
      raise ftp_error;
   end if;



   /*-- Step 12. Open local File in Read or Write mode --*/
   l_file := utl_file.fopen(l_local_directory, p_local_filename, l_local_read_write_cmd, l_max_block_size);



   /*-- Step 13. Main FTP File Processing - Sending or Receiving the actual data --*/
   if upper(p_put_get_cmd) = 'PUT' then

      if p_remote_trans_method = 'ASCII' then
         put_ascii_file (l_data_connection, l_file, l_remote_file_size, l_ret_value, l_ret_status);
      else
         put_binary_file (l_data_connection, l_local_directory, p_local_filename, l_remote_file_size, l_ret_value, l_ret_status);
      end if;

   else

      if p_remote_trans_method = 'ASCII' then
         get_ascii_file (l_data_connection, l_file, l_remote_file_size, l_ret_value, l_ret_status);
      else
         get_binary_file (l_data_connection, l_file, l_remote_file_size, l_ret_value, l_ret_status);
      end if;

   end if;


   /*-- Raise FTP Exception in case of an Error in main put/get procedures --*/
   if l_ret_status != 'SUCCESS' then
      l_error_message := l_ret_value;
      raise ftp_error;
   end if;



   /*-- Step 14. Close Local File --*/
   utl_file.fclose(l_file);



   /*-- Step 15. Verify that the Local and Remote Byte Counts match exactly--*/
   utl_file.fgetattr(l_local_directory, p_local_filename, l_exists_flag, l_local_file_size, l_block_size);

   if l_remote_file_size != l_local_file_size then
      l_error_message := 'Local Byte Count ('||l_local_file_size||') and Remote Byte Count ('||l_remote_file_size||') dont match.';
      raise ftp_error;
   end if;



   /*-- Step 16. Close the "Passive" (data) Connection --*/
   utl_tcp.close_connection (l_data_connection);



   /*-- Step 17. Send QUIT command to FTP Server --*/
   l_bytes := utl_tcp.write_line (l_control_connection, 'QUIT'||utl_tcp.crlf);



   /*-- Step 18. Close the "Control" Connection --*/
   utl_tcp.close_connection (l_control_connection);



   x_ret_value  := 'File Transferred.';
   x_ret_status := 'SUCCESS';


exception when ftp_error then
             x_ret_value  := l_error_message;
             x_ret_status := 'ERROR';
             utl_file.fclose_all;
             utl_tcp.close_all_connections;

          when db_directory_missing then
             x_ret_value  := 'Invalid Local Directory for : '||p_local_directory;
             x_ret_status := 'ERROR';
             utl_file.fclose_all;
             utl_tcp.close_all_connections;

          when utl_file.invalid_operation then
             x_ret_value  := 'The file could not be opened or operated on as requested.';
             x_ret_status := 'ERROR';
             utl_file.fclose_all;
             utl_tcp.close_all_connections;


          when utl_file.invalid_path then
             x_ret_value  := 'File location or filename was invalid.';
             x_ret_status := 'ERROR';
             utl_file.fclose_all;
             utl_tcp.close_all_connections;


          when utl_file.invalid_mode then
             x_ret_value  := 'The open_mode parameter in FOPEN was invalid.';
             x_ret_status := 'ERROR';
             utl_file.fclose_all;
             utl_tcp.close_all_connections;


          when utl_file.invalid_filehandle then
             x_ret_value  := 'The file handle was invalid.';
             x_ret_status := 'ERROR';
             utl_file.fclose_all;
             utl_tcp.close_all_connections;


          when utl_file.read_error then
             x_ret_value  := 'An operating system error occurred during the read operation.';
             x_ret_status := 'ERROR';
             utl_file.fclose_all;
             utl_tcp.close_all_connections;


          when utl_file.write_error then
             x_ret_value  := 'An operating system error occurred during the write operation.';
             x_ret_status := 'ERROR';
             utl_file.fclose_all;
             utl_tcp.close_all_connections;


          when utl_file.internal_error then
             x_ret_value  := 'An unspecified error in PL/SQL.';
             x_ret_status := 'ERROR';
             utl_file.fclose_all;
             utl_tcp.close_all_connections;


          when others then
             x_ret_value  := 'Unknown Error ocurred in FTP_PKG.ftp';
             x_ret_status := 'ERROR';
             utl_file.fclose_all;
             utl_tcp.close_all_connections;
             dbms_output.put_line (SQLERRM);



end ftp;

/*--------------------------------------------------------------------------------*/

procedure put_ascii_file (p_data_connection  in  utl_tcp.connection,
                          p_file             in  utl_file.file_type,
                          x_remote_file_size out number,
                          x_ret_value        out varchar2,
                          x_ret_status       out varchar2) is


l_bytes            number             := 0;
l_total_bytes      number             := 0;
l_file_data        varchar2 (32767)   := null;
l_data_connection  utl_tcp.connection := p_data_connection;


begin

    loop
       begin
           utl_file.get_line(p_file, l_file_data);

       exception when no_data_found then
           exit;
       end;

       l_bytes       := utl_tcp.write_line(l_data_connection, l_file_data||chr(10));
       l_total_bytes := l_total_bytes + l_bytes;
    end loop;


x_remote_file_size := l_total_bytes;
x_ret_value        := null;
x_ret_status       := 'SUCCESS';


exception when others then
             x_remote_file_size := l_total_bytes;
             x_ret_value        := 'Unknown Error ocurred in FTP_PKG.put_ascii_file';
             x_ret_status       := 'ERROR';
             utl_tcp.flush (l_data_connection);
             dbms_output.put_line (SQLERRM);


end put_ascii_file;

/*--------------------------------------------------------------------------------*/

procedure get_ascii_file (p_data_connection  in  utl_tcp.connection,
                          p_file             in  utl_file.file_type,
                          x_remote_file_size out number,
                          x_ret_value        out varchar2,
                          x_ret_status       out varchar2) is


l_bytes            number             := 0;
l_total_bytes      number             := 0;
l_file_data        varchar2 (32767)   := null;
l_data_connection  utl_tcp.connection := p_data_connection;

begin

    loop
       begin
           l_file_data := utl_tcp.get_line(l_data_connection,true);

           if l_file_data is not null then
              utl_file.put_line(p_file, l_file_data);

              l_bytes       := length(l_file_data) + 1;
              l_total_bytes := l_total_bytes + l_bytes;
           end if;

        exception when utl_tcp.end_of_input then
           exit;
        end;
    end loop;


x_remote_file_size := l_total_bytes;
x_ret_value        := null;
x_ret_status       := 'SUCCESS';


exception when others then
             x_remote_file_size := l_total_bytes;
             x_ret_value        := 'Unknown Error ocurred in FTP_PKG.get_ascii_file';
             x_ret_status       := 'ERROR';
             utl_tcp.flush (l_data_connection);
             dbms_output.put_line (SQLERRM);


end get_ascii_file;

/*--------------------------------------------------------------------------------*/

procedure put_binary_file (p_data_connection  in  utl_tcp.connection,
                           p_local_directory  in  varchar2,
                           p_local_filename   in  varchar2,
                           x_remote_file_size out number,
                           x_ret_value        out varchar2,
                           x_ret_status       out varchar2) is


l_bfile              bfile;
l_binary_data        blob;
l_binary_raw         raw (32767);
l_bytes_written      pls_integer        := 0;
l_max_block_size     binary_integer     := 32767;
l_file_position      integer            := 1;
l_binary_data_len    integer            := 0;
l_data_connection    utl_tcp.connection := p_data_connection;


begin

    dbms_lob.createtemporary (l_binary_data, FALSE, 10);
    l_bfile := bfilename     (p_local_directory, p_local_filename);
    dbms_lob.fileopen        (l_bfile, dbms_lob.file_readonly);
    dbms_lob.loadfromfile    (l_binary_data, l_bfile, dbms_lob.getlength (l_bfile));
    dbms_lob.fileclose       (l_bfile);

    l_binary_data_len := dbms_lob.getlength (l_binary_data);

    while l_file_position < l_binary_data_len
       loop
          dbms_lob.read (l_binary_data, l_max_block_size, l_file_position, l_binary_raw);
          l_bytes_written := utl_tcp.write_raw (l_data_connection, l_binary_raw, l_max_block_size);
          utl_tcp.flush (l_data_connection);
          l_file_position := l_file_position + l_bytes_written;
       end loop;


x_remote_file_size := l_file_position-1;
x_ret_value        := null;
x_ret_status       := 'SUCCESS';


exception when others then
             x_remote_file_size := l_bytes_written;
             x_ret_value        := 'Unknown Error ocurred in FTP_PKG.put_binary_file';
             x_ret_status       := 'ERROR';
             utl_tcp.flush (l_data_connection);
             dbms_output.put_line (SQLERRM);


end put_binary_file;

/*--------------------------------------------------------------------------------*/

procedure get_binary_file (p_data_connection  in  utl_tcp.connection,
                           p_local_file       in  utl_file.file_type,
                           x_remote_file_size out number,
                           x_ret_value        out varchar2,
                           x_ret_status       out varchar2) is


l_binary_data      blob;
l_buffer           raw (32767);
l_binary_raw       raw (32767);
l_bytes_read       pls_integer;
l_max_block_size   binary_integer     := 32767;
l_file_position    integer            := 1;
l_binary_data_len  integer            := 0;
l_data_connection  utl_tcp.connection := p_data_connection;


begin

      dbms_lob.createtemporary (l_binary_data, FALSE, 10);

      loop
         begin
             l_bytes_read := utl_tcp.read_raw (l_data_connection, l_buffer, 32767);

             dbms_lob.writeappend (l_binary_data, l_bytes_read, l_buffer);

         exception when utl_tcp.end_of_input then
             exit;

         end;
      end loop;

      l_binary_data_len := dbms_lob.getlength (l_binary_data);

      while l_file_position < l_binary_data_len
         loop
            dbms_lob.read (l_binary_data, l_max_block_size, l_file_position, l_binary_raw);

            if l_binary_data is not null then
               utl_file.put_raw (p_local_file, l_binary_raw, TRUE);
            end if;

            l_file_position := l_file_position + l_max_block_size;
         end loop;


x_remote_file_size := l_binary_data_len + 1;
x_ret_value        := null;
x_ret_status       := 'SUCCESS';


exception when others then
             x_remote_file_size := l_binary_data_len;
             x_ret_value        := 'Unknown Error ocurred in FTP_PKG.get_binary_file';
             x_ret_status       := 'ERROR';
             utl_tcp.flush (l_data_connection);
             dbms_output.put_line (SQLERRM);


end get_binary_file;


end FTP_PKG;
/