Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

How to link file deletion to transaction control

with one comment

As you may have noticed in the early page on How to delete an external file from SQL, the ON DELETE trigger deletes the file on the first phase of a two-phase commit. By the way, you’ll need the code and instructions from that other post for this one. This means that while you can rollback the BFILE locator value, you can’t recreate the file. It isn’t in the trash bin unfortunately.

I thought a GLOBAL TEMPORARY TABLE would fill the role quite well because a COMMIT or ROLLBACK deletes the contents of the table. I tested my theory by building the following table:

While I could build an ON DELETE trigger, I found that while the COMMIT deletes the rows but fails to trigger the event. It would have been so great if a regular COMMIT could have made this work, but it didn’t.

I rebuilt the table as a normal table and tested my model successfully. The components to make this work are noted below:

1. The placeholder table lets you stuff a BFILE locator their until you’re ready to COMMIT the change:

2. Then, I built a trigger on the ITEM table (taken from the Oracle Database 11g PL/SQL Programming book code). It inserts the BFILE locator in the FILE_LIST table:

3. Then, I built a trigger to commit work, delete external files, and cleanup the FILE_LIST table. It is this:

4. A ROLLBACK at this point undoes the transaction. The value in the ITEM_PHOTO column is recovered, and the copy of the BFILE locator is erased from the FILE_LIST table. The problem is how to effect the desired behavior with the commit. I wrote the FILE_ACTION_COMMIT procedure to manage that process, as shown:

A commit, delete and commit is probably overkill, but I wanted to convey the idea of the sequence if a TEMPORARY GLOBAL TABLE would’ve worked. A delete and commit would work more efficiently.

Now that the environment is there, you should put a file named Raiders3.png in a virtual directory named IMAGES to perform this test. The testing steps are:

1. Run the following two UPDATE statements. Alternatively, copy the query from the next step in between to see your handiwork. The second UPDATE statement triggers the process.

2. Query the FILE_LIST to see that a copy of the BFILE locator exists:

3. Rollback the transaction and see everything is unchanged both in and out of the database, or execute the FILE_ACTION_COMMIT procedure. It will synchronize the database with the external file system.

SQL> EXECUTE file_action_commit;

It would have been so much cleaner with a GLOBAL TEMPORARY TABLE if they worked the way that I thought they did. At least, this works effectively and simply.

Written by maclochlainn

August 22, 2008 at 6:34 am

One Response

Subscribe to comments with RSS.

  1. […] You can find it here …. Tagged with: deleting external files, global temporary tables, Oracle, pl/sql « Code for my Oracle Database 11g PL/SQL Programming […]

Leave a Reply

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

You are commenting using your 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: