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.

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 …

The lack of a DESCRIBE ALL command frustrated me when working on my first Oracle Applications instance. I wrote a view that describes all tables and views in a schema. Then, I wrote a query that formats the data from the view. The datatypes are now current for Oracle 11g. There’s also a PHP program to provide a web view of the data.

The query lets you see the formatted data from the SQL> prompt, where you can spool it to a file.

You can find the view, query and PHP program here.

While an earlier entry discusses CSV uploads to Oracle external tables, I neglected to mention some things. You can also upload position specific files, override the directory for log files, and override the file extensions.

There are actually two syntax methods for uploading position specific file. Only one is covered in the Oracle 11g Database Utilities manual. It makes you wonder if Oracle supports one or both.

You can override default log, bad, or discard file extensions when you enclose the relative file name in single quotes. You can also specify a virtual directory that differs from your upload (data import) directory.

You may get a surprise if you think virtual directories are case insensitive while defining external tables. They’re not. You must enter overriding virtual directories in uppercase only! You’ll raise this exception stack if you attempt anything else:

ERROR at line 1:
ORA-29913: error IN executing ODCIEXTTABLEOPEN callout
ORA-29400: DATA cartridge error
KUP-04080: directory object download NOT found

The full syntax for virtual directories and extensions is in this note.

A couple students snagged some data through Google searches but they found it was formatted as an XML file. They wanted to know the easiest way to convert it and load it into an Oracle database. I created this quick reference which leverages Apache Xalan Project command-line tool and Oracle external tables. You must source files in the %CLASSPATH% (or $CLASSPATH) correctly order as shown in the example or you will raise a "NoClassDefFoundError: org/apache/xalan/xslt/Process" error.

Briefly, XML supports two paradigms. One is the single-pass compiler (or streams approach). Another is a data structure (or tree approach), which typically requires programming skills.

XSLT Processors typically implement a streams approach. You call the XSLT Processor by passing two arguments. One is an XML source document and the other XSL style sheet.

While this is often done in your web browser or an application, you can leverage the technology to manually convert an XML file transfer into a comma-delimited file. Comma-delimited files are also known as comma separated value (CSV) files. After you convert the XML file you can use an Oracle external table to read it into the database. Naturally, the Oracle XDK offers more features and complexity.

A quick example …