Dear mas Fansul,
Sepertinya perintah commit bisa diletakkan dipaling akhir. Untuk mencegah
ketika terjadi gangguan pada koneksi terputus ditengah jalan sehingga data
tidak timpang karena ada salah satu proses yang sudah commit duluan
sedangkan proses satunya belum berjalan karena terlanjur koneksi putus.
Mungkin kalau dari saya suggestnya seperti ini :
create or replace procedure insert_data_cabang
is vcount smallint;
begin
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 ; remarked by alwin
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; remarked by alwin
EXECUTE IMMEDIATE 'ALTER SESSION CLOSE DATABASE LINK CabA';
commit; -- added by alwin
end;
Sepertinya, data kadang2 tidak terupdate karena ada masalah pada koneksi
ditengah2 proses, sehingga proses pertama selesai tapi proses selanjutnya
belum selesai.
Waallahu'alam :)
Salam,
Alwin.S
www.oblongware.com
Oblongware - Kaos Oblongnya Penggemar IT
On Thu, Apr 15, 2010 at 2:59 PM, Rudy W Sitanggang <
[email protected]> wrote:
> saya ngk ngeliat detail sih bro,
>
> kalo kalo gue liat sekilas, mending commit nya di akhir aja bro..
>
>
> biar aman semua proses terlaksana baru di commit
>
> On Thu, Apr 15, 2010 at 7:31 AM, <[email protected]> wrote:
>
> >
> >
> > Hello ,
> >
> > coba kirim ulang kenapa nggak masuk ke forum
> >
> > 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] <fansul%40yahoo.co.id>
> >
> > __________________________________________________
> > Apakah Anda Yahoo!?
> > Lelah menerima spam? Surat Yahoo! memiliki perlindungan terbaik terhadap
> > spam
> > http://id.mail.yahoo.com
> >
> >
> >
>
>
>
> --
> Warm regards,
>
>
> Rudy Wardhany
>
>
> Yahoo ID: [email protected]
>
>
>
[Non-text portions of this message have been removed]
