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.