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
About the Author
Rating: Not yet rated