how to search a long column?

[topstar]

How do I search a long column for a text string in it?

I obviously cannot use "LIKE" because sql gives an error

ORA-00932: Inconsistent datatypes

[pando]

right now I can think of writing a function which convert long to varchar2 then in your where clause use this function

[topstar]

Surely, Oracle should have something to help us search for strings embedded in LONG columns?

No?

Otherwise what is the use of only storing data in LONG if you cannot search/retrieve anything in/from it?

can anyone tell me if there are any functions I could use to work with long columns?

[oddster]

I think this question would be more appropriate in the developers forum.

My answer would be 'DON'T USE LONGS!'

They cause endless grief to us dbas!

[julian]

Originally posted by topstar

Surely, Oracle should have something to help us search for strings embedded in LONG columns?

No?

Otherwise what is the use of only storing data in LONG if you cannot search/retrieve anything in/from it?

can anyone tell me if there are any functions I could use to work with long columns?

In Oracle 8.1.7 and 9i you can index LONG columns with Oracle Text. It is a core feature of the DB.

So, I suggest you use the new CONTEXT OPTION, first it has to be installed and configured in your server.