Re: LONG to clob/varchar2(4000)

luke.pickett_at_gmail.com wrote:
> I know that I can cast a long to clob using to_lob() and an
> insert/create table but I do not have write access to the database only
> read access. I need to search within the long for a specific word and
> I am not sure how to do this. I know I am making this too hard and
> their is an easy answer but I don't see it. I have a couple of examples
> I have tried with no success:
>
> select ALL_TEXT
> from Long_text
> where CAST(ALL_TEXT as VARCHAR2(4000)) like '%Smith%'
>
> select ALL_TEXT
> from Long_text
> where contains(CAST(ALL_TEXT as VARCHAR2(4000)), 'Smith') >0
>
> select ALL_TEXT
> from Long_text
> where contains(to_lob(ALL_TEXT), 'Smith') >0
>
> .... and lots more. I have tried to create temp tables to store this
> but as I cannot write to the db I also get errors(maybe missing
> something there too.) Oracle version 9i.

Ask your DBA to create an Oracle Text index on ALL_TEXT column if there isn't one already. Then you will be able to search in that LONG column efficiently using CONTAINS operator:

select rowid from long_text where contains(all_text,'smith',0) > 0

Note that you don't have to cast all_text to anything for CONTAINS() to work - all it needs is a Text index on searched column.

Hth,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Sat Dec 16 2006 - 04:41:01 CST