Oracle Text use Base_Letter setting when creating index

Question

On my database there is a product title. We need to be able to search this so that it isn't accent sensitive, ie searching 'Dorthey' will still return 'Dórthéy'.

We are already creating an Oracle Text index. The setting for converting accented characters to base letters is called base_letter, and needs to be turned on.

This is how the index is created.

create index ITITLE_PSIPRODUCTTITLE on PSI.PSIPRODUCTTITLE(PT_TITLE) INDEXTYPE IS ctxsys.context

Do I turn on this setting when creating the index, and how? Or is the base_letter setting turned on elsewhere?

Appreciate any help!

Answer 1

You have to define a BASIC_LEXER where you set the BASE_LETTER to true.

You use the BASIC_LEXER in the creation of the text index as demonstrated in the example

begin
ctx_ddl.create_preference (
'my_lexer', 'BASIC_LEXER' );
ctx_ddl.set_attribute( 'my_lexer',
'BASE_LETTER', 'true' );
end;
/

create table tab as
select 1 id, 'schön' txt from dual union all
select 2 id, 'schoen' txt from dual union all
select 3 id, 'schon' txt from dual union all
select 4 id, 'schnix' txt from dual;

create index tab_ttx on tab(txt) indextype is ctxsys.context
parameters ( 'lexer my_lexer' );


select * from tab where contains(txt,'schon')>0;

        ID TXT   
---------- ------
         1 schön 
         3 schon 

Please check the other parameters such as ALTERNATE_SPELLING and/or OVERRIDE_BASE_LETTER as illustrated in this blog post

Alternatively consult the Oracle documentation