Re: How to search a LONG column

In article <352076cb.308157446_at_news.earthlink.net>, Brian Everett <beverett_at_remove_this.usa.net> wrote:

>You may want to consider the Context Option.
>I store many forms of documents in Long fields that are indexed for
>all types of searches.

This is one way to go.

>>According to the docs you can't reference a LONG column in a WHERE clause,
>>one of the many restrictions with LONGS!
>>
>>Part of our application allows users to set filter conditions including
>>the ability to word search, is there any way we can word search text
>>stored in a LONG?

Another thing Oracle recommends (I read this in one of the tuning books, possibly OReilly Tuning, or Oracle DBA Handbook) is to create another table for that data, and store it line by line, with a foreign key to the primary key of the other table. Each line is stored in a varchar2. This is how PL/SQL source is stored in the the data dictionary...

Sean

• cut here --------------

SQL> column name format a15
SQL> column text format a60
SQL> set pagesize 38
SQL> set pause on
SQL> select name, text from all_source;


NAME            TEXT
--------------- ------------------------------------------------------------
DBMS_ALERT      package dbms_alert is
DBMS_ALERT
DBMS_ALERT        ------------
DBMS_ALERT        --  OVERVIEW
DBMS_ALERT        --
DBMS_ALERT        --  This package provides support for the asynchronous (as