我有一个目标模式adresse,其中plz是一个数字,还有一个源模式,其中plz是varchar2。
下面的代码抛出一个ORA-01722无效的数字错误,但我无法确定原因。大约有30个plz,如'26189‘或'38108’和5个空值。
create or replace
procedure f1_get_adresses is
cursor c_adresse is
select id, strasse, hausnummer, to_number(to_nchar(postleitzahl))
as postleitzahl, ort, land
from db2_lsg2.f1_adresse;
rec c_adresse%rowtype;
counter number(10);
val_plz number(10);
begin
for rec in c_adresse
loop
-- PLZ
select count(*) into counter from postleitzahl
where plz = rec.postleitzahl and ort = rec.ort;
if counter = 0 then
if rec.postleitzahl is null then
val_plz := 0;
else
val_plz := rec.postleitzahl;
end if;
insert into postleitzahl (plz, ort) values (rec.ort, val_plz);
end if;
-- Land
select count(*) into counter from land
where land_name = rec.land;
if counter = 0 then
insert into land (land_name) values (rec.land);
end if;
--Adresse
select count(*) into counter from adresse
where strasse = rec.strasse
and hausnummer = rec.hausnummer
and plz = rec.postleitzahl
and (select land_name from land where land_name = rec.land) = rec.land;
if counter = 0 then
insert into adresse (strasse, hausnummer, plz, land_id)
values (
rec.strasse,
rec.hausnummer,
rec.postleitzahl,
(select land_id from land where land_name = rec.land)
);
end if;
end loop;
end;发布于 2012-03-09 11:11:19
你给我们的细节不多,让我们猜猜.rec.ort的类型是什么?
insert into postleitzahl (plz, ort) values (rec.ort, val_plz); 不应该是(不同的顺序)
insert into postleitzahl (plz, ort) values (val_plz, rec.ort); ?https://stackoverflow.com/questions/9632097
复制相似问题