Delete file on FTP site with Oracle

[Cantor]

Hi,

we need to delete a file who is on a ftp site. Is there any PL/SQL commands to do that? Does the package UTL_TCP can delete a file? I saw functions like "put" and "get" but no "delete".

Thanks for your help,

Cantor

 

[SantaMufasa]

Cantor,

I presume that you want the "file delete" activity to occur from a machine that is not the "ftp" server itself, correct?

If, from a remote (non-ftp) machine, you were attempting to remove the file off of the ftp machine, what command syntax would you be using? Please give an example of what works for you outside of Oracle.

Also, what tool do you plan to use at the point where you are attempting to remove the "ftp" file...SQL*Plus, Oracle*Forms, et cetera?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 17:22 (03Feb05) UTC (aka "GMT" and "Zulu"),
@ 10:22 (03Feb05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[Cantor]

Sorry to be so unclear.

Nothing has been done yet. We are at the "is it possible?" stage.

The plan is to execute a DB package who will delete a zip file from a ftp server. So the "tool" would be a PL/SQL procedure store in this package.

I hope this will help us to help me,

Thanks,

Cantor

 

[SantaMufasa]

Cantor,

I can think of a way to cause the .zip file to become "empty" from PL/SQL, but I am not aware of a PL/SQL method/procedure to "rm" a file from PL/SQL.

If you are running a script from SQL*Plus, there is a method to "rm" a file that is visible from the command prompt.

Let us know if either of these "directions" is useful to you. If not, then we'll need to wait from another Tek-Tips contributor that is more clever than I am.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 19:11 (03Feb05) UTC (aka "GMT" and "Zulu"),
@ 12:11 (03Feb05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[Cantor]

The second direction seems interesting.

If you are running a script from SQL*Plus, there is a method to "rm" a file that is visible from the command prompt.

How can you do that? Let's say we have toto.zip to delete form this ftp server :

Host name : ftp.sitename.com
directory : /data/zipfile

Are you saying it's possible to "see" toto.zip with SQL*Plus, and then remove it?

Cantor

 

[SantaMufasa]

Cantor,

Not necessarily. From the SQL*Plus prompt, you can "shell out" to whatever server you are on by using SQL*Plus's "host" command. For example, from SQL*Plus, I can erase a file, "temp.sql" from the current (Windows) directory with the following command:

SQL> host erase temp.sql

So, it is rather low-tech, but it accomplishes a file erase just fine.

Now, in your case, if you can show me a single command line that you could issue from your command line that would erase the file on your ftp site, then I could probably show you how to do the same thing from SQL*Plus. Do you know of such a command?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 21:11 (03Feb05) UTC (aka "GMT" and "Zulu"),
@ 14:11 (03Feb05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[sem]

utl_tcp may help if you're familiar with ftp protocol itself. Just as an example:

CREATE or replace PROCEDURE delFile(pServer   IN VARCHAR2,
                         pPort     IN INTEGER DEFAULT 21,
                         pUser     IN VARCHAR2,
                         pPassword IN VARCHAR2,
                         pFile     IN VARCHAR2) is
lBytes INTEGER; 
lConnection UTL_TCP.connection;

BEGIN

  lConnection := utl_tcp.open_connection(pServer, pPort); 
  lBytes := UTL_TCP.write_line(lConnection, 'USER ' || pUser); 
  lBytes := UTL_TCP.write_line(lConnection, 'PASS ' || pPassword); 
  lBytes := UTL_TCP.write_line(lConnection, 'DEL ' || pFile); 
  lBytes := UTL_TCP.write_line(lConnection, 'quit'); 
  utl_tcp.close_all_connections;
END;

Regards, Dima

http://seminihin.narod.ru