Hello ,
Saya punya script dibawah ini, dari pusat ambil data cabang.
saya tidak tahu salahnya di mana, yang terjadi sekarang kadang2 data
belum terupdate di pusat, tapi status di cabang sudah terupdate
status menjadi 'Y'
Script ini udah di jalankan
// update jual_i...@caba z set z.status = 'Y'
where trim(i.nomor) = trim(z.nomor) ;
sedangkan scipt ini gagal
if vcount > 0 then
update jual_global x set x.nilai = nvl(i.nilai,0)
where trim(x.nomor) = trim(i.nomor) ;
else
insert into jual_global x
( x.nomor, x.nilai ) values ( i.nomor, i.nilai );
end if ;
scipt lengkapnya di bawah ini, mohon solusi yang lebih baik
tks
create or replace procedure insert_data_cabang
is
vcount smallint;
begin
---- Hapus data yang ada perubahan -----
for i in
(
select a.nomor from jual_...@caba a where a.status = 'N'
) loop
vcount := 0 ;
select count(*) into vcount from jual_global b
where trim(b.nomor) = trim(i.nomor)
if vcount > 0
then
delete jual_global x
where
trim(b.nomor) = trim(i.nomor) ;
update jual_...@caba z
set z.status = 'Y'
where
trim(i.nomor) = trim(z.nomor) ;
end if;
end loop;
commit ;
-------------------
for i in
(
select
c.nomor,
sum(c.nilai) as nilai
from
jual_i...@caba c
where
c.status = 'N'
group by
c.nomor
)
loop
vcount := 0;
select
count(*)
into
vcount
from jual_global x
where
trim(x.nomor) = trim(i.nomor) ;
if vcount > 0
then
update jual_global x
set x.nilai = nvl(i.nilai,0)
where
trim(x.nomor) = trim(i.nomor) ;
else
insert into jual_global x
(
x.nomor,
x.nilai
)
values
(
i.nomor,
i.nilai
);
end if ;
update jual_i...@caba z
set z.status = 'Y'
where
trim(i.nomor) = trim(z.nomor) ;
end loop;
commit;
EXECUTE IMMEDIATE 'ALTER SESSION CLOSE DATABASE LINK CabA';
end;
--
Best regards,
fansul
mailto:[email protected]
__________________________________________________
Apakah Anda Yahoo!?
Lelah menerima spam? Surat Yahoo! memiliki perlindungan terbaik terhadap spam
http://id.mail.yahoo.com
