 Problems in using Contains clause [message #233800] |
Fri, 27 April 2007 09:02  |
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 #234782 is a reply to message #233800] |
Thu, 03 May 2007 02:43  |
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 |
 |
Barbara Boehmer
Messages: 9100
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 |
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 |
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 |
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.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
My Problem is solved now with your help.