LONG to clob/varchar2(4000)

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. Received on Fri Dec 15 2006 - 12:52:19 CST