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



 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.
  l_conn  UTL_TCP.connection;
  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');


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;


open c1;


     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;