Re: how to copy data from long to varchar2

From: Martin T. <bilbothebagginsbab5_at_freenet.de>
Date: 6 Nov 2006 05:48:38 -0800
Message-ID: <1162820918.185413.8930@m7g2000cwm.googlegroups.com>

Peter Keckeis wrote:
> "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.

>

Ah ok. My test data wasn't that long.
It's a bit weird though that you get empty when the long are longer than the varchar2 instead of an error. Whatever :)

best,
Martin Received on Mon Nov 06 2006 - 07:48:38 CST