Re: how to copy data from long to varchar2

From: Peter Keckeis <no_spam_peter.keckeis_at_11er.at>
Date: Mon, 6 Nov 2006 08:41:42 +0100
Message-ID: <90520$454ee739$c2d02602$7533@news.hispeed.ch>

"Martin T." <bilbothebagginsbab5_at_freenet.de> schrieb im Newsbeitrag news:1162542003.960987.301590_at_b28g2000cwb.googlegroups.com...

> Peter Keckeis wrote:

>> "Martin T." <bilbothebagginsbab5_at_freenet.de> schrieb im Newsbeitrag
>> news:1162472305.225648.113800_at_b28g2000cwb.googlegroups.com...
>> > Peter Keckeis wrote:
>> >> Hello all,
>> >>
>> >> I would like a way to copy the data from l_txt to vc_txt with
>> >> sql*plus.
>> >>
>> >> create table test (pk char(6), vc_txt varchar2(2000), l_txt long,
>> >> constraint testpk primary key(pk))
>> >>
>> >> Using
>> >>
>> >> update test set vc_txt = l_txt;
>> >> or
>> >> update test set vc_txt = to_char(l_txt);
>> >>
>> >> generates an ORA-00932 error.
>> >>
>> >> Can you tell me please the right update statement.
>> >>
>> >
>> > You might want to post your Oracle version when asking around here :)
>> >
>> > Hmm .... can't get this to work on 9i2 either, but the docs don't seem
>> > to say it wouldn't work.
>> >
>> > Work-around:
>> >
>> > begin
>> > for reco in (
>> > select *
>> > from test
>> > for update
>> > ) loop
>> > update test set vc_txt = reco.l_txt;
>> > end loop;
>> > end;
>> >
>> > br,
>> > Martin
>> >

>>

>> Thanks to Martin

>>

>> Acts unfortunately not as expected.

>>

>> PL/SQL procedure execute successfull, but vc_txt columns are emtpy.

>>

>> oracle version is 10g

>>
>
> Hm. Well I tried the script on my 9i2 and the values were copied as
> expected ... but of course I only had a very small amount of test data.
> Can you log the long values read via dbms_output in the script?
> How long are your Longs anyway?
> And, is this a one-off thing you are trying here? As Frank said it
> might be a good idea to get rid of that long column.
>
> cheers,
> Martin
>

Hi Martin,

i made the following changes in the PL/SQL Script:

begin
  for reco in (
    select pk,l_txt
    from test
    for update
  ) loop
    update test set vc_txt = substr(reco.l_txt,1,2000) where pk = reco.pk;   end loop;
end;

(pk = primary key field)

and now, it works fine.

cheers,
Peter Received on Mon Nov 06 2006 - 01:41:42 CST