Re: how to copy data from long to varchar2

From: Martin T. <bilbothebagginsbab5_at_freenet.de>
Date: 3 Nov 2006 00:20:04 -0800
Message-ID: <1162542003.960987.301590@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 Received on Fri Nov 03 2006 - 02:20:04 CST