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