Re: Long to varchar2?

In article <834780575.14996.1_at_lantis.demon.co.uk>, Afshin Ghafoori <ash_at_lantis.demon.co.uk> writes
>3bohlman_at_rzdspc5.informatik.uni-hamburg.de (Holger Bohlmann) wrote:
>
>>Hello,
 

>>is there anybody out there, who knows, how I can convert a
>>LONG-Value in a VARCHAR2-Value (in Oracle?). Is it possible
>>to do that?
 

>>Holger Bohlmann
>
>
>It is possible to convert them using a rather crude trick:
>Create a form in Forms 3.x and query-up the data into the form field
>(lets say :blk.dat), then using PL/SQL inside the form, you can
>reference :blk.dat and carryout functions on it (e.g., SUBSTR or
>whatever), you can now write routines to insert the database...
>
>Don't forget to define you :blk.dat field as a LONG with a length of
>65000 (approx). The drawback is that it can only take about 64K from
>the beginning of the field...
>
>

Hi ..
VARCHAR2 limit is 2000 - try using a function

insert into new_table (pk,longvalue_as_varchar2) SELECT pk,function(pk) from tableX

where function may be

CREATE FUNCTION function(pk in number)

       return char is

cursor c1 select long_value from tableX where the_primary_key = pk

outy varchar2(2000);

begin
open c1;
fetch c1 into outy;
close c1;
return outy;
end;

(IF loading/selecting from remote databases, use two database links one for the insert/select and one in the function else sqlnet falls over with memory problems (well it does on 7.1.6 of the kern. anyway ..))

good luck

Neill Atkins
Portsmouth, UK. neill_at_firstwrd.demon.co.uk Received on Sat Jun 15 1996 - 00:00:00 CEST