search long data type

[bergis]

Hi, I have a table with a field that contains description of ongoing operations. This is a long data type field (bear with me, I'm not well into all the correct terminology..).
I want to search this field for occurrences of spesific words. I've done this in Access, syntax is like this:

WHERE OPSUM.OPER LIKE "*word*"

But i want to do the same thing in the oracle database (have done it in access after linking the data in). I've tried different syntaxes (i.e replacing "* with other symbols like '%) but all I get is different variants of this error message: ORA-0932: Inconsistent data types.

So, does anyone know of a simple way of searching an text string in a field in an oracle db for spesific words?

thanks
Kjell

 

[malleykr]

Kjell,

The problem you are running into is the LONG datatype. There are many restrictions concerning LONG datatypes that do not occur with other text datatypes such as VARCHAR2. One of these restrictions is that a LONG field cannot occur in the WHERE clause of a select statement.

For more information about restrictions involving long datatypes, follow this link:

http://pwww.eh.pweh.com/mis/db-cs/html/ora8doc/DOC/server.805/a58225/ch2.htm#3612

You can search the field in Access because Access is more limited. It does not have LONG datatypes, so it just treats the contents as text.

Due to the restrictive nature of the LONG datatype, I am not sure it will be possible for you to use Oracle itself to search the field. However, if you are looking to search the field for a particular application, it is possible that you could simply select the LONG data type and search it in the application instead of in the database to find what you are looking for.

Good luck!
KR

 

[bergis]

thanks, that clears that up, just have to work around it.

I have worked with oracle tables linked into access, and then I can search the OPER column, in access it appears as MEMO data type, and it also seems that access searches all of the field as it is in oracle, i.e. I can search for a spesific word and in the result I see all the OPER fields that contain this word, but the word I searched for isn't always visible in the text that I see. (I know this explanation is a bit 'clouded').

Its all clear to me now..

Kjell