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.

Thanks in advance.
Peter

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

"Martin T." <bilbothebagginsbab5 (AT) freenet (DOT) de> schrieb im Newsbeitrag
news:1162472305.225648.113800 (AT) b28g2000cwb (DOT) googlegroups.com...
Thanks to Martin

Acts unfortunately not as expected.

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

oracle version is 10g

Regards
Peter

Martin T. schreef:
Must be pre-8.0, because Oracle advertises to use LOB's, not longs
since the end of 7.3. The use of char(6) as primary key also makes
me wonder.

And when were varchar2(4000) columns introduced?

--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

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

"Martin T." <bilbothebagginsbab5 (AT) freenet (DOT) de> schrieb im Newsbeitrag
news:1162542003.960987.301590 (AT) b28g2000cwb (DOT) googlegroups.com...

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

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