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