How to enable case-sensitive search with Oracle Text

Blueriq versions 13.2, 12.13.16, 11.11.27 and higher offer you case-insensitive search out of the box. This applies to searching and filtering data in lists (Aggregate List, Case List, Task List, Work List and Instance List). This article describes how you can configure case insensitive search if you use Oracle combined with an older Blueriq version.

In OracleText, the full-text index is case insensitive by default. The case-sensitivity of the index can be specified at creation time or changed later using lexer preferences.

By default, Blueriq uses the CTX_SYS.DEFAULT_LEXER which comes pre-configured with various settings depending on the language used when the database was installed. For example, if the Oracle database was installed with the Dutch language, the default lexer has composite indexing and alternate spelling enabled for the Dutch language. If a custom lexer is defined, make sure not to omit any settings from the default lexer that you would like to keep.

Creating a case-insensitive lexer

To enable case-sensitivity, a custom lexer must be defined and configured:

begin
	ctx_ddl.create_preference('example_lexer', 'BASIC_LEXER');
	ctx_ddl.set_attribute('example_lexer', 'mixed_case', 'no'); -- no = case sensitive, yes = case insensitive
end;

Setting the lexer at index creation time

The custom lexer is specified as an index parameter:

drop index aq_fulltext_index;
create index aq_fulltext_index on aq_fulltext(text) 
  indextype is ctxsys.context 
  parameters ('datastore aq_fulltext_uds lexer example_lexer sync(every "sysdate+1/24")');

Changing the lexer without recreating the index

The lexer can also be changed without dropping the index first:

alter index aq_fulltext_index parameters ('replace metadata lexer example_lexer');
alter index aq_fulltext_index rebuild;

For this option, the following points need to be taken into consideration:

the 'alter index rebuild parameters' may not be used to change the lexer, instead changing the lexer and rebuilding the index must be done using two separate statements
simply changing the value of an attribute on a lexer preference has no effect on the full-text index, the 'alter index parameters' statement must be used
after the lexer is changed, the effects will not become visible until the index is rebuilt