MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for July, 2008

How to describe Oracle collection datatypes

without comments

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 31st, 2008 at 5:06 am

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

without comments

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

without comments

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 26th, 2008 at 11:22 pm

A better widget for capturing the fully qualified external file name

without comments

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 26th, 2008 at 10:36 pm

Posted in Oracle

Tagged with ,

A twist on a

with one comment

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 26th, 2008 at 9:56 pm

A Quick 64 bit Update

without comments

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 26th, 2008 at 7:08 pm

CSS Books Recommendation

without comments

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 25th, 2008 at 8:43 pm

A bold step to the fully qualified BFILE file name

without comments

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 …

Written by maclochlainn

July 23rd, 2008 at 6:33 am

A wrapper function to DBMS_LOB.FILEGETNAME procedure

without comments

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 …

Written by maclochlainn

July 22nd, 2008 at 4:21 am

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

without comments

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.

Written by maclochlainn

July 20th, 2008 at 9:16 pm

Posted in PHP

Tagged with , , , , ,