OraFAQ Forum: Text & interMedia » [CONTAINS] No result whereas there are

Home » Server Options » Text & interMedia » [CONTAINS] No result whereas there are (Oracle Text / Oracle Database Entreprise Edition, v9.2.0.8, Windows 2000)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

[CONTAINS] No result whereas there are [message #294724]

Fri, 18 January 2008 11:53

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.

Report message to a moderator

Re: [CONTAINS] No result whereas there are [message #294743 is a reply to message #294724]

Fri, 18 January 2008 15:07

Barbara Boehmer
Messages: 9104
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>

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 19:06:39 CST 2025