Installing the Context Cartridge

One of the disadvantages of using LONG or RAW columns in Oracle is that they're difficult to search using conventional means and you cannot create indexes on them in the usual manner (using the CREATE INDEX statement).

Oracle's ConText cartridge solves this problem. Using the ConText Option allows the creation of indexes on LONG columns which in turn allows them to be searched

Installing Oracle ConText:

Well, if you're here, you probably know all that, you just want to know how to install this thing. The Steps for installing Context are:

1. Install oracle ConTEXT Cartride using Oracle Installer.
   
   
2. Create a separate tablespace for ConText minimum 100M.
   
   
3. Run creation script for creating context user CTXSYS,drcsyst.sql in $ORACLE_HOME/ctx/admin. Must run as user SYS.
   
   The script will prompt you for four values of the user to create:
   
   [username] [password] [default tablespace] [temp tablespace]
   
   Set the default tablespace to be one created in step 2.
   
   
4. Create context objects - run script
   
      $ORACLE_HOME/ctx/admin/dr0inst
   
      as CTXSYS user.
   
   
5. Run script drlngset.sql as CTXSYS to set up linguistics tables.
   
   
6. Add TEXT_ENABLE=TRUE ininit<SID>.ora file.
   
   
7. At this point,ConTexT is essentially setup. The next steps you'll have to do as the schema owner of the tables that you want to runConText on.
   
   
8. Grant the ConTexT Admin privilege to the object schema owner. (i.e. GRANT CTXADMIN to <Schemaowner>;)
   
   
9. Start Context Service: on unix shell run:
   
   ctxsrv -user ctxsys/<ctxsys pw> -personality QDML
   
   Note: this process must be running for ConTexT to work.
   
   
10. As <SCHEMA Owner> create the following policy & index.
    
    EXECUTECTX_DDL.CREATE_POLICY('<policyname>','<tablename>.<column>');
    EXECUTE CTX_DDL.CREATE_INDEX('<policyname>');
    
    For example,say you have the following table DDL:
    
    

       CREATE TABLE MESSAGE
       (
         MESSAGE_ID NUMBER,
         TEXT LONG
       );

    And you want to enable ConTexT to search the text column.
    You would then run the following(after granting CTXADMIN to this table's schema owner):
    
    Note that the policy name can by anything you want as long as it is unique.
    
    EXECUTE CTX_DDL.CREATE_POLICY('MY_POLICY','MESSAGE.TEXT');
    EXECUTE CTX_DDL.CREATE_INDEX('MY_POLICY');
    
    Note that the ctxsrv process MUST first be running for this to work.
    
    

11. At this point, you are all set to perform searches on the TEXT column!
    
    Once you get everything setup, you'll probably want to put Step 8 into the background, and redirect output to /dev/null2>&1

---

EXECUTING SEARCHES:

Once CONTEXT is running, you are set to perform searchs on LONG Columns. The Syntax is as follows:
SELECT * FROM <TABLE> WHERECONTAINS(<LONG COLUMN>,'<SEARCHPHRASE>') >0

Using the MESSAGE table above as an example, the syntax for a search on the TEXT column for the phrase 'DBA Service" would be as follows:

SELECT * FROM MESSAGE WHERE CONTAINS(TEXT,'DBA Service') > 0;