Nyway to search PL/SQL reserved words with contains function

oracleplsql

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;

Best Answer

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 %';
Related Question