Re: ORA-00932 Cannot do a like against a long datatype

Longs are deprecated, and have been since 8.0. They shouldn't be used in an 8.1.7 database.

Not much help, I suppose. But gets to the essential point. Presumably you have some constraints preventing you from migrating to LOBs?

Regards
HJR "Michael McGarrigle" <mjm_at_barwonwater.vic.gov.au> wrote in message news:9d0cafdc.0205272001.1d0eab92_at_posting.google.com...
> When I try to perform a like against a long datatype I get error
ORA-00932.
> The data in the long field will typically have up to 64000 characters
> I found a function which I thought would fix the problem as follows :
>
> create or replace fn_doc_text(wk_doc_id number) return varchar2
> wk_desc2 varchar2(64000);
> v_long long;
> begin
> select doc_text into v_long from documents
> where doc_id = wk_doc_id;
> wk_desc2 := substr(v_Long,1,64000);
> return(wk_desc2);
> end;
>
> This function will not compile because of the limitation in size for
varchar2.
>
> I have also tried using the dbms_lob.instr() function but this does not
> work against long datatypes.
>
> We are running version 8.1.7
>
> I would appreciate any thoughts as to how this could be achieved.
>
> TIA
Received on Mon May 27 2002 - 23:16:43 CDT