MacLochlainns Weblog

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 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'
                             ,'<strong>hostname</strong><em></em>: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

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.

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 …

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 …

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 …

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.

VMWare Fusion did such a sweet job of managing the Windows XP x64 installation, it lulled me into a false sense of security. It’s ashame VM Workstation doesn’t do the same. I dashed right off and installed Oracle Database 11g. After working through the security key steps for Firefox 3.0, I noticed to my chagrin that resolution was against the IP number, as you can see in the screen shot:

So, I went about changing things. The steps are here if you’re interested. The result is a correctly configured Oracle Enterprise Manager with a valid Windows Hostname.

My Toshiba Tecra died last weekend, I replaced it with a MacBookPro. My MacBook didn’t have enough memory because it’s limited to 2GB. While installing a VM of Windows XP and Oracle 11g, I found that Firefox 3.0 doesn’t trust the Oracle Enterprise Manager certificate.

This is the alert box you get after the install at http://localhost:1158/em:

You can grant a security exception to the browswer. Here are the steps:

1. Dismiss the dialog box, then click the hyperlink “Or you can add an exception…” in the web page.

2. Click the “Add Exception …:” button.

3. Click the “Get Certificate …” button.

4. Confirm the “Create Security Exception” button.

You’re done. So was I, a bit annoying really … a lot like Microsoft Vista – allow or deny.

You probably know foreign key constraints let you maintain referential integrity. They are perfect for mandatory relationships. Mandatory relationships require that a row can’t exist unless a primary key exists somewhere else first. Actually, the primary key can be inserted into the same table as the foreign key for a self-referencing relationship. The somewhere else can be another column in the same row for a self referencing relationship. It can also be a column in another table for an external relationship. You can use a foreign key constraint for an optional relationship. You can write a database trigger to maintain a foreign key for an optional relationship or accept the default behavior as Dominic Brooks reminded me.

The example shows you the design and implementation of such a trigger.