The Oracle (tm) Users' Co-Operative FAQ

How do I delete an O/S file from within PL/SQl


Author's name: Jonathan Lewis

Author's Email: Jonathan@jlcomp.demon.co.uk

Date written: 28th Jan 2002

Updated : 24th Jan 2005

Oracle version(s): 8.1.7.0 - 9.2

The pl/sql package utl_file allows me to create, read and write flat files at the O/S level on the server. Also the dbms_lob package allows me to read files from the server and load them into the database. But how do I delete an O/S file from within pl/sql after I have finished with it.

Back to index of questions


One 'near-soultion' is to use the utl_file package to re-open the file for writing (without the append option), and then close the file without writing to it. This recovers most of the disk space, but still leaves the file on the system as an empty O/S file.

Another approach is to write a short piece of Java, which can then be called from PL/SQL. Java currently offers far more flexibility than PL/SQL when dealing with O/S files, for example you could use Java to invoke and load a directory listing from PL/SQL so that you know what files exist for deletion. (See further reading).

A pure simple PL/SQL solution, however, appears to exist in the dbms_backup_restore package. This is virtually undocumented (other than in the script dbmsbkrs.sql) in 8.1.7, but contains a simple deletefile() procedure.

begin
        dbms_backup_restore.deletefile('/tmp/temp.txt');
end;
/

This appears to work as required with no side-effects.

Update for 9.2

In version 9.2, the utl_file package has been enhanced with the fremove() procedure for deleting a file.


Updated 24th Jan 2005

I have received an email from S Kumar pointing out that the call to dbms_backup_restore.deletefile() always gives the message: “PL/SQL procedure successfully completed” even if the path or file is not present or file or path name is invalid or if open. So we can not depend on this package's output.


Further reading: Expert One-on-One: Oracle (Tom Kyte) for examples of using Java from PL/SQL packages, in particular a routine to call Java to perform a directory listing.


Back to top

Back to index of questions