The trick for making a BFILE read-write or at least read-delete

A while back, I demonstrated 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.

You can find the blog page here …

A better widget for capturing the fully qualified external file name

I’ve updated the how to return a fully qualified BFILE name from a function. The new function lets you select the fully qualified file name from a BFILE column, like:

SELECT get_canonical_local_bfilename(item_photo) AS file_name
FROM item
WHERE item_id = 1021;


In my test environment, it returns:

FILE_NAME
——————————————
C:\JavaDev\BFileFramework\HarryPotter1.png


A twist on a java.security.AccessControlException

A twist or quirk, I’m not sure. I was working on a internal Java library to delete external files when I encountered an ORA-29532. The error said that I needed to grant read file permissions but it really required read, write, and delete permissions. Worse yet, they’d already been granted.

Then, I remembered that this is the instance that I built before assigning a hostname in the C:\WINDOWS\system32\drivers\etc\hosts file. I’d blogged about it earlier, describing how to drop and recreate the Enterprise Manager. I had a hunch, and it worked.

I ran the following:

BEGIN
DBMS_JAVA.GRANT_PERMISSION(’SYSTEM’
,’SYS:java.net.SocketPermission’
,’hostname:1521′
,’connect,resolve’);
END;
/

Then, I reconnected as the test user and everything worked fine. The full error received:

BEGIN delete_file(get_canonical_local_bfilename(:locator));
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.security.AccessControlException: the Permission (java.io.FilePermission
C:\JavaDev\BFileFramework\HarryPotter1.png read) has not been granted to PLSQL.
The PL/SQL to grant this is
dbms_java.grant_permission( ‘PLSQL’,
‘SYS:java.io.FilePermission’, ‘C:\JavaDev\BFileFramework\HarryPotter1.png’,'read’ )
ORA-06512: at “PLSQL.DELETE_FILE”, line 1
ORA-06512: at line 1

You can get the full Java stack trace by two steps:

1. Call the DBMS_JAVA.SET_OUTPUT(2000000);
2. Set SERVEROUTPUT ON

A Quick 64 bit Update

Over the last month I’ve built a number of test environments. Specifically, working with 64 bit OS. I’ve found a number of quirks.

Pet peeves include: (1) The Microsoft patching progrm auto detects x64 and chooses to install IE x64 when Flash is 32 bit and inoperable with 64 bit browsers; (2) VMWare Workstation disallows installation of 64 bit OS when running on Vista Home x64 (appears to require Vista Business or Ultimate); and (3) the work arounds required to install Oracle XE on Ubuntu x64.

A bright note is how slick VMWare Fusion manages installation on a Mac over VMWare Workstation on Windows or Linux. Perhaps those features will be in the next release.

CSS Books Recommendation

David McFarland’s CSS: The Missing Manual is a great reference on my book shelf. Next to it is Pro CSS and HTML Design Patterns book. The latter is a quick reference to those pesky things you want to do now that you may have forgotten, or not yet encountered with CSS. While the why is better in the McFarland’s book, the immediacy of solutions in Bowers’ book is fantastic. Together they’re an awesome set.

A bold step to the fully qualified BFILE file name

I’m working on a framework for synchronizing BFILE column values with the file system. It turns the read-only BFILE datatype into a pseudo read-write datatype. The related page shows you how to get the information from the database catalog.

Staying with the concept of pages, you can find the code here …

A wrapper function to DBMS_LOB.FILEGETNAME procedure

It got tiresome writing the logic for getting a BFILE file name. So, I wrote a wrapper function that lets you return the physical file name for any table. I did it by leveraging NDS against a dynamic anonymous PL/SQL block.

It’s here …

Nice “how-to” install OPAL on Ubuntu 8 Server

I noticed that somebody posted instructions and some scripts to install the OPAL (Oracle, Perl/PHP, Apache, Linux) in the OTN forum. I haven’t had a chance to run though it yet. Thought I’d point you to it directly. If you want the forum, go here. He’d like feedback in the forum.

A couple gotchas with the CONTAINS function

While working on my Oracle Text demonstration, I revisited three old acquaintances. The encounters were likely due to my haste and poor typing. I moved past the errors pretty quickly but thought they could use some documentation.

You can find it here if your interested …

Down, up, and around Hierarchical Queries

Hierarchical queries are powerful structures in the Oracle database. They let you walk a tree down or up, but they can die when you fail to connect rows correctly. I’ve put together a Hierarchical Query Basics page. Hopefully somebody finds it useful.