Searching in CLOB using Oracle Text


by Semaphore - Date: 2007-02-15 - Word Count: 366 Share This!







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.



Related Tags: india, windows mobile, business process outsourcing, software testing, offshore software development, consulting services, offshore outsourcing, data entry india, offshore outsourcing india, outsourcing software development, application development, offshore software development company india, it outsourcing company, website design and development, java development, net application development, vb .net application development, asp .net development, database application, system integration, search engine optimization and promotion

Your Article Search Directory : Find in Articles

© The article above is copyrighted by it's author. You're allowed to distribute this work according to the Creative Commons Attribution-NoDerivs license.
 

Recent articles in this category:



Most viewed articles in this category: