MacLochlainns Weblog

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 …

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.

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 …

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.

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.

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 …

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.