-
Sql scripts
hi This pankaj
i am facing a problem ...........
whenever i run the folowing script system hangs after clearing two stage.
please suggest what is wrong with this and is it anathor way to do it
declare
bal_amt number:=0;
tmp_col_no number:=0;
tmp_inv_no number:=0;
cursor opn is
select distinct tmp_pty_cd pty_cd,-79376.24 opn from tmp_os
where to_char(tmp_date,'yyyy')=2005
and tmp_pty_cd='A057';
begin
for c2 in opn loop
bal_amt:=c2.opn;
loop
if bal_amt > 0 then
declare
cursor inv is
select inv_date,inv_no,inv_net_value from p1
where inv_pty_cd=c2.pty_cd
and to_char(inv_date,'yyyymm')=200505
and inv_prod_type!='A'
order by 1,2;
begin
for i in inv loop
dbms_output.put_line(bal_amt);
bal_amt:=bal_amt-i.inv_net_value;
update p1
set inv_prod_type='A'
where inv_no=i.inv_no;
commit;
exit when bal_amt <0;
end loop;
end;
else
declare
cursor coll is
select col_date,col_no,col_amount from p
where col_pty_cd=c2.pty_cd
and to_char(col_date,'yyyymm')=200505
and col_micr_tag!='A'
order by 1,2;
begin
for c in coll loop
dbms_output.put_line(bal_amt);
bal_amt:=bal_amt+c.col_amount;
update p
set col_micr_tag='A'
where col_no=c.col_no;
commit;
exit when bal_amt >0;
end loop;
end;
end if;
commit;
end loop;
end loop;
end;
/
-
first of all, edit your post by wrapping the code with the [ code ] and [ / code ] tags so its readable, then trace the session that is running the code and tell us what you see.