Searching in CLOB using Oracle Text

Introduction
In l & 39; current era based on knowledge of computer applications, it is often necessary to store large amounts of data based on the text in the database. In addition, the user must also & 39; opportunity to search the text for keywords. There are several ways to provide search functionality.
If the Oracle database is used in the application & 39;, these massive text data can be stored in Oracle database as CLOB (Character Large Object) data type . Oracle 10g supports the storage of data & 39; up to 4 GB in CLOB.
Oracle text provides a very powerful research and management text database Oracle 10g. Oracle Text indexes the text content of fast and accurate research information.
Usage
Let me now d & 39; illustrate how d & 39; use Oracle Text for research in CLOB data in Oracle 10g database.
There is a table in the database which is having a great & 39; 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 create the field d & 39; ions, which must be sought. Thus, to create an index named Content Tbl Index:
CREATE INDEX Content Tbl Index ON
Content Tbl (Text Content) INDEXTYPE EAST CTXSYS.CONTEXT;
Now, & 39; user can search the entire contents with multiple combinations of keywords and operators, for example:
SELECT Content Desc OF Content Tbl
WHERE CONTAINS (Text Content, & 39; OUT SOURCING%%% & 39; or & 39; SOFTWARE DEVELOPMENT & 39; or & 39;%%% & 39; WEB SITE) > 0;
This query returns all lines of the database with TEXT CONTENT column containing a & 39; keywords & 39; Out Sourcing & 39;, & 39; Software Development ", " Website ".
Concerns
While Oracle using the search features text, I & 39;& 39;ve found that the following points must be taken into account to obtain good results of research:
1. The keyword & 39; and that the operators must be provided in Upper case.
2. The keyword must be pre-fixed and suffixed with a sign.
3%. Whenever content is changed or updated, & 39; n & 39; index is not updated automatically. It follows the line that would be executed after each " Save " process of updating the index.
CTX DDL.SYNC INDEX (index) Author: By CJDerasari CJDerasari works as a project manager at Semaphore Infotech Pvt. Ltd, India. He has over 16 years of experience & 39;. You can contact on email: cjd@semaphore-software.com.



Bookmark it: