MacLochlainns Weblog

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.

Running VMWare Fusion installations doesn’t require interaction. You can walk away from your Mac knowing it won’t go to sleep if you download Caffeine from the Apple download site. It lets you defer the Mac sleep cycle. I’ve found it very useful during the setup of my virtual machines, along with a 320 GB 2.5″ USB drive.

While I’ve worked on many systems over the years, principally Windows for the desktop, and Linux and Unix for the servers, I’ve begun to return to Apple because of Mac OS X. It different than the Apple 6 and Apple 7 that consumed me during my years at American President Lines in Oakland, California. The biggest difference is that my MacBookPro can run Mac OS X, Windows XP x64, Ubuntu, Red Hat 4 in virtual machines courtesy of VMWare Fusion. This affords me a great set of testing environments on a single machine. That does exclude Microsoft Vista. I’ve found Vista’s memory demands coupled with the Oracle 11g really requires 3 GB, and therefore a separate physical machine. I typically only run one virtual machine at a time because they’re allocated 2 GB of memory, and my MacBookPro only (my how life changes) supports 4 GB or memory.

My son just talked me into using Quicksilver, an application launcher – it’s great. I think he’ll make a great technology evangelist someday. You can find more Mac, Ruby, and Django (a Python framework) tricks and techniques on his blog. He’s also just started a gig doing hybrid application development for the iPod and iPhone. I think you’ll be seeing news on that front too.

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.

I got in a hurry while building an Oracle Text sample case. It triggered an error because of missing privileges. When I google’d it, I found there wasn’t a clean answer. Here’s a quick answer and the two dependencies.

1. You must have choosen one of the correct lexer (they’re found in the Oracle Text manual). They are only 10 in the list. Western European languages are typically managed by the BASIC_LEXER or MULTI_LEXER. The former for a single language and the latter for mixed languages. When you create a CONTEXT index.

2. You must have the CTXAPP role granted to your user.

The choice of a wrong lexer is illustrated in the Oracle forum. The following illustrates the error stack raised when you don’t have the right role privileges.

Create a CONTEXT index without the CTXAPP role:

CREATE INDEX regexp_index ON sample_regexp(story_thread)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('LEXER english_lexer STOPLIST ctxsys.default_stoplist');

It creates a corrupt index but raises the following error stack:

CREATE INDEX regexp_index ON sample_regexp(story_thread)
*
ERROR at line 1:
ORA-29855: error occurred IN the execution OF ODCIINDEXCREATE ROUTINE
ORA-20000: Oracle Text error:
DRG-10700: preference does NOT exist: english_lexer
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 365

The word “preference” indicates the missing privilege. Grant the user the CTXAPP, drop the index, and recreate it. Basically, the CTXAPP runs an external procedure that reads the Inxight lexers. They’re found in directories within the $ORACLE_HOME/ctx/data directory.

If you want to know what’s in the default or custom STOPLIST, you can use this set of tools to find, format, and output the words for any STOPLIST.

The example is here …