MacLochlainns Weblog

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 …

I was trying to automate cleaning up external files when I discovered that there isn’t an administrative view in Oracle Database 11g to link table, virtual directory, and external file. Reflecting on that discovery in Oracle 11g, I realized that limits the concept of a push paradigm with an external file. So, I wrote one.

The catalog view is here …

External files are great tools for reading data into and writing data out of an Oracle database. You have two options for reading data into the database. One uses SQL*Loader and the other uses Oracle Data Pump. You have only one option to write data from the database into an external table file. That’s Oracle Data Pump.

I thought this was pretty straightforward when recommending it as a solution. Given the questions that I got back, it appears that it isn’t. Actually, I couldn’t find an example for how you import data through an external table by using Oracle Data Pump. I only checked the Oracle Database Utilities 11g documentation, but maybe its somewhere else. 

The “Creating an external table that uses SQL*Loader” page demonstrates how you can create an input or read-only external table. The “Creating an external table that uses Oracle Data Pump” shows you how to create read-write external tables.

Exceptions covered in the Articles

The two referenced pages should help you understand the basics and resolve these error messages (at least on an Oracle Database 11g where I tested them):

Exception stack raised by Oracle SQL*Loader when you provide file extensions for log, bad, or discarded file names without enclosing them in single quotes:

ORA-29913: error IN executing ODCIEXTTABLEOPEN callout
ORA-29400: DATA cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "dot": expecting one OF: "badfile,
byteordermark, characterset, colon, column, data, delimited, discardfile,
disable_directory_link_check, fields, fixed, load, logfile, language,
nodiscardfile, nobadfile, nologfile, date_cache, processing, readsize, string,
skip, territory, vari"
KUP-01007: at line 2 COLUMN 20

You can also enclose a different Oracle virtual directory by using ‘virtual_directory’:’name.extension’ syntax.

Exception stack raised by Oracle Data Pump when you fail to enumerate columns in the source query:

ERROR at line 6:
ORA-30656: COLUMN TYPE NOT supported ON external organized TABLE

Exception stack raised by Oracle Data Pump when you try to rebuild the external table without previously dropping the external file:

CREATE TABLE item_export
*
ERROR at line 1:
ORA-29913: error IN executing ODCIEXTTABLEOPEN callout
ORA-29400: DATA cartridge error
KUP-11012: file item_export.dmp IN C:\DATA\Download already EXISTS

A post last week in the SQL & PL/SQL Forum caught my eye because it referenced an old post by Tom Kyte. That post shows you how to read an external file system directory using Java library wrapped by a PL/SQL program unit. The problem I have with the solution is that it writes the data to a table, and then it reads the file list from the table. This type of design requires cleaning up the table after running the function or procedure.

An improvement on Tom’s old solution would be to return the list as a SQL collection data type. A few searches on the Internet and of the Oracle documentation didn’t unearth an example. The referenced code and instructions show you how to implement the necessary pieces with a PL/SQL wrapper function.

Somebody got to this blog looking for a way to write a Native Dynamic SQL (NDS) statement that returned a system reference cursor. I created an image file to show how to do that. I borrowed the code from Chapter 11 of Oracle Database 11g PL/SQL Programming. The only problem with this is that it’s a PL/SQL only solution typically. You can write it as a function or procedure, and then use a Java or PHP program to display the reference cursor in a web page. You can check this previous post for a PHP example.

You could write an ugly SQL statement full of CASE statements to derive the leap year moving forward or backward in time. You could likewise write an uglier statement that allows for moving back from a 31 day month to a 30 day month or forward from a 30 day month to a 31 day month.

However, you can use the add_months(date_in,number_of_months), like:

Leap year back to a non-leap year (system clock set to 29-FEB-2008):

SQL\> SELECT add_months(SYSDATE,-12) FROM dual;

From the last day of May to the last day of April (system clock set to 31-MAY-2008):

SQL\> SELECT add_months(SYSDATE,-1) FROM dual;

From the last day of April to the last day of May (system clock set to 30-APR-2008):

SQL\> SELECT add_months(SYSDATE,1) FROM dual;

You can read more here in the Oracle SQL Reference.