MacLochlainns Weblog

A while back, I posted how you can read a file directory from SQL. Here’s how you can delete an external file referenced by a BFILE column. There’s only one problem with this level of the architecture, there’s no rollback. However, it does let you delete the external file when you want to delete the column value.

The first thing that’s required is the code that lets you read fully qualified file names (or canonical file names). You can find that code in this blog page. Once you’ve sorted through that, you should create a virtual directory, insert a reference to a BFILE, and put a copy of the physical file in the external location.

Now you can build the Java library that lets you delete a file. A quick caveat, this code includes an AND logical operator that is two ampersands. SQL uses an ampersand for substitution variables. You’ll need to suppress that behavior when you run this code.

You do that by issuing the following command:

SQL> SET DEFINE OFF

1. Run the internal Java code from SQL as shown:

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "DeleteFile" AS
  // Java import statements
  import java.io.File;
  import java.security.AccessControlException;
 
  // Class definition.
  public class DeleteFile
  {
    // Define variable(s).
    private static File file;
 
    // Define copyTextFile() method.
    public static void deleteFile(String fileName) throws AccessControlException {
 
      // Create files from canonical file names.
      file = new File(fileName);
 
      // Delete file(s).
      if (file.isFile() && file.delete()) {}}}
/

2. After you’ve written the Java library, you write the following wrapper:

CREATE OR REPLACE PROCEDURE delete_file (dfile VARCHAR2) IS
LANGUAGE JAVA
NAME 'DeleteFile.deleteFile(java.lang.String)';
/

You should note that this is a stored procedure because the Java method returns a void type. It is also the best way to deploy it in the database trigger.

3. At this point, you should connect as SYS and run the following command:

BEGIN
  DBMS_JAVA.GRANT_PERMISSION('PLSQL'
                            ,'SYS:java.io.FilePermission'
                            ,'C:\JavaDev\BFileFramework'
                            ,'read,write,delete');
END;
/

If you get a read permission error with an ORA-29532, you should check this blog entry for a possible solution.

4. With the functions to read fully qualified file names from the reference above, you can now build a database trigger. The code is:

CREATE OR REPLACE TRIGGER item_t1
AFTER UPDATE OR DELETE ON item
FOR EACH ROW
WHEN (old.item_photo IS NOT NULL)
BEGIN
  delete_file(get_canonical_local_bfilename(:old.item_photo));
END;
/

5. You can now test this with the following UPDATE or DELETE statements:

UPDATE item SET item_photo = NULL WHERE item_id = 1021;

or

DELETE FROM item WHERE item_id = 1021;

The file is completed removed from the file system. It is not available in the recycle bin (or trash) folder. If you attempt a ROLLBACK of the database, it can’t undo the file deletion. An alternative that lets you synchronize your file deletion with a commit of the transaction is found in this blog page.