Sql scripts
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Sql scripts

  1. #1
    Join Date
    Jul 2005
    Posts
    1

    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;
    /

  2. #2
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    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.
    Last edited by waitecj; 07-07-2005 at 11:46 AM.
    Assistance is Futile...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width