Searching in CLOB using Oracle Text

Introduction

In the present era of Knowledge based computer applications, there is often a need to store a large amount of text based data into the Database. Moreover, the user also needs facility to search this  text for specific key-words. There are several ways to provide search functionality.

If the Oracle database is used in the application, then such massive text data can be stored into Oracle database as CLOB (Character
Large Object) data type. Oracle 10g supports storing of character data up to 4 GB in CLOB.

Oracle Text provides a powerful text search and text management for Oracle 10g database. Oracle Text indexes the text content for fast & accurate retrieval of information.

Usage

Let me now illustrate how to use Oracle Text for searching in CLOB data into Oracle 10g database.

There is a table in the database which is having a large text content stored as CLOB.

Table : CONTENT_TBL

CONTENT_ID                NUMBER(6)

CONTENT_DESC          VARCHAR2(100)

TEXT_CONTENT            CLOB

Oracle Text requires an index to be created ion  the field which is to be searched for. So, create an index named Content_Tbl_Index

as :CREATE INDEX Content_Tbl_Index ON Content_Tbl (Text_Content) INDEXTYPE IS CTXSYS.CONTEXT;

Now, user can search for all content with multiple combinations of Keywords and operators, for example:

SELECT Content_Desc FROM Content_Tbl WHERE CONTAINS (Text_Content, %OUT SOURCING%

OR %SOFTWARE DEVELOPMENT% OR %WEB SITE% ) >0;

This query will return all the rows from the database  with the column TEXT_CONTENT containing any of the key words Out Sourcing,

Software Development, Web Site.

Concerns

While using the Oracle Text search features, I  have observed that the following points are to be kept in mind for getting

proper search results:

1.The keyword as well as join operators are to be provided in Upper case.

2. The keyword should be pre-fixed and suffixed by a % sign.

3. Whenever the content is changed or updated, the index does not updated automatically.

It follows  the ing line which  needs to be executed after each "Save" process  to update the index.

CTX_DDL.SYNC_INDEX (index name)

Author:

By C.J.Derasari

C.J.Derasari is working as a Project Leader at Semaphore Infotech Pvt. Ltd, India. He has more than 16 years Experience. You can contact on email: cjd@semaphore-software.com