| roduction | | | | CREATE INDEX Content_Tbl_Index ON |
| In the present era of Knowledge based computer | | | | Content_Tbl (Text_Content) INDEXTYPE IS |
| applications, there is often a need to store a large | | | | CTXSYS.CONTEXT; |
| amount of text based data into the Database. | | | | Now, user can search for all content with multiple |
| Moreover, the user also needs facility to search this | | | | combinations of Keywords and operators, for |
| text for specific key-words. There are several ways | | | | example: |
| to provide search functionality. | | | | SELECT Content_Desc FROM Content_Tbl |
| If the Oracle database is used in the application, then | | | | WHERE CONTAINS (Text_Content, '%OUT |
| such massive text data can be stored into Oracle | | | | SOURCING%’ OR %SOFTWARE |
| database as CLOB (Character Large Object) data | | | | DEVELOPMENT%’ OR %WEB SITE%' ) |
| type. Oracle 10g supports storing of character data up | | | | >0; |
| to 4 GB in CLOB. | | | | This query will return all the rows from the database |
| Oracle Text provides a powerful text search and text | | | | with the column TEXT_CONTENT containing any of |
| management for Oracle 10g database. Oracle Text | | | | the key words Out Sourcing’, Software |
| indexes the text content for fast & accurate | | | | Development’, Web Site’. |
| retrieval of information. | | | | Concerns |
| Usage | | | | While using the Oracle Text search features, I have |
| Let me now illustrate how to use Oracle Text for | | | | observed that the following points are to be kept in |
| searching in CLOB data into Oracle 10g database. | | | | mind for getting proper search results: |
| There is a table in the database which is having a | | | | 1. The keyword as well as join operators are to be |
| large text content stored as CLOB. | | | | provided in Upper case. |
| Table : CONTENT_TBL | | | | 2. The keyword should be pre-fixed and suffixed by a |
| CONTENT_ID NUMBER(6) | | | | % sign. |
| CONTENT_DESC VARCHAR2(100) | | | | 3. Whenever the content is changed or updated, the |
| TEXT_CONTENT CLOB | | | | index does not updated automatically. It follows the ing |
| Oracle Text requires an index to be created ion the | | | | line which needs to be executed after each |
| field which is to be searched for. So, create an index | | | | Save process to update the index. |
| named Content_Tbl_Index as : | | | | |