Re: search for reserved word in Oracle Text

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Fri, 18 Jun 2004 01:03:03 +0200
Message-ID: <125901c454bf$3f70cba0$3c02a8c0@JARAWIN>

Hi Ivan,

this is more a problem of special characters than of a reserved words. You may find printjoins in lexer helpfull. There is lot of other stuff as skipjoins, numjoins, numgroups etc. Consult Oracle Text Reference for details.
Printjoins are characters that should be considered in index terms, e.g. FILE_NAME is indexed as FILENAME if '_' isn't a printjoin. It works (in principle) fine, but there are some pittfalls as illustrated below on your example with '?'. (I'm on 9.2.0.1.0)

SQL> @test_text_index
SQL> BEGIN
2 --- define valid delimiters for indexing 3 ctx_ddl.drop_preference('mylex_test'); 4 ctxsys.ctx_ddl.create_preference('mylex_test', 'BASIC_LEXER'); 5 ctxsys.ctx_ddl.set_attribute('mylex_test', 'printjoins', '_$%&?'); 6 END;
7 /

PL/SQL procedure successfully completed.

SQL> -- check it
SQL> select prv_attribute, prv_value from CTX_PREFERENCE_VALUES where prv_prefer
ence = 'MYLEX_TEST'
2 ;

PRV_ATTRIBUTE

PRV_VALUE

PRINTJOINS
_$%&?

SQL> -- prepare table
SQL> drop table t;

Table dropped.

SQL> create table t (t varchar2(30));

Table created.

SQL> --- populate and ..
SQL> insert into t values ('a?b');

1 row created.

SQL> insert into t values ('a_b');

1 row created.

SQL> insert into t values ('?');

1 row created.

SQL> insert into t values ('_');

1 row created.

SQL> insert into t values ('a$b');

1 row created.

SQL> insert into t values ('$');

1 row created.

SQL> ----
SQL> commit;

Commit complete.

SQL> -- define index
SQL> create index t_ix1 on t(t)
2 indextype is ctxsys.context
3 parameters ('DATASTORE CTXSYS.DEFAULT_DATASTORE LEXER mylex_test');

Index created.

SQL> -- only one row with ? found ..
SQL> select score(1), t
  2 from t
  3 where
  4 contains(t,'%\?%',1) > 0
  5 order by score(1) desc;

SCORE(1) T
---------- ------------------------------

5 a?b

SQL> -- because single "?" is lost .... don't know why! SQL> select token_text, token_type FROM DR$T_IX1$I;

TOKEN_TEXT                                                       TOKEN_TYPE


---------------------------------------------------------------- ----------

$                                                                         0
A$B                                                                       0
A?B                                                                       0
A_B                                                                       0
_                                                                         0

SQL> -- here is the world O.K.
SQL> select score(1), t
  2 from t
  3 where
  4 contains(t,'%\$%',1) > 0
  5 order by score(1) desc;

SCORE(1) T
---------- ------------------------------

         5 $
         5 a$b

SQL>
----- Original Message -----

From: "Ivan Chow" <ichow2_at_hotmail.com>
To: <oracle-l_at_freelists.org>
Sent: Thursday, June 17, 2004 8:36 PM
Subject: search for reserved word in Oracle Text

> Hi,
> Anyone has any idea how to query a reserved word in Oracle Text?

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

Received on Thu Jun 17 2004 - 18:05:07 CDT