CODEWITHSUNDEEP
sqloracle-databaseoracle12ctext-search
Athrey T R
Athrey T R

Reputation: 63

Oracle Contains failed working for phrase containing "not" word

I'm trying to search a phrase like 'not placed' in table, where col is indexed by "indextype is ctxsys.context"

select * from 
table
where contains (col, 'not placed')>0

Without the "NOT" word the search is working absolutely fine without any issues.

As soon as the "not" is added in the search phrase the below issue is thrown - 

ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50901: text query parser syntax error on line 1, column 1  
29902. 00000 -  "error in executing ODCIIndexStart() routine"
*Cause:    The execution of ODCIIndexStart routine caused an error.
*Action:   Examine the error messages produced by the indextype code and
           take appropriate action.

I even tried to use escape sequence for the "not" word but it failed to recognize the "not" word itself

Upvotes: 0

Views: 557

Answers (1)

Chris Saxon
Chris Saxon

Reputation: 9875

not is a reserved word for the not operator. You need to escape it to search for this value with contains. Do this by surrounding it in curly braces {}.

It's also one of the default stop words. These are not included in the index.

This creates an index with an empty stop list. So it includes every word:

create table t (
  c1 varchar2(100)
);

insert into t values ( 'placed' );
insert into t values ( 'not placed' );
insert into t values ( 'something else' );
insert into t values ( 'file is placed in folder' ); 
insert into t values ( 'file is not placed in folder' ); 
commit;

create index i 
  on t ( c1 ) 
  indextype is ctxsys.context
  parameters (
    'stoplist ctxsys.empty_stoplist sync(on commit)'
  );

select * from t 
where  contains (c1, 'placed') > 0;

C1                             
placed                          
not placed                      
file is placed in folder        
file is not placed in folder  

select * from t 
where  contains (c1, 'not placed') > 0;

ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50901: text query parser syntax error on line 1, column 1  

select * from t 
where  contains (c1, '{not} placed') > 0;

C1                             
not placed                      
file is not placed in folder

But you probably want to create your own custom stop list.

Upvotes: 1

Related Questions