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

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.

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.

Note: 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.

No comments :

Post a Comment