Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

How to delete an external file from SQL

with one comment

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
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.security.AccessControlException;
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.

Advertisements

Written by maclochlainn

July 26, 2008 at 11:16 pm

One Response

Subscribe to comments with RSS.

  1. […] You can find the blog page here … Tagged with: bfile, delete external file in SQL, ORA-29532 « A better widget for capturing the fully qualified external file name […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: