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.
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
Recent articles in this category:
- Improper Way Of Marketing Reflects Poorly On A Company.
New business, product or service everything requires visibility, awareness in order to come into the - Replacing Paper Prints With Online Versions
Nowadays saving out on resources and being additionally informative are both aspects that are in. In - Stop Smoking Effectively
If I told you of a way that you could stop smoking harmful tobacco would you believe it? Most people - What Is Runtime Error 182? And How To Fix It
Are you finding an effective way to fix runtime error 182? Do you think fixing runtime error 182 is - Do You Know How To Fix Runtime Error 87 In Minutes?
Are you finding an effective way to fix runtime error 87? Do you think fixing runtime error 87 is to - Knowledge About Avi, Avi Player, Avi Converter On Mac
Knowledge about AVI, AVI player, AVI Converter on MacWhat is an AVI?AVI, an acronym for Audio Video - Buy Your Highly Successful Email Survey Software Today
Email Survey Software- Boost Your Business and Increase ProfitsAn email survey software could be one - Xrm - The Anything Relationship Management Solution
I recently attended the Microsoft Dynamics West Region FY11 Sales Planning Retreat. This year's meet - What Are The Benefits Of Working With Electronic Medical Records
Recording medical information is a vital part of health care services. These records are necessary f - Basic Factor To Make Website Business Oriented
Internet is home for millions of websites. The online business is becoming more and more competitive
Most viewed articles in this category:
- Parental Control Software
Parental control software is software that can help parents protect their children when they are onl - Digital Asset Management Software
Managing and organizing your organization's documents is a critical component to your business's suc - AdobeRGB vs. sRGB
Understanding color spaces I'll try to explain it very simplified, but understandable for everyone - Confessions of a Prankster
I wanted to get a jump on April Fool's Day, partially because of the long, cold winter blues, and pa - Malicious Thoughts About The Spyware Ills Of My PC
Who would think I was capable of such revengeful thoughts about the parties responsible for inflicti - Recover File and Recover Deleted File Tools
Data recovery software is a very effective way of retrieving data from a worn or damaged hard disk d - Life without Windows
Ubuntu, a user-friendly version of Linux, has been running so nicely on my home PC that I decided to - What Benefit Does an Online Software Download Site Offer You?
Are you having a problem that where you find a good softeware when you consider to have a try or wan - Maintaining A Website
There was an era when people were talking about how to create a website using html coding or some ea - Benefits Of Proper Time Tracking
Have you ever written down time when you have started and finished your work? Maybe you have had mul