I have a large free-text field that I am trying to query for specific occurrences. I have three query terms that need to be near each other, so I want to use the contains near operator. But one of the query terms is either "no" or "not".
When I try to use not, I get this error...
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 have tried "not" instead and it ignores that query term and returns those that contain the other two query terms near each other.
When I try to use no, again it ignores that query term and returns those that contain the other two query terms near each other. But I don't get the above error like I did with not.
Is there a reason "no" or "not" cannot be used as query terms with the contains operator? If so, is there anyway around it?
Thanks
First up, no and not are both in the default stoplist. So you'll have to use a custom stoplist which excludes these to index them.
Once you've done this, you need to escape any keywords you want to search for. As you've discovered not is a keyword. To get around this, stick in between curly braces:
create table t (
x varchar2(10)
);
insert into t values ('this');
insert into t values ('that');
insert into t values ('not this');
commit;
exec ctx_ddl.create_stoplist('empty_stoplist');
create index i on t (x) indextype is ctxsys.context
parameters ('stoplist empty_stoplist');
select * from t
where contains (x, 'this') > 0;
X
this
not this
select * from t
where contains (x, 'not this') > 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 (x, '{not} this') > 0;
X
not this
exec ctx_ddl.drop_stoplist('empty_stoplist');