1 2 3 4 Previous Next 45 Replies Latest reply on Nov 23, 2009 6:16 PM by 736597

    If you need to FTP with PL/SQL...

    87539
      If you need to perform FTP from within PL/SQL and your database version has the UTL_TCP package, here is a free package you can use. The source code is hopefully documented well enough for you to tell what's going on and how to use the functions. Suggestions on improving the code are welcome, and I can provide limited support via email for what I've written, but I would encourage anyone who uses the code to modify/fix it according to their needs. If you modify the code, I respectfully request that you leave intact the authorship and note comments at the beginning of the package.

      Please note that I have not rigorously tested this code, but it has successfully transferred files in both directions in the limited tests that I have performed.

      -- Copy the code below and run it in your favorite SQL editor --

      CREATE OR REPLACE PACKAGE FTP IS
      /*******************************************************************************
      Simplified FTP client API using UTL_TCP package
      Author: Alan Wessman, Brigham Young University
      Note: This FTP client attempts to adhere to the protocol and advice found at:
      http://cr.yp.to/ftp.html
      No warranties are made regarding the correctness of this code.
      *******************************************************************************/

      /*------------------------------------------------------------------------------
      Notes:
      1. Most of these functions will raise UTL_TCP.NETWORK_ERROR if the connection
      is not open or is reset during the network transaction. They will also
      raise VALUE_ERROR if the server response is ill-formed or a buffer is
      too small to hold data. (Most buffers in this package are defined as
      VARCHAR2(32767) to avoid size limitations; reduce this if memory overhead
      is a concern.)
      2. "Verbose mode" can be enabled/disabled by changing the default value of
      the vDebug variable in the package body. Setting vDebug to TRUE will
      cause a session transcript to be output to DBMS_OUTPUT.
      3. The following is an example of how this package might be used:

      declare
      c utl_tcp.connection;
      vresp varchar2(32767);
      vbuf varchar2(32767);
      vresp_code number;
      vremote_host varchar2(32) := 'some.hostname.com';
      vusername varchar2(8) := 'username';
      vpassword varchar2(8) := 'password';
      begin
      dbms_output.put_line( 'Opening session...' );
      vresp_code := ftp.open_session( c,
      vremote_host,
      vusername,
      vpassword,
      vresp,
      5 );
      vresp_code := ftp.put( c,
      '/home/somebody',
      'local.test',
      'remote.test',
      vresp );
      vresp_code := ftp.remote_command( c, 'CHMOD 660 remote.test' );
      vresp_code := ftp.chdir( c, '/home/somebody/subdir' );
      vresp_code := ftp.pwd( c );
      vresp_code := ftp.get( c,
      '/home/somebody',
      'new_file.test',
      'another_remote_file.test',
      vresp );
      vresp_code := ftp.close_session( c );
      dbms_output.put_line( 'Closed session.' );
      exception
      when others then dbms_output.put_line( sqlcode || ':' || sqlerrm );
      end;

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

      /*------------------------------------------------------------------------------
      Function: Open_Session
      Description: Begins an FTP session with the remote server.
      Parameters:
      conn OUT parameter that contains the connection info; to be passed
      in to subsequent commands to maintain session state.
      host Name or IP address of remote server
      username User ID to use for login
      password Password to use for login
      response OUT parameter; buffer for server replies
      timeout_secs Number of seconds for TCP timeout. Pass in NULL to disable
      timeout (wait forever for responses). Pass in 0 (zero) for
      no wait.
      Return value: 0 (zero) if operation is successful; FTP error code if operation
      is not successful.
      Exceptions: May raise UTL_TCP.NETWORK_ERROR if host parameter is incorrect or if
      some other networking error occurs.
      May raise VALUE_ERROR if server response is ill-formed.
      ------------------------------------------------------------------------------*/
      FUNCTION Open_Session( conn OUT NOCOPY UTL_TCP.Connection,
      host IN VARCHAR2,
      username IN VARCHAR2,
      password IN VARCHAR2,
      response OUT VARCHAR2,
      timeout_secs IN NUMBER DEFAULT 60 ) RETURN NUMBER;

      /*------------------------------------------------------------------------------
      Function: Get
      Description: Retrieves a file on the remote server and stores its contents in
      a VARCHAR2 buffer.
      Parameters:
      conn IN OUT parameter that contains the connection info; to be
      passed in to subsequent commands to maintain session state.
      buf OUT parameter; buffer for retrieved file contents
      remote_path Pathname (including file name) indicating location of remote
      file to be retrieved
      response OUT parameter; buffer for server replies.
      Return value: 0 (zero) if operation is successful; FTP error code if operation
      is not successful.
      Exceptions: May raise UTL_TCP.NETWORK_ERROR if some networking error occurs.
      May raise VALUE_ERROR if server response is ill-formed or buf is
      too small for file contents.
      ------------------------------------------------------------------------------*/
      FUNCTION Get( conn IN OUT NOCOPY UTL_TCP.Connection,
      buf OUT VARCHAR2,
      remote_path IN VARCHAR2,
      response OUT VARCHAR2 ) RETURN NUMBER;

      /*------------------------------------------------------------------------------
      Function: Get
      Description: Retrieves a file on the remote server and stores its contents in
      a local file. Assumes an open file handle and does not close it.
      Parameters:
      conn IN OUT parameter that contains the connection info; to be
      passed in to subsequent commands to maintain session state.
      local_file IN OUT parameter; UTL_FILE file handle for input file. File
      is assumed to be open for writing.
      remote_path Pathname (including file name) indicating location of remote
      file to be retrieved
      response OUT parameter; buffer for server replies.
      Return value: 0 (zero) if operation is successful; FTP error code if operation
      is not successful.
      Exceptions: May raise UTL_TCP.NETWORK_ERROR if some networking error occurs.
      May raise VALUE_ERROR if server response is ill-formed or buf is
      too small for file contents.
      May raise any of the UTL_FILE exceptions if file write operations
      fail. See UTL_FILE documentation for additional details.
      ------------------------------------------------------------------------------*/
      FUNCTION Get( conn IN OUT NOCOPY UTL_TCP.Connection,
      local_file IN OUT UTL_FILE.File_Type,
      remote_path IN VARCHAR2,
      response OUT VARCHAR2 ) RETURN NUMBER;

      /*------------------------------------------------------------------------------
      Function: Get
      Description: Retrieves a file on the remote server and stores its contents in
      a local file. Opens and closes local file automatically.
      Parameters:
      conn IN OUT parameter that contains the connection info; to be
      passed in to subsequent commands to maintain session state.
      local_path Pathname of local directory in which to store the retrieved
      file's contents
      local_filename Name of local file in which to store retrieved file's contents
      (creates new file or overwrites existing file)
      remote_path Pathname (including file name) indicating location of remote
      file to be retrieved
      response OUT parameter; buffer for server replies.
      Return value: 0 (zero) if operation is successful; FTP error code if operation
      is not successful.
      Exceptions: May raise UTL_TCP.NETWORK_ERROR if some networking error occurs.
      May raise VALUE_ERROR if server response is ill-formed or buf is
      too small for file contents.
      May raise any of the UTL_FILE exceptions if file open, write, or
      close operations fail. See UTL_FILE documentation for additional
      details.
      ------------------------------------------------------------------------------*/
      FUNCTION Get( conn IN OUT NOCOPY UTL_TCP.Connection,
      local_path IN VARCHAR2,
      local_filename IN VARCHAR2,
      remote_path IN VARCHAR2,
      response OUT VARCHAR2 ) RETURN NUMBER;

      /*------------------------------------------------------------------------------
      Function: Put
      Description: Stores data as a file on the remote server
      Parameters:
      conn IN OUT parameter that contains the connection info; to be
      passed in to subsequent commands to maintain session state.
      buf IN parameter; contains data to upload
      remote_path Pathname (including file name) indicating location of remote
      file to be created/overwritten
      response OUT parameter; buffer for server replies.
      Return value: 0 (zero) if operation is successful; FTP error code if operation
      is not successful.
      Exceptions: May raise UTL_TCP.NETWORK_ERROR if some networking error occurs.
      May raise VALUE_ERROR if server response is ill-formed.
      ------------------------------------------------------------------------------*/
      FUNCTION Put( conn IN OUT NOCOPY UTL_TCP.Connection,
      buf IN VARCHAR2,
      remote_path IN VARCHAR2,
      response OUT VARCHAR2 ) RETURN NUMBER;

      /*------------------------------------------------------------------------------
      Function: Put
      Description: Uploads a local file to the remote server. Assumes an open file
      handle and does not close it.
      Parameters:
      conn IN OUT parameter that contains the connection info; to be
      passed in to subsequent commands to maintain session state.
      local_file IN OUT parameter; UTL_FILE file handle for input file. File
      is assumed to be open for reading.
      remote_path Pathname (including file name) indicating location of remote
      file to be created/overwritten.
      response OUT parameter; buffer for server replies.
      Return value: 0 (zero) if operation is successful; FTP error code if operation
      is not successful.
      Exceptions: May raise UTL_TCP.NETWORK_ERROR if some networking error occurs.
      May raise VALUE_ERROR if server response is ill-formed.
      May raise any of the UTL_FILE exceptions if file read operations
      fail. See UTL_FILE documentation for additional details.
      ------------------------------------------------------------------------------*/
      FUNCTION Put( conn IN OUT NOCOPY UTL_TCP.Connection,
      local_file IN OUT UTL_FILE.File_Type,
      remote_path IN VARCHAR2,
      response OUT VARCHAR2 ) RETURN NUMBER;

      /*------------------------------------------------------------------------------
      Function: Put
      Description: Uploads a local file to the remote server. Opens and closes local
      file automatically.
      Parameters:
      conn IN OUT parameter that contains the connection info; to be
      passed in to subsequent commands to maintain session state.
      local_path Pathname of local directory in which file to upload exists.
      local_filename Name of local file to upload.
      remote_path Pathname (including file name) indicating location of remote
      file to be created/overwritten.
      response OUT parameter; buffer for server replies.
      Return value: 0 (zero) if operation is successful; FTP error code if operation
      is not successful.
      Exceptions: May raise UTL_TCP.NETWORK_ERROR if some networking error occurs.
      May raise VALUE_ERROR if server response is ill-formed.
      May raise any of the UTL_FILE exceptions if file open, read, or
      close operations fail. See UTL_FILE documentation for additional
      details.
      ------------------------------------------------------------------------------*/
      FUNCTION Put( conn IN OUT NOCOPY UTL_TCP.Connection,
      local_path IN VARCHAR2,
      local_filename IN VARCHAR2,
      remote_path IN VARCHAR2,
      response OUT VARCHAR2 ) RETURN NUMBER;

      /*------------------------------------------------------------------------------
      Function: Remote_Command
      Description: Sends an arbitrary command to the server via the SITE command.
      Parameters:
      conn IN OUT parameter that contains the connection info; to be
      passed in to subsequent commands to maintain session state.
      command Command and parameter(s) to send to the server, e.g.
      'CHMOD 600 foo.txt'
      Return value: 0 (zero) if operation is successful; FTP error code if operation
      is not successful.
      Exceptions: May raise UTL_TCP.NETWORK_ERROR if some networking error occurs.
      May raise VALUE_ERROR if server response is ill-formed.
      ------------------------------------------------------------------------------*/
      FUNCTION Remote_Command( conn IN OUT NOCOPY UTL_TCP.Connection,
      command IN VARCHAR2 ) RETURN NUMBER;

      /*------------------------------------------------------------------------------
      Function: Chdir
      Description: Changes current working directory on remote server to specified
      path.
      Parameters:
      conn IN OUT parameter that contains the connection info; to be
      passed in to subsequent commands to maintain session state.
      remote_path Path on remote server to change to.
      Return value: 0 (zero) if operation is successful; FTP error code if operation
      is not successful.
      Exceptions: May raise UTL_TCP.NETWORK_ERROR if some networking error occurs.
      May raise VALUE_ERROR if server response is ill-formed.
      ------------------------------------------------------------------------------*/
      FUNCTION Chdir( conn IN OUT NOCOPY UTL_TCP.Connection,
      remote_path IN VARCHAR2 ) RETURN NUMBER;

      /*------------------------------------------------------------------------------
      Function: Pwd
      Description: Prints current working directory (on remote server) to debugging
      output if debugging is turned on.
      Parameters:
      conn IN OUT parameter that contains the connection info; to be
      passed in to subsequent commands to maintain session state.
      Return value: 0 (zero) if operation is successful; FTP error code if operation
      is not successful.
      Exceptions: May raise UTL_TCP.NETWORK_ERROR if some networking error occurs.
      May raise VALUE_ERROR if server response is ill-formed.
      ------------------------------------------------------------------------------*/
      FUNCTION Pwd( conn IN OUT NOCOPY UTL_TCP.Connection ) RETURN NUMBER;

      /*------------------------------------------------------------------------------
      Function: Close_Session
      Description: Closes the TCP connection to the remote server.
      Parameters:
      conn IN OUT parameter that contains the connection info; to be
      passed in to subsequent commands to maintain session state.
      Return value: 0 (zero)
      Exceptions: None raised.
      ------------------------------------------------------------------------------*/
      FUNCTION Close_Session( conn IN OUT NOCOPY UTL_TCP.Connection ) RETURN NUMBER;

      /*------------------------------------------------------------------------------
      Function: Close_All_Sessions
      Description: Closes all currently open TCP connections.
      Parameters: None.
      Return value: 0 (zero)
      Exceptions: None raised.
      ------------------------------------------------------------------------------*/
      FUNCTION Close_All_Sessions RETURN NUMBER;

      END FTP;
      /
      CREATE OR REPLACE PACKAGE BODY FTP IS

      vDebug BOOLEAN := TRUE;

      FATAL_ERROR EXCEPTION;

      PROCEDURE Debug( msg IN VARCHAR2 ) IS
      BEGIN
      IF vDebug THEN
      DBMS_OUTPUT.Put_Line( msg );
      END IF;
      END Debug;

      FUNCTION Get_Response( conn IN OUT NOCOPY UTL_TCP.Connection,
      buf IN OUT VARCHAR2 ) RETURN NUMBER IS
      vLen NUMBER;
      vCode NUMBER;
      vResp VARCHAR2(32767);
      BEGIN
      vLen := UTL_TCP.READ_LINE( conn, vResp );
      Debug( vResp );
      -- If TO_NUMBER below fails, let the exception propagate to calling proc
      vCode := TO_NUMBER( SUBSTR( vResp, 1, 3 ) );
      vResp := SUBSTR( vResp, 4 );
      buf := buf || SUBSTR( vResp, 2 );
      IF SUBSTR( vResp, 1, 1 ) = '-' THEN
      LOOP
      vLen := UTL_TCP.READ_LINE( conn, vResp );
      Debug( vResp );
      <<Get_Code>>
      BEGIN
      vCode := TO_NUMBER( SUBSTR( vResp, 1, 3 ) );
      vResp := SUBSTR( vResp, 4 );
      IF SUBSTR( vResp, 1, 1 ) = ' ' THEN
      buf := buf || SUBSTR( vResp, 2 );
      EXIT;
      END IF;
      EXCEPTION WHEN VALUE_ERROR THEN NULL;
      END Get_Code;
      buf := buf || vResp;
      END LOOP;
      END IF;
      RETURN vCode;
      END Get_Response;

      FUNCTION Do_Command( conn IN OUT NOCOPY UTL_TCP.Connection,
      cmd IN VARCHAR2,
      response OUT VARCHAR2 ) RETURN NUMBER IS
      vResult NUMBER := 0;
      BEGIN
      vResult := UTL_TCP.WRITE_LINE( conn, cmd );
      vResult := Get_Response( conn, response );
      RETURN vResult;
      END Do_Command;

      FUNCTION Parse_Port_Number( port_string IN VARCHAR2 ) RETURN NUMBER IS
      vResult NUMBER;
      vNew_Port_String VARCHAR2(32767);
      BEGIN
      -- This stuff parses out the port number encoding from the server reply
      -- Reply is in the format xyzh1,h2,h3,h4,p1,p2xyz
      -- xyz = possible character data (server-dependent, may not exist)
      -- h1-h4 = server IP elements; ignore since we know the host already
      -- p1,p2 = port number encoding (port number = p1 * 256 + p2 )
      vNew_Port_String := TRANSLATE( port_string, '0123456789', '0000000000' );
      vNew_Port_String := SUBSTR( port_string,
      INSTR( vNew_Port_String, '0' ),
      INSTR( vNew_Port_String, '0', -1 ) -
      INSTR( vNew_Port_String, '0' ) + 1 );
      vNew_Port_String := SUBSTR( vNew_Port_String,
      INSTR( vNew_Port_String, ',', 1, 4 ) + 1 );
      vResult := 256 * TO_NUMBER( SUBSTR( vNew_Port_String,
      1,
      INSTR( vNew_Port_String, ',' ) - 1 ) );
      vResult := vResult + TO_NUMBER( SUBSTR( vNew_Port_String,
      INSTR( vNew_Port_String, ',' ) + 1 ) );
      RETURN vResult;
      -- Allow VALUE_ERROR to propagate
      END Parse_Port_Number;

      FUNCTION Open_Session( conn OUT NOCOPY UTL_TCP.Connection,
      host IN VARCHAR2,
      username IN VARCHAR2,
      password IN VARCHAR2,
      response OUT VARCHAR2,
      timeout_secs IN NUMBER DEFAULT 60 ) RETURN NUMBER IS
      vResp_Code NUMBER;
      vGarbage NUMBER; -- For calling functions when we don't care about return val
      BEGIN
      conn := UTL_TCP.OPEN_CONNECTION( host,
      21,
      tx_timeout => timeout_secs );
      vResp_Code := Get_Response( conn, response );
      IF vResp_Code = 220 THEN
      vResp_Code := Do_Command( conn, 'USER ' || username, response );
      IF vResp_Code IN ( 331, 332 ) THEN
      vResp_Code := Do_Command( conn, 'PASS ' || password, response );
      IF vResp_Code NOT IN ( 202, 230 ) THEN
      RAISE FATAL_ERROR;
      END IF;
      ELSE
      RAISE FATAL_ERROR;
      END IF;
      END IF;
      vResp_Code := Do_Command( conn, 'TYPE I', response );
      Debug( 'Logged into ' || conn.remote_host || ' at port ' || conn.remote_port );
      RETURN 0;
      EXCEPTION
      WHEN FATAL_ERROR THEN
      Debug( 'Fatal error opening session:' );
      Debug( ' Code: ' || vResp_Code );
      Debug( ' Response: ' || response );
      vGarbage := Close_Session( conn );
      RETURN vResp_Code;
      END Open_Session;

      FUNCTION Get( conn IN OUT NOCOPY UTL_TCP.Connection,
      buf OUT VARCHAR2,
      remote_path IN VARCHAR2,
      response OUT VARCHAR2 ) RETURN NUMBER IS
      vResp VARCHAR2(32767);
      vResp_Code NUMBER;
      vNew_Conn UTL_TCP.Connection;
      vNew_Port NUMBER;
      BEGIN
      -- do PASV
      vResp_Code := Do_Command( conn, 'PASV', response );
      IF vResp_Code = 227 THEN
      <<Switch_Port>>
      BEGIN
      vNew_Port := Parse_Port_Number( response );

      vNew_Conn := UTL_TCP.OPEN_CONNECTION( conn.remote_host,
      vNew_Port,
      tx_timeout => conn.tx_timeout );
      Debug( 'Data connection: ' || vNew_Conn.remote_host || ':' || vNew_Conn.remote_port );
      vResp_Code := Do_Command( conn, 'RETR ' || REPLACE( remote_path, CHR(12), CHR(0) ), response );
      IF vResp_Code <> 150 THEN
      RAISE FATAL_ERROR;
      END IF;
      <<Get_Download>>
      BEGIN
      LOOP
      vResp := vResp || UTL_TCP.GET_LINE( vNew_Conn, FALSE );
      END LOOP;
      EXCEPTION
      WHEN UTL_TCP.END_OF_INPUT THEN NULL;
      END Get_Download;
      vResp_Code := Close_Session( vNew_Conn );
      vResp_Code := Get_Response( conn, response );
      IF vResp_Code BETWEEN 400 AND 599 THEN
      RAISE FATAL_ERROR;
      END IF;
      EXCEPTION
      WHEN OTHERS THEN
      Debug( SQLERRM );
      RAISE FATAL_ERROR;
      END Switch_Port;
      ELSE
      RAISE FATAL_ERROR;
      END IF;
      vResp_Code := Close_Session( vNew_Conn );
      buf := vResp;
      RETURN 0;
      EXCEPTION
      WHEN FATAL_ERROR THEN
      Debug( 'Fatal error getting ' || remote_path || ':' );
      Debug( ' Code: ' || vResp_Code );
      Debug( ' Response: ' || response );
      vResp_Code := Close_Session( vNew_Conn );
      RETURN vResp_Code;
      WHEN OTHERS THEN
      Debug( vResp_Code || ': ' || SQLERRM );
      RETURN vResp_Code;
      END Get;

      FUNCTION Get( conn IN OUT NOCOPY UTL_TCP.Connection,
      local_file IN OUT UTL_FILE.File_Type,
      remote_path IN VARCHAR2,
      response OUT VARCHAR2 ) RETURN NUMBER IS
      vResp VARCHAR2(32767);
      vResp_Code NUMBER := -1;
      vNew_Conn UTL_TCP.Connection;
      vNew_Port NUMBER;
      BEGIN
      -- do PASV
      vResp_Code := Do_Command( conn, 'PASV', response );
      IF vResp_Code = 227 THEN
      <<Switch_Port>>
      BEGIN
      vNew_Port := Parse_Port_Number( response );

      vNew_Conn := UTL_TCP.OPEN_CONNECTION( conn.remote_host,
      vNew_Port,
      tx_timeout => conn.tx_timeout );
      Debug( 'Data connection: ' || vNew_Conn.remote_host || ':' || vNew_Conn.remote_port );
      vResp_Code := Do_Command( conn, 'RETR ' || REPLACE( remote_path, CHR(12), CHR(0) ), response );
      IF vResp_Code <> 150 THEN
      RAISE FATAL_ERROR;
      END IF;
      <<Get_Download>>
      BEGIN
      LOOP
      vResp := UTL_TCP.GET_LINE( vNew_Conn, FALSE );
      UTL_FILE.Put( local_file, vResp );
      END LOOP;
      EXCEPTION
      WHEN UTL_TCP.END_OF_INPUT THEN NULL;
      END Get_Download;
      vResp_Code := Close_Session( vNew_Conn );
      vResp_Code := Get_Response( conn, response );
      IF vResp_Code BETWEEN 400 AND 599 THEN
      RAISE FATAL_ERROR;
      END IF;
      EXCEPTION
      WHEN OTHERS THEN
      Debug( SQLERRM );
      RAISE FATAL_ERROR;
      END Switch_Port;
      ELSE
      RAISE FATAL_ERROR;
      END IF;
      vResp_Code := Close_Session( vNew_Conn );
      RETURN 0;
      EXCEPTION
      WHEN FATAL_ERROR THEN
      Debug( 'Fatal error getting ' || remote_path || ':' );
      Debug( ' Code: ' || vResp_Code );
      Debug( ' Response: ' || response );
      vResp_Code := Close_Session( vNew_Conn );
      RETURN vResp_Code;
      WHEN OTHERS THEN
      Debug( vResp_Code || ': ' || SQLERRM );
      RETURN vResp_Code;
      END Get;

      FUNCTION Get( conn IN OUT NOCOPY UTL_TCP.Connection,
      local_path IN VARCHAR2,
      local_filename IN VARCHAR2,
      remote_path IN VARCHAR2,
      response OUT VARCHAR2 ) RETURN NUMBER IS
      vFile UTL_FILE.File_Type;
      vResult NUMBER := -1;
      BEGIN
      vFile := UTL_FILE.FOPEN( local_path, local_filename, 'w' );
      vResult := Get( conn, vFile, remote_path, response );
      UTL_FILE.FCLOSE( vFile );
      RETURN vResult;
      EXCEPTION WHEN OTHERS THEN
      IF UTL_FILE.IS_OPEN( vFile ) THEN
      UTL_FILE.FCLOSE( vFile );
      END IF;
      RAISE;
      END Get;

      FUNCTION Put( conn IN OUT NOCOPY UTL_TCP.Connection,
      buf IN VARCHAR2,
      remote_path IN VARCHAR2,
      response OUT VARCHAR2 ) RETURN NUMBER IS
      vResp VARCHAR2(32767);
      vResp_Code NUMBER;
      vNew_Conn UTL_TCP.Connection;
      vNew_Port NUMBER;
      BEGIN
      -- do PASV
      vResp_Code := Do_Command( conn, 'PASV', response );
      IF vResp_Code = 227 THEN
      <<Switch_Port>>
      BEGIN
      vNew_Port := Parse_Port_Number( response );
      vNew_Conn := UTL_TCP.OPEN_CONNECTION( conn.remote_host,
      vNew_Port,
      tx_timeout => conn.tx_timeout );
      Debug( 'Data connection: ' || vNew_Conn.remote_host || ':' || vNew_Conn.remote_port );
      vResp_Code := Do_Command( conn, 'STOR ' || REPLACE( remote_path, CHR(12), CHR(0) ), response );
      IF vResp_Code <> 150 THEN
      RAISE FATAL_ERROR;
      END IF;
      vResp_Code := UTL_TCP.WRITE_TEXT( vNew_Conn, buf );
      UTL_TCP.FLUSH( vNew_Conn );
      vResp_Code := Close_Session( vNew_Conn );
      vResp_Code := Get_Response( conn, response );
      IF vResp_Code BETWEEN 400 AND 599 THEN
      RAISE FATAL_ERROR;
      END IF;
      EXCEPTION
      WHEN OTHERS THEN
      Debug( SQLERRM );
      RAISE FATAL_ERROR;
      END Switch_Port;
      ELSE
      RAISE FATAL_ERROR;
      END IF;
      vResp_Code := Close_Session( vNew_Conn );
      response := vResp;
      RETURN 0;
      EXCEPTION
      WHEN FATAL_ERROR THEN
      Debug( 'Fatal error putting ' || remote_path || ':' );
      Debug( ' Code: ' || vResp_Code );
      Debug( ' Response: ' || response );
      vResp_Code := Close_Session( vNew_Conn );
      RETURN vResp_Code;
      WHEN OTHERS THEN
      Debug( vResp_Code || ': ' || SQLERRM );
      RETURN vResp_Code;
      END Put;

      FUNCTION Put( conn IN OUT NOCOPY UTL_TCP.Connection,
      local_file IN OUT UTL_FILE.File_Type,
      remote_path IN VARCHAR2,
      response OUT VARCHAR2 ) RETURN NUMBER IS
      vResp VARCHAR2(32767);
      vResp_Code NUMBER;
      vNew_Conn UTL_TCP.Connection;
      vNew_Port NUMBER;
      vNew_Port_String VARCHAR2(32767);
      BEGIN
      -- do PASV
      vResp_Code := Do_Command( conn, 'PASV', response );
      IF vResp_Code = 227 THEN
      <<Switch_Port>>
      BEGIN
      vNew_Port := Parse_Port_Number( response );

      vNew_Conn := UTL_TCP.OPEN_CONNECTION( conn.remote_host,
      vNew_Port,
      tx_timeout => conn.tx_timeout );
      Debug( 'Data connection: ' || vNew_Conn.remote_host || ':' || vNew_Conn.remote_port );
      vResp_Code := Do_Command( conn, 'STOR ' || REPLACE( remote_path, CHR(12), CHR(0) ), response );
      IF vResp_Code <> 150 THEN
      RAISE FATAL_ERROR;
      END IF;
      <<Get_Download>>
      BEGIN
      LOOP
      UTL_FILE.Get_Line( local_file, vResp );
      vResp_Code := UTL_TCP.WRITE_LINE( vNew_Conn, vResp );
      END LOOP;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN NULL;
      END Get_Download;
      vResp_Code := Close_Session( vNew_Conn );
      vResp_Code := Get_Response( conn, response );
      IF vResp_Code BETWEEN 400 AND 599 THEN
      RAISE FATAL_ERROR;
      END IF;
      EXCEPTION
      WHEN OTHERS THEN
      Debug( SQLERRM );
      RAISE FATAL_ERROR;
      END Switch_Port;
      ELSE
      RAISE FATAL_ERROR;
      END IF;
      vResp_Code := Close_Session( vNew_Conn );
      RETURN 0;
      EXCEPTION
      WHEN FATAL_ERROR THEN
      Debug( 'Fatal error putting ' || remote_path || ':' );
      Debug( ' Code: ' || vResp_Code );
      Debug( ' Response: ' || response );
      vResp_Code := Close_Session( vNew_Conn );
      RETURN vResp_Code;
      WHEN OTHERS THEN
      Debug( vResp_Code || ': ' || SQLERRM );
      RETURN vResp_Code;
      END Put;

      FUNCTION Put( conn IN OUT NOCOPY UTL_TCP.Connection,
      local_path IN VARCHAR2,
      local_filename IN VARCHAR2,
      remote_path IN VARCHAR2,
      response OUT VARCHAR2 ) RETURN NUMBER IS
      vFile UTL_FILE.File_Type;
      vResult NUMBER;
      BEGIN
      vFile := UTL_FILE.FOPEN( local_path, local_filename, 'r' );
      vResult := Put( conn, vFile, remote_path, response );
      UTL_FILE.FCLOSE( vFile );
      RETURN vResult;
      EXCEPTION WHEN OTHERS THEN
      IF UTL_FILE.IS_OPEN( vFile ) THEN
      UTL_FILE.FCLOSE( vFile );
      END IF;
      RAISE;
      END Put;

      FUNCTION Remote_Command( conn IN OUT NOCOPY UTL_TCP.Connection,
      command IN VARCHAR2 ) RETURN NUMBER IS
      vResp_Code NUMBER;
      vResponse VARCHAR2(32767);
      BEGIN
      vResp_Code := Do_Command( conn, 'SITE ' || command, vResponse );
      IF vResp_Code BETWEEN 500 AND 599 THEN
      RETURN vResp_Code;
      END IF;
      RETURN 0;
      END Remote_Command;

      FUNCTION Chdir( conn IN OUT NOCOPY UTL_TCP.Connection,
      remote_path IN VARCHAR2 ) RETURN NUMBER IS
      vResp_Code NUMBER;
      vResponse VARCHAR2(32767);
      BEGIN
      vResp_Code := Do_Command( conn, 'CWD ' || REPLACE( remote_path, CHR(12), CHR(0) ), vResponse );
      IF vResp_Code BETWEEN 500 AND 599 THEN
      RETURN vResp_Code;
      END IF;
      RETURN 0;
      END Chdir;

      FUNCTION Pwd( conn IN OUT NOCOPY UTL_TCP.Connection ) RETURN NUMBER IS
      vResp_Code NUMBER;
      vResponse VARCHAR2(32767);
      BEGIN
      vResp_Code := Do_Command( conn, 'PWD', vResponse );
      IF vResp_Code BETWEEN 500 AND 599 THEN
      RETURN vResp_Code;
      END IF;
      RETURN 0;
      END Pwd;

      FUNCTION Close_Session( conn IN OUT NOCOPY UTL_TCP.Connection ) RETURN NUMBER IS
      BEGIN
      IF conn.remote_host IS NULL THEN
      RETURN 0;
      END IF;

      Debug( 'Closing connection on ' || conn.remote_host || ':' || conn.remote_port );

      UTL_TCP.Close_Connection( conn );
      RETURN 0;
      EXCEPTION
      WHEN UTL_TCP.NETWORK_ERROR THEN RETURN 0;
      END Close_Session;

      FUNCTION Close_All_Sessions RETURN NUMBER IS
      BEGIN
      UTL_TCP.Close_All_Connections;
      RETURN 0;
      END Close_All_Sessions;

      END FTP;
      /
        • 1. re:If you need to FTP with PL/SQL...
          87539
          It looks like the code didn't all come through. If you email me I will send you the full, correct, formatted version.
          • 2. re:If you need to FTP with PL/SQL...
            53886
            It looks like the code didn't all come through. If you email me I will send you the full, correct, formatted version.
            • 3. re:If you need to FTP with PL/SQL...
              6280
              Could you please send it to sukumarc@yahoo.com
              Thanks.
              • 4. re:If you need to FTP with PL/SQL...
                354622
                Could you send this code to me. My emaiID :
                Nirav_N_Patel@dell.com

                Thanks,
                Nirav
                • 5. Re: re:If you need to FTP with PL/SQL...
                  18016
                  IF you want send this code to my e-mail address too.

                  massimo.galavotti@formula.it.

                  Thanks.

                  Have you a Happy New Year.
                  • 6. Re: re:If you need to FTP with PL/SQL...
                    21978
                    Here's another PL/SQL package that will FTP ASCII text files. It assumes that you have proper permissions on the remote host and simply want to transfer one or more text files, not perform any other miscellaneous commands.

                    Also, from what I have read, in 9i UTL_FILE supports reading and writing of binary data so an FTP client could be written to transfer either ASCII or BINARY files.

                    Regards,
                    Russ

                    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;
                    /
                    • 7. Re: re:If you need to FTP with PL/SQL...
                      108458
                      Hi Alan,

                      Could you please email me the full code? Thanks

                      luozhihong@hotmail.com
                      • 8. Re: re:If you need to FTP with PL/SQL...
                        34946
                        Alan,
                        This approach works, with the following exception:

                        During an ftp get, you read until utl_tcp.bytes_available is 0, but this results in an empty file sometimes because there were no bytes available without a blocking call.

                        The alternate package here uses utl_tcp.get_line until an end of input exception is raised. Some tcp connections wont ever get to an end of input..so this is also problematic in some cases.

                        The server response with the 150 will explain how many bytes are being sent '150 Opening BINARY mode data connection for FILENAME (185 bytes).

                        This can be parsed to see how many bytes are being transferred (similar to checking the content_length: response in an HTTP Post session), allowing you to tell whether the current receive buffer has reached the correct size.

                        What is the best way to go here???

                        By the way, do you have any ideas about how to create a plsql command line telnet client similar to this ftp client? I think the only trick is figuring out the initial negotiation...
                        • 9. Re: re:If you need to FTP with PL/SQL...
                          389746
                          Could you send this code to me, I you need really to it.
                          Thanks on advance.
                          Gilbert
                          Adami.Gilberto@Katamail.com
                          • 10. Re: re:If you need to FTP with PL/SQL...
                            389746
                            Could you send this code to me, I you need really to it.
                            Thanks on advance.
                            Gilbert
                            Adami.Gilberto@Katamail.com
                            • 11. Re: re:If you need to FTP with PL/SQL...
                              389746
                              Could you send this code to me, I you need really to it.
                              Thanks on advance.
                              Gilbert
                              Adami.Gilberto@Katamail.com
                              • 12. Re: re:If you need to FTP with PL/SQL...
                                389746
                                Could you send this code to me, I you need really to it.
                                Thanks on advance.
                                Gilbert
                                Adami.Gilberto@Katamail.com
                                • 13. Re: If you need to FTP with PL/SQL...
                                  333528
                                  I cannot seem to get this code of yours to work correctly....
                                  I seem to be getting 'end-of-input reached' error when I try to PUT a file.
                                  Any ideas?
                                  • 14. Re: If you need to FTP with PL/SQL...
                                    333528
                                    I think that problem might be due to not logging in correctly.
                                    I am getting a 'code 500: command not recognized or not implemented' error when I send the password.
                                    I believe that PASS is not the correct keyword.
                                    How can I find out what the correct keyword is? (if this is the problem?)
                                    1 2 3 4 Previous Next