MacLochlainns Weblog

Somebody asked for a URI reference to the PL/SQL code for my Oracle Database 11g PL/SQL ProgrammingOracle Database 11g PL/SQL Programming book. You can find it here on the McGraw-Hill site. If you find any glitches, just update my blog. I’ll fix them and get them reposted.

Also, I’ve updated the Comparative Data Modeling Relational versus XML with screen shots for the code and the mapping of a FLOWR (For, Let, Order by, Where, and Return) statements to nested loops for PL/SQL.

Over the last couple of weeks I had the privilege of reviewing the progression of a presentation made by Michael Bowers at the MarkLogic 2008 User Conference. The presentation makes a compelling case that XML databases may have a much larger market share in the near future.

If you’re interested in my reflection on data modeling and a quick example of highly normalized or structured XML, it’s here …

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.

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 …