Excellent FTP Client within Oracle PL/SQL   June 22, 2007

We needed to transfer image file from our  Infrastructure application server to our middle tier server through PL/SQL procedure.

The following is PL/SQL script doing just that

You will need to

1) load the following package  into your schema

     
http://www.oracle-base.com/dba/miscellaneous/ftp.pks
http://www.oracle-base.com/dba/miscellaneous/ftp.pkb
credit goes to Tim Hall [oracle-base.com]

 2) Create an ORACLE directory reference point to your local drive

     CREATE OR REPLACE DIRECTORY my_docs AS  '/app/infra/portal/mosd'

 3)  Then write the following PL/SQL block

     -- Send a binary file to a remote FTP server.
DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := ftp.login('mid.realsoft.com', '21', 'ammar', 'sajdi');
  ftp.binary(p_conn => l_conn);
  ftp.put(p_conn      => l_conn,
          p_from_dir  => 'MY_DOCS',
          p_from_file => 'filename.gif',
          p_to_file   => 'filename.gif');
  ftp.logout(l_conn);
  utl_tcp.close_all_connections;
END;

Our actually example is a bit more elaborate.

We have a record in the database for each image file.  because, we initially upload images to the database and store some database about the image including filename etc ..

For our example to work, we need to read the filename form the database and initiate an FTP put request. ie

Create or replace procedure put_images is

cursor c1 select * from image_Files where  sysdate < expireydate;

  REC1 c1%notfound;

  l_conn  UTL_TCP.connection;

begin

open c1;

loop

     fetch c1 into REC1;

    exit when C1%notfound;

   ftp.put(p_conn      => l_conn,
          p_from_dir  => 'MY_DOCS',
          p_from_file => 'rec1.filename',
          p_to_file   => 'rec1.filename');

 end loop;
 ftp.logout(l_conn);
 utl_tcp.close_all_connections;
end;

NOTE: if the remote server, you you want to transfer the files has an Oracle Instance running, then there is an easier way to accomplish this transfer using the DBMS_FILE_TRANSFER package that is demonstrated in previous tip