0

Is there anyway to search PL/SQL reserved words with the contains function in the Oracle?

We would like to search keywords such as "test for" or "test do" with the contains or catsearch functions from the user table in Oracle but the results only show for "test" and ignore those reserved words such as "do" or "for" automatically.

We assume we have a table called content in the users schema.

enter image description here

The queries are listed below:

create table users.content (content_id int not null, content varchar(20) not null);

insert into users.content values (1, 'do something');

insert into users.content values (2, 'test it');

insert into users.content values (3, 'it has been tested');

insert into users.content values (4, 'will test it');

create index users.idx_content on users.content (content) indextype is ctxsys.context;

--drop index users.idx_content;

--drop table users.content;

select content_id, content from users.content where contains(content, 'do or test') > 0;
6
  • Why do you want to do this? – Colin 't Hart Oct 20 '14 at 7:29
  • @Colin'tHart we would like to search keywords such as "for test" or "do test" with contains or catsearch function in the Oracle but the results only show for "test" and ignore the reserved words such as "do" or "for" etc. – Andy Oct 20 '14 at 22:43
  • Are you trying to search the text of code stored in Oracle (which is what I initially read your question as asking)? Or are you trying to search for data stored in a user-defined table? If you're searching for data in a user-defined table, can you create an Oracle Text index on that table? – Justin Cave Oct 20 '14 at 23:55
  • @Justin Cave I am trying to search data from user-defined table and have created the index for that table already. the contains function is working fine with all of keywords except Oracle reserved words. – Andy Oct 21 '14 at 0:00
  • What do you consider "Oracle reserved words"? "do", for example, is not a reserved word in v$reserved_words. – Justin Cave Oct 21 '14 at 0:05
1

You can't use contains because there is no Oracle Text index on the database source. You could, I suppose, write a query that copied the data from dba_source to a custom table, create an Oracle Text index on that table, and search that table using the contains function.

It would generally make more sense, though, to just query dba_source with a like query

SELECT *
  FROM dba_source
 WHERE lower( text ) like '%for %';
3
  • Sorry, I should explain clearly. we want to search keywords such as "for test" or "do test" with contains or catsearch function from user table in the Oracle but the results only show for "test" and ignore the "for" or "do" etc. Thanks. – Andy Oct 20 '14 at 22:53
  • @Andy - Sorry, I'm afraid that comment doesn't clarify anything for me. It may be useful to expand your question to include, say, a few example pieces of code, the types of searches you want to run, and the expected results of those searches on that sample code. Neither "for test" or "do test" are keywords in PL/SQL-- "for" is a keyword but neither "do" nor "test" are. – Justin Cave Oct 20 '14 at 22:57
  • I modified the question and you can have a look. However, thank you for your help anyway :) – Andy Oct 20 '14 at 23:42
0

The following post answers my question and we ignored the stoplist when we created the indexes.

https://community.oracle.com/thread/2181803

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.