Home » Server Options » Text & interMedia » [CONTAINS] No result whereas there are (Oracle Text / Oracle Database Entreprise Edition, v9.2.0.8, Windows 2000)
[CONTAINS] No result whereas there are [message #294724] Fri, 18 January 2008 11:53 Go to next message
morphee7
Messages: 1
Registered: January 2008
Location: France
Junior Member
Hi,

I'm using Oracle Text indexes on a v9.2.0.8 database on windows 2000 SP4.

I have granted my user with the CTXAPP role, created the indexes, added data in the indexed columns (varchar2 and CLOB), rebuilt the indexes.

But whatever the word I'm searching for, I have no result !

Sample of query :
SELECT ID 
FROM MY_TABLE WHERE CONTAINS (TITLE, 'classification',1) > 0

=> No rows returned

But whith
SELECT ID 
FROM MY_TABLE WHERE dbms_lob.instr (TITLE, 'classification',1,1) > 0

=> 1 row returned !!!

I've searched every where : checking grants, index creation, package installation, stoplist, and so on ... nothing. I would be grateful if someone could help me.

Thanks.
Re: [CONTAINS] No result whereas there are [message #294743 is a reply to message #294724] Fri, 18 January 2008 15:07 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Can you post a copy and paste of exactly what you did and the results, especially your index creation and rebuild statements? Did you wait long enough for the index rebuild to finish before querying? What do you see when you select the token_text values from your dr$...$i index table? Please see the working example below.


SCOTT@orcl_11g> CREATE USER my_user IDENTIFIED BY my_user
  2  /

User created.

SCOTT@orcl_11g> GRANT CONNECT, RESOURCE, CTXAPP TO my_user
  2  /

Grant succeeded.

SCOTT@orcl_11g> CONNECT my_user/my_user
Connected.
MY_USER@orcl_11g> 
MY_USER@orcl_11g> CREATE TABLE my_table
  2    (id     NUMBER,
  3  	title  CLOB)
  4  /

Table created.

MY_USER@orcl_11g> CREATE INDEX my_index
  2  ON my_table (title)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  /

Index created.

MY_USER@orcl_11g> INSERT INTO my_table (id, title) VALUES (1, 'classification')
  2  /

1 row created.

MY_USER@orcl_11g> ALTER INDEX my_index REBUILD
  2  /

Index altered.

MY_USER@orcl_11g> SELECT *
  2  FROM   my_table
  3  WHERE  DBMS_LOB.INSTR (title, 'classification', 1, 1) > 0
  4  /

        ID
----------
TITLE
--------------------------------------------------------------------------------
         1
classification


MY_USER@orcl_11g> SELECT token_text FROM dr$my_index$i
  2  /

TOKEN_TEXT
----------------------------------------------------------------
CLASSIFICATION

MY_USER@orcl_11g> SELECT *
  2  FROM   my_table
  3  WHERE  CONTAINS (title, 'classification', 1) > 0
  4  /

        ID
----------
TITLE
--------------------------------------------------------------------------------
         1
classification


MY_USER@orcl_11g> 
 

Previous Topic: CTXCAT / CONTEXT index
Next Topic: oracle text contains near
Goto Forum:
  


Current Time: Thu Mar 28 11:32:58 CDT 2024