Forum Stats

  • 3,783,338 Users
  • 2,254,760 Discussions
  • 7,880,368 Comments

Discussions

Oracle text: contains operator with index ignoring diacritical marks

640834
640834 Member Posts: 25
edited Dec 3, 2008 5:40AM in General Database Discussions
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
Tagged:

Answers

  • 640834
    640834 Member Posts: 25
    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
This discussion has been closed.