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)