Home » Server Options » Text & interMedia » CONTAINS function not working!
CONTAINS function not working! [message #227556] Wed, 28 March 2007 11:09 Go to next message
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 Go to previous message
Barbara Boehmer
Messages: 9077
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>  



Previous Topic: How to index embedded documents
Next Topic: Ctx_doc.ifilter problem
Goto Forum:
  


Current Time: Thu Mar 28 03:39:23 CDT 2024