Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

Archive for July 2008

How to describe Oracle collection datatypes

leave a comment »

User defined collection datatypes are very useful. If they’ve been created by somebody else, it’s helpful to have a query to find out whether they’re a VARRAY or Nested Table. I actually got tired of hunting for the details. You probably might concur. So, here’s a query to find the collection datatype, the limit of a VARRAY, and the base datatype.

You can find it here …

Written by maclochlainn

July 31, 2008 at 5:06 am

How to copy external files with Java and PL/SQL wrappers

leave a comment »

Moving forward with the external file architecture. The referenced page lets you copy external files from one viretual directory to another. There is Java library with two method. One copies external text and the other image files. Two functions wrap the methods.

The code is posted in this page on the blog.

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

leave a comment »

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 …

Written by maclochlainn

July 26, 2008 at 11:22 pm

A better widget for capturing the fully qualified external file name

leave a comment »

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:


Written by maclochlainn

July 26, 2008 at 10:36 pm

Posted in Oracle

Tagged with ,

A twist on a

with 2 comments

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:


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: the Permission (
C:\JavaDev\BFileFramework\HarryPotter1.png read) has not been granted to PLSQL.
The PL/SQL to grant this is
dbms_java.grant_permission( 'PLSQL',
'', '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);

Written by maclochlainn

July 26, 2008 at 9:56 pm

A Quick 64 bit Update

leave a comment »

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.

Written by maclochlainn

July 26, 2008 at 7:08 pm

CSS Books Recommendation

leave a comment »

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.

Written by maclochlainn

July 25, 2008 at 8:43 pm