Home » Server Options » Text & interMedia » CONTAINS function not working!
| CONTAINS function not working! [message #227556] |
Wed, 28 March 2007 11:09  |
RashmiK
Messages: 1
Registered: March 2007
|
Junior Member |
|
|
Hello,
I have a table which has a column
SEARCH_FIELD CLOB datatype.
I want to search the text within this column and iam using CONTAINS function for this.
select * from scd_contract_detail
where contains(search_text,'on-demand',1) >0
However, this does not return me any rows inspite of the rows available in the table. Seems there is some problem with the "-" operator here. If i remove the "-" operator and search it gives me the record but only searches on the character after space i.e "demand" and does not search based on the complete search text "on demand".
If i try to search only on "-" operator like
select * from scd_contract_detail
where contains(search_text,'-',1) >0
I get the following exception
ORA-29902 - Error in executing ODCIIndexStart() routine
ORA-2000 - intermedia Text Error:
DRG-50901 - Text query parser syntax error on line 1 column 1
Can somebody help me with this? Is this related to the Oracle Internal Problem?
Awaiting Reply,
Thanks,
Rashmi..
|
|
|
|
| Re: CONTAINS function not working! [message #227616 is a reply to message #227556] |
Wed, 28 March 2007 15:44  |
 |
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It would help to know what results you want it to return and want results you do not want returned. There are various issues involved here and various combinations produce different results. Some of the issues are:
1. If you do not specify a stoplist, Oracle uses the default stoplist, which includes the word "on", as well as other overly common words to be ignored, such as "the", "and", "or", "of", and so on. So, the word "on" is not tokenized and is ignored in search strings. So, searching for "on-demand" is like search for "-demand". You can specify an empty stoplist or create your own stoplist with your own stopwords.
2. The "-" symbol has special meaning to Oracle text; It means minus. So, searching for "on-demand" means to search for any rows that contain "on" minus any rows that contain "demand", so it would find "on call", but not "on demand", if "on" is not a stopword.
3. Unless you specify a lexer, Oracle uses the default lexer and words containing a hyphen (-) are tokenized as two separate words, so "on-demand" is tokenized as two separate words, "on" and "demand", without the hyphen. You can create your own lexer and specify "-" as whitespace so "on-demand" is treated like "on demand" or you can create your own lexer and specify "-" as a printjoin, so that "on-demand" is tokenized and searchable as one word.
4. If you want Oracle Text to search for words that contain special characters and ignore their special meaning, them you need to enclose the entire token in the search string with curly brackets, like "{on-demand}", which will search for "on-demand" with the hyphen in it, if on is not a stopword and the hyphen has been specified as a printjoin.
These are just a few possible factors. Please see the demonstration below that shows some of the differing results under different combinations of circumstances.
-- table and data:
SCOTT@10gXE> CREATE TABLE scd_contract_detail
2 (search_text CLOB)
3 /
Table created.
SCOTT@10gXE> INSERT ALL
2 INTO scd_contract_detail VALUES ('on-demand')
3 INTO scd_contract_detail VALUES ('on demand')
4 INTO scd_contract_detail VALUES ('on-call')
5 INTO scd_contract_detail VALUES ('on call')
6 INTO scd_contract_detail VALUES ('on')
7 INTO scd_contract_detail VALUES ('demand')
8 INTO scd_contract_detail VALUES ('whatever')
9 SELECT * FROM DUAL
10 /
7 rows created.
-- with default stoplist and default lexer:
SCOTT@10gXE> CREATE INDEX your_index ON scd_contract_detail (search_text)
2 INDEXTYPE IS CTXSYS.CONTEXT
3 /
Index created.
SCOTT@10gXE> SELECT token_text FROM dr$your_index$i
2 /
TOKEN_TEXT
----------------------------------------------------------------
CALL
DEMAND
WHATEVER
SCOTT@10gXE> SELECT * FROM scd_contract_detail
2 WHERE CONTAINS (search_text, 'on-demand', 1) > 0
3 /
no rows selected
SCOTT@10gXE> SELECT * FROM scd_contract_detail
2 WHERE CONTAINS (search_text, 'on MINUS demand', 1) > 0
3 /
no rows selected
SCOTT@10gXE> SELECT * FROM scd_contract_detail
2 WHERE CONTAINS (search_text, '{on-demand}', 1) > 0
3 /
SEARCH_TEXT
--------------------------------------------------------------------------------
on-demand
on demand
SCOTT@10gXE> SELECT * FROM scd_contract_detail
2 WHERE CONTAINS (search_text, 'on demand', 1) > 0
3 /
SEARCH_TEXT
--------------------------------------------------------------------------------
on-demand
on demand
SCOTT@10gXE> SELECT * FROM scd_contract_detail
2 WHERE CONTAINS (search_text, 'on', 1) > 0
3 /
no rows selected
SCOTT@10gXE> SELECT * FROM scd_contract_detail
2 WHERE CONTAINS (search_text, 'demand', 1) > 0
3 /
SEARCH_TEXT
--------------------------------------------------------------------------------
on-demand
on demand
demand
-- with an empty stoplist (no stopwords) and "-" as whitespace:
SCOTT@10gXE> BEGIN
2 CTX_DDL.CREATE_PREFERENCE ('your_lexer', 'BASIC_LEXER');
3 CTX_DDL.SET_ATTRIBUTE ('your_lexer', 'WHITESPACE', '-');
4 END;
5 /
PL/SQL procedure successfully completed.
SCOTT@10gXE> DROP INDEX your_index
2 /
Index dropped.
SCOTT@10gXE> CREATE INDEX your_index ON scd_contract_detail (search_text)
2 INDEXTYPE IS CTXSYS.CONTEXT
3 PARAMETERS
4 ('LEXER your_lexer
5 STOPLIST CTXSYS.EMPTY_STOPLIST')
6 /
Index created.
SCOTT@10gXE> SELECT token_text FROM dr$your_index$i
2 /
TOKEN_TEXT
----------------------------------------------------------------
CALL
DEMAND
ON
WHATEVER
SCOTT@10gXE> SELECT * FROM scd_contract_detail
2 WHERE CONTAINS (search_text, 'on-demand', 1) > 0
3 /
SEARCH_TEXT
--------------------------------------------------------------------------------
on-call
on call
on
SCOTT@10gXE> SELECT * FROM scd_contract_detail
2 WHERE CONTAINS (search_text, 'on MINUS demand', 1) > 0
3 /
SEARCH_TEXT
--------------------------------------------------------------------------------
on-call
on call
on
SCOTT@10gXE> SELECT * FROM scd_contract_detail
2 WHERE CONTAINS (search_text, '{on-demand}', 1) > 0
3 /
SEARCH_TEXT
--------------------------------------------------------------------------------
on-demand
on demand
SCOTT@10gXE> SELECT * FROM scd_contract_detail
2 WHERE CONTAINS (search_text, 'on demand', 1) > 0
3 /
SEARCH_TEXT
--------------------------------------------------------------------------------
on-demand
on demand
SCOTT@10gXE> SELECT * FROM scd_contract_detail
2 WHERE CONTAINS (search_text, 'on', 1) > 0
3 /
SEARCH_TEXT
--------------------------------------------------------------------------------
on-demand
on demand
on-call
on call
on
SCOTT@10gXE> SELECT * FROM scd_contract_detail
2 WHERE CONTAINS (search_text, 'demand', 1) > 0
3 /
SEARCH_TEXT
--------------------------------------------------------------------------------
on-demand
on demand
demand
-- with an empty stoplist and "-" as printjoin:
SCOTT@10gXE> DROP INDEX your_index
2 /
Index dropped.
SCOTT@10gXE> EXEC CTX_DDL.DROP_PREFERENCE ('your_lexer')
PL/SQL procedure successfully completed.
SCOTT@10gXE> BEGIN
2 CTX_DDL.CREATE_PREFERENCE ('your_lexer', 'BASIC_LEXER');
3 CTX_DDL.SET_ATTRIBUTE ('your_lexer', 'PRINTJOINS', '-');
4 END;
5 /
PL/SQL procedure successfully completed.
SCOTT@10gXE> CREATE INDEX your_index ON scd_contract_detail (search_text)
2 INDEXTYPE IS CTXSYS.CONTEXT
3 PARAMETERS
4 ('LEXER your_lexer
5 STOPLIST CTXSYS.EMPTY_STOPLIST')
6 /
Index created.
SCOTT@10gXE> SELECT token_text FROM dr$your_index$i
2 /
TOKEN_TEXT
----------------------------------------------------------------
CALL
DEMAND
ON
ON-CALL
ON-DEMAND
WHATEVER
6 rows selected.
SCOTT@10gXE> SELECT * FROM scd_contract_detail
2 WHERE CONTAINS (search_text, 'on-demand', 1) > 0
3 /
SEARCH_TEXT
--------------------------------------------------------------------------------
on call
on
SCOTT@10gXE> SELECT * FROM scd_contract_detail
2 WHERE CONTAINS (search_text, 'on MINUS demand', 1) > 0
3 /
SEARCH_TEXT
--------------------------------------------------------------------------------
on call
on
SCOTT@10gXE> SELECT * FROM scd_contract_detail
2 WHERE CONTAINS (search_text, '{on-demand}', 1) > 0
3 /
SEARCH_TEXT
--------------------------------------------------------------------------------
on-demand
SCOTT@10gXE> SELECT * FROM scd_contract_detail
2 WHERE CONTAINS (search_text, 'on demand', 1) > 0
3 /
SEARCH_TEXT
--------------------------------------------------------------------------------
on demand
SCOTT@10gXE> SELECT * FROM scd_contract_detail
2 WHERE CONTAINS (search_text, 'on', 1) > 0
3 /
SEARCH_TEXT
--------------------------------------------------------------------------------
on demand
on call
on
SCOTT@10gXE> SELECT * FROM scd_contract_detail
2 WHERE CONTAINS (search_text, 'demand', 1) > 0
3 /
SEARCH_TEXT
--------------------------------------------------------------------------------
on demand
demand
SCOTT@10gXE>
|
|
|
|
Goto Forum:
Current Time: Thu Nov 21 10:38:24 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
