I have an 9i database on Windows 2003 with some mutli-line text stored
in a LONG field. I want to seatch this LONG field with a string.

SQL> desc multiline_text;

ENTITY_DBID NUMBER(10)
ENTITY_FIELDDEF_ID NUMBER(10)
DATA LONG

SQL> select multiline_text.data
from multiline_text
where cast(multiline_text.data AS VARCHAR2(132)) like '%Done.' ;

ORA-00932: inconsistent datatypes

How can I convert LONG to a searchable string?

TIA

I do not have a system in front on me to test with but I am fairly
sure that the to_clob function will convert a long datatype to a clob
datatype then you can use dbms_lob functions on the column.

Neither the 9.2 or 10.2 SQL manual entries on to_clob list long as a
valid datatype to the function but it takes every other kind of
character data so it should work.

HTH -- Mark D Powell --

Harry331 wrote:
> I have an 9i database on Windows 2003 with some mutli-line text stored
> in a LONG field. I want to seatch this LONG field with a string.
>
> SQL> desc multiline_text;
>
> ENTITY_DBID NUMBER(10)
> ENTITY_FIELDDEF_ID NUMBER(10)
> DATA LONG
>
> SQL> select multiline_text.data
> from multiline_text
> where cast(multiline_text.data AS VARCHAR2(132)) like '%Done.' ;
>
> ORA-00932: inconsistent datatypes
>
> How can I convert LONG to a searchable string?
>
> TIA

Morgan's Library at www.psoug.org
Click on LONG To CLOB
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org