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

        ftp.pks

        ftp.pkb

 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);      -- To transfer ASCII content  replace the ftp.binary with ftp.ascii
  ftp.put(p_conn      => l_conn,       -- to get a file just replace put with get  (also note the from_file and to_File etc ..)
          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;