Deleting O/S files from Oracle SQL or PL/SQL
I am required to download images from from an Oracle BLOB field into an O/S UNIX directory.
Doing that is not a big deal and there are many scripts widely available over the net that can do exactly that for you using the UTL_FILE utility. However, i want to make sure that the O/S directory is clean before i start downloading the images. I only need to delete the files in a specific directory first
As there are many solution for this problem, i however, favored using JAVA Stored Procedures for that
Here is the code to build the java stored procedure
CREATE OR REPLACE AND RESOLVE JAVA
SOURCE NAMED "OSDelete" AS
import java.io.File;
public class OSDelete {
public static int delete (String fileName) {
File myFile = new File (fileName);
boolean retval = myFile.delete();
if (retval)
return 1;
else
return 0;
}
}
/
This is just a class called OSDelete with a method called delete
Next, we need to wrap it in an Oracle PL/SQL function
CREATE OR REPLACE function fdelete (file_name
varchar2)
return number is
language JAVA NAME 'OSDelete.delete(java.lang.String) return
java.lang.int';
/
Now it is a normal function that you can use in your standard Query or PL/SQL.
Declare
x number;
begin
x := fdelete('/home/oracle/ammar.txt'); -- UNIX filesystem
end;
Note if you are using windows, you have to use Windows Filesystem conventions (ie 'c:\temp\etc...'
I created a table to test this function
The table contains filename for files that need to be deleted
CREATE TABLE TEST_DEL
(F1 varchar2(100));
INSERT INTO TEST_DEL values ('/home/oracle/ammar1.txt');
INSERT INTO TEST_DEL values ('/home/oracle/ammar2.txt');
Commit;
I created ammar1.txt and ammar2.txt on the /home/oracle directory
Then I issued that following Query
SQL>SELECT FDELETE(f1) from TEST_DEL;
As expected that files ammar1.txt and ammar2.txt disappeared from the O/S