Home » Server Options » Text & interMedia » Problems in using Contains clause
icon4.gif  Problems in using Contains clause [message #233800] Fri, 27 April 2007 09:02 Go to next message
Rashmi_Katariya
Messages: 9
Registered: April 2007
Junior Member
Hi All,
I have table with search_text field of CLOB datatype. iam using contains clause to search text in that field. most of the time the contains clause works. however, there are few phrases where my contains clause is not returning me the row, inspite of the row being available. my query is as follows :

Select contract_id,search_text
From scd_contract_detail
Where contains(search_text,'About the client',1) >0;

This particular phrase, "About the client" is not being searched in he field by cotains clause. Preveiously i had an issue with "on-demand" phrase which was solved by using curly brackets like "{on-demand}. but "about the client" phrase not working still. is there any problem with the phrase?

can there not be any generic method that will take care of all types of phrases?like using curly braces etc?

Any help appreciated..

Thanks,
Rashmi K.
Re: Problems in using Contains clause [message #234293 is a reply to message #233800] Mon, 30 April 2007 13:54 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Is the CTX index freshly rebuilt?
Re: Problems in using Contains clause [message #234782 is a reply to message #233800] Thu, 03 May 2007 02:43 Go to previous messageGo to next message
Rashmi_Katariya
Messages: 9
Registered: April 2007
Junior Member
No. The CTX index is not freshly rebuilt. it was built when the table was created. this is the DDL of the table.

CREATE INDEX scd_contract_detail_idx ON
idcscd.scd_contract_detail(search_text) INDEXTYPE IS CTXSYS.CONTEXT;

ALTER INDEX scd_contract_detail_idx REBUILD ONLINE PARAMETERS ('sync memory 20M');


Do i need to define an empty stoplist?

Thanks,
Rashmi.
Re: Problems in using Contains clause [message #235051 is a reply to message #233800] Thu, 03 May 2007 18:56 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Like "-", "about" has special meaning in Oracle Text. There is a list of the special words and symbols in the online text documentation. Any time that you want to search for a whole phrase that contains any of them, you need to use the curly brackets, as demonstrated below.

SCOTT@10gXE> CREATE TABLE scd_contract_detail
  2    (contract_id  NUMBER,
  3  	search_text  CLOB)
  4  /

Table created.

SCOTT@10gXE> INSERT ALL
  2  INTO scd_contract_detail VALUES (1, 'All about the client')
  3  INTO scd_contract_detail VALUES (2, 'More about the client')
  4  INTO scd_contract_detail VALUES (3, 'About something else')
  5  SELECT * FROM DUAL
  6  /

3 rows created.

SCOTT@10gXE> CREATE INDEX scd_contract_detail_idx
  2  ON scd_contract_detail (search_text)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  /

Index created.

SCOTT@10gXE> -- reproduction of problem:
SCOTT@10gXE> COLUMN search_text FORMAT A30
SCOTT@10gXE> Select contract_id, search_text
  2  From   scd_contract_detail
  3  Where  contains (search_text, 'About the client', 1) > 0
  4  /
Select contract_id, search_text
*
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50901: text query parser syntax error on line 1, column 7


SCOTT@10gXE> -- solution:
SCOTT@10gXE> Select contract_id, search_text
  2  From   scd_contract_detail
  3  Where  contains (search_text, '{About the client}', 1) > 0
  4  /

CONTRACT_ID SEARCH_TEXT
----------- ------------------------------
          1 All about the client
          2 More about the client

SCOTT@10gXE> 



Re: Problems in using Contains clause [message #235163 is a reply to message #233800] Fri, 04 May 2007 03:54 Go to previous messageGo to next message
Rashmi_Katariya
Messages: 9
Registered: April 2007
Junior Member
Yes, this works.I saw the oracle documentation. There are around 100 reserved qwords and special characetrs defined by oracle. "About" and "on" as you mentioned are one of them.
Can there not be a way to identify that this belongs to the oracles default list. I mean , is there any table or some command or any other way available to identify this list. Otherwise then i can create a table with all these characters and words and then search if it matches. This will be a little longer route to take.


Thanks for All the help. I have almost solved my problem.

Rashmi.
Re: Problems in using Contains clause [message #235271 is a reply to message #233800] Fri, 04 May 2007 11:04 Go to previous messageGo to next message
Rashmi_Katariya
Messages: 9
Registered: April 2007
Junior Member
Hello Again,
I have created a table of special characters and words to identify them in contains clause. Depending on it i would use curly braces.

SQL> create table special_characters (character_text varchar2(10));
SQL> TABLE CREATED.

I have inserted all the following special words and characters :


There is no problem with Words. But while comparing the special characters like &,%,}/ etc, iam not getting the count. My query is :

SQL>Select * from special_characeter where character_text = '%';

SQL> 0 rows selected.

I also tried this :
SQL>SET ESCAPE \

This searches for few special characetrs like &,\, but all special characetrs are not searched by this. Are there different escape characetrs to be used for different special characeters? Will backslash \ not escape all the special characeters?

Any help appreciated.

Thanks,
Rashmi.
Re: Problems in using Contains clause [message #235272 is a reply to message #233800] Fri, 04 May 2007 11:07 Go to previous messageGo to next message
Rashmi_Katariya
Messages: 9
Registered: April 2007
Junior Member
Hello Again,
I have created a table of special characters and words to identify them in contains clause. Depending on it i would use curly braces.

SQL> create table special_words (character_text varchar2(10));
SQL> TABLE CREATED.

I have inserted all the following special words and characters :
insert into special_words values('ABOUT');
insert into special_words values('ACCUM');
insert into special_words values('AND');
insert into special_words values('BT');
insert into special_words values('BTG');
insert into special_words values('BTI');
insert into special_words values('BTP');
insert into special_words values('EQUIV');
insert into special_words values('FUZZY');
insert into special_words values('HASPATH');
insert into special_words values('INPATH');
insert into special_words values('MDATA');
insert into special_words values('MINUS');
insert into special_words values('NEAR');
insert into special_words values('NOT');
insert into special_words values('NT');
insert into special_words values('NTG');
insert into special_words values('NTI');
insert into special_words values('NTP');
insert into special_words values('OR');
insert into special_words values('PT');
insert into special_words values('RT');
insert into special_words values('SQE');
insert into special_words values('SYN');
insert into special_words values('TR');
insert into special_words values('TRSYN');
insert into special_words values('TT');
insert into special_words values('WITHIN');


insert into special_words values(',');
insert into special_words values('&');
insert into special_words values('=');
insert into special_words values('?');
insert into special_words values('{}');
insert into special_words values('\');
insert into special_words values('()');
insert into special_words values('[]');
insert into special_words values('-');
insert into special_words values(';');
insert into special_words values('~');
insert into special_words values('|');
insert into special_words values('!');
insert into special_words values('>');
insert into special_words values('*');
insert into special_words values('%');
insert into special_words values('_');



There is no problem with Words. But while comparing the special characters like &,%,}/ etc, iam not getting the count. My query is :

SQL>Select * from special_words where character_text = '%';

SQL> 0 rows selected.

I also tried this :
SQL>SET ESCAPE \

This searches for few special characetrs like &,\, but all special characetrs are not searched by this. Are there different escape characetrs to be used for different special characeters? Will backslash \ not escape all the special characeters?

Any help appreciated.

Thanks,
Rashmi.
Re: Problems in using Contains clause [message #235283 is a reply to message #235272] Fri, 04 May 2007 13:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I am unable to reproduce the problem. Can you provide a copy and paste of an actual run, as I have done below?

SCOTT@10gXE> create table special_words (character_text varchar2(10))
  2  /

Table created.

SCOTT@10gXE> insert into special_words values('%')
  2  /

1 row created.

SCOTT@10gXE> select * from special_words where character_text = '%'
  2  /

CHARACTER_
----------
%

SCOTT@10gXE> 


Re: Problems in using Contains clause [message #235332 is a reply to message #233800] Sat, 05 May 2007 01:43 Go to previous message
Rashmi_Katariya
Messages: 9
Registered: April 2007
Junior Member
Yes, this statement runs well now.. I dont know what wrong i was doing then to get 0 rows selected. Unable to reproduce it again ... Sorry for the trouble Smile My Problem is solved now with your help.

Thanks a lot Smile
Rashmi.
Previous Topic: Multi-column index setup question
Next Topic: How to define stopwords and stoplist
Goto Forum:
  


Current Time: Thu Mar 28 17:21:59 CDT 2024