Oracle text: contains operator with index ignoring diacritical marks

640834

Hi,

I want to create a domain index ignoring diacritical marks like (á,é,ì,ü,...), but it doesn't work. Here's my script:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> drop table test;

Tabla borrada.

SQL> begin
  2    ctx_ddl.drop_preference('my_lexer');
  3  end;
  4  /

Procedimiento PL/SQL terminado correctamente.

SQL>
SQL> create table test (x varchar(30));

Tabla creada.

SQL> insert into test values ('rápido');

1 fila creada.

SQL> commit;

Confirmaci¾n terminada.

SQL>
SQL>
SQL> begin
  2    ctx_ddl.create_preference('my_lexer', 'BASIC_LEXER');
  3    ctx_ddl.set_attribute('my_lexer', 'base_letter', 'yes');
  4  end;
  5  /

Procedimiento PL/SQL terminado correctamente.

SQL> commit;

Confirmaci¾n terminada.

SQL>
SQL>
SQL> CREATE INDEX test_idx
  2      ON test(x)
  3      INDEXTYPE IS CTXSYS.CONTEXT
  4  parameters ('lexer my_lexer Sync (on commit)');

-ndice creado.

SQL>
SQL> select x, dump(x) from test;
X                              DUMP(X)
------------------------------ -------------------------------------
rápido                         Typ=1 Len=6: 114,160,112,105,100,111

1 fila seleccionada.

SQL>
SQL> select * from test where  contains(x,'rapido',1)>0;

ninguna fila seleccionada

SQL>
SQL> select token_text from dr$test_idx$i;
TOKEN_TEXT
----------------------------------------------------------------
PIDO
R

2 filas seleccionadas.

Why Oracle is indexing "rápido" in two pieces, "R" and "PIDO"? I guess that is the reason why "contains(x,'rapido',1)>0" does not find any row.
Am I doing anything wrong related to the baisc_lexer?

Thanks!

Joaquin Gonzalez

640834

It was a problem with sqlplus.exe (not happening to sqlplusw.exe) displaying wrong caracters,
because of the code page it was using.

I've solved executing one of these:
"set NLS_LANG=american_america.US8PC437" or "chcp 1252", before sqlplus.exe.

Joaquin Gonzalez