Oracle Text example

View All Scripts Login to Run Script

Script Name Oracle Text example
Description A basic example of Oracle Text index with stemming and stoplist queries. Also compares against functionality offered by "like" operator.
Area Oracle Text
Contributor Oracle
Created Monday October 05, 2015

Statement 1

create table ot_data ( id          number, 
                       description clob )

Table created.

Statement 2

create index ot_data_idx on ot_data ( description ) 
       indextype is CTXSYS.context 
       parameters ( 'SYNC ( on commit )' )

Index created.

Statement 3

insert into ot_data ( id, description ) values ( 1, 'This text will be indexed by Oracle Text.' )

1 row(s) inserted.

Statement 4

insert into ot_data ( id, description ) values ( 2, 'This example shows several queries that take advantage of Oracle Text.' )

1 row(s) inserted.

Statement 5
```
commit
```
```
Statement processed.
```

Statement 6

Basic Oracle Text query: both "like" and "contains" will return the same 1 row

select * from ot_data where description like '%indexed%'

 
ID DESCRIPTION
1 This text will be indexed by Oracle Text.

ID	DESCRIPTION
1	This text will be indexed by Oracle Text.

Statement 7

select * from ot_data where contains( description, 'indexed' ) > 0

 
ID DESCRIPTION
1 This text will be indexed by Oracle Text.

Statement 8
Using stemming ($) in Oracle Text: the "like" query will not find the direct match
```
select * from ot_data where description like '%query%'
```
```
no data found
```

ID	DESCRIPTION
1	This text will be indexed by Oracle Text.

Statement 9

select * from ot_data where contains( description, '$query' ) > 0

 
ID DESCRIPTION
2 This example shows several queries that take advantage of Oracle Text.

ID	DESCRIPTION
2	This example shows several queries that take advantage of Oracle Text.

Statement 10

Using default English stopwords: the "contains" query will not find any matches because "This" is not indexed

select * from ot_data where description like '%This%'

 
ID DESCRIPTION
1 This text will be indexed by Oracle Text.
2 This example shows several queries that take advantage of Oracle Text.

2 rows selected.

ID	DESCRIPTION
1	This text will be indexed by Oracle Text.
2	This example shows several queries that take advantage of Oracle Text.

Statement 11

select * from ot_data where contains( description, 'This' ) > 0

no data found

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database