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.

luke.pickett@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