Home » SQL & PL/SQL » SQL & PL/SQL » continue after exception
continue after exception [message #255887] Thu, 02 August 2007 05:03 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Could I continue processing next record after exception is raised?

begin

      -------- for active members having record in net
       for x in c1 loop
       update net set arrears=x.balance where member_id=x.member_id and m=mp and y=yp;
       dbms_output.put_line(x.member_id||' '||x.balance||''||mp||' '||yp);
       end loop;

      -------- for in-active members, insert a record in outstanding table
      begin
          for x2 in c2 loop
          dbms_output.put_line(x2.member_id||' '||x2.balance||''||mp||' '||yp);
          insert into outstanding (member_id,amount,outs_date,remarks) 
          values(x2.member_id,x2.balance,SYSDATE,'Graduated/Left Institute');
          end loop; 
                Exception when dup_val_on_index then
                NULL;
                dbms_output.put_line(' Already present in outstanding');

----I NEED TO PROCESS NEXT RECORD HERE
    
     end;
      -------- for active members having no record in net
      for x3 in c3 loop
      insert into net(member_id,arrears,m,y) values (x3.member_id,x3.balance,mp,yp);
      end loop; 
END;




Thank You

Riaz

[Updated on: Thu, 02 August 2007 05:12] by Moderator

Report message to a moderator

Re: continue after exception [message #255894 is a reply to message #255887] Thu, 02 August 2007 05:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Move your exception block.
begin

      -------- for active members having record in net
       for x in c1 loop
       update net set arrears=x.balance where member_id=x.member_id and m=mp and y=yp;
       dbms_output.put_line(x.member_id||' '||x.balance||''||mp||' '||yp);
       end loop;

      -------- for in-active members, insert a record in outstanding table
          for x2 in c2 loop
          dbms_output.put_line(x2.member_id||' '||x2.balance||''||mp||' '||yp);

      begin

          insert into outstanding (member_id,amount,outs_date,remarks) 
          values(x2.member_id,x2.balance,SYSDATE,'Graduated/Left Institute');

                Exception when dup_val_on_index then
                NULL;
                dbms_output.put_line(' Already present in outstanding');
     end;

          end loop; 

      -------- for active members having no record in net
      for x3 in c3 loop
      insert into net(member_id,arrears,m,y) values (x3.member_id,x3.balance,mp,yp);
      end loop; 
END;

Regards
Michel

[Updated on: Thu, 02 August 2007 05:16]

Report message to a moderator

Re: continue after exception [message #255898 is a reply to message #255887] Thu, 02 August 2007 05:22 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Moving the block creates errors

SQL> create or replace procedure bbf4      (mp IN char, yp IN number) as
  2    balance_month varchar2(3);
  3    balance_year number(4);
  4  BEGIN
  5    ------- GET LAST MONTH AND YEAR (IF LAST MONTH IS JAN change year also
  6  balance_month:=to_char(add_months(to_date(mp||yp,'MONyyyy'),-1),'MON');
  7  balance_year:=to_char(add_months(to_date(mp||yp,'MONyyyy'),-1),'yyyy');
  8  DECLARE
  9  ----------------fetch balance for the last month for ACTIVE members and member_id found in NET
 10     cursor c1 is select net.member_id,balance,m,y,status
 11     from net,members
 12     where net.member_id=members.member_id
 13     and status=1
 14     and m=balance_month and y=balance_year
 15     and net.member_id IN(select member_id from net where m=mp and y=yp);
 16  ----------------fetch balance for the last month for ACTIVE members and and member_id  not found in NET
 17     cursor c3 is select net.member_id,balance,m,y,status
 18     from net,members
 19     where net.member_id=members.member_id
 20     and status=1
 21     and m=balance_month and y=balance_year
 22     and net.member_id NOT IN(select member_id from net where m=mp and y=yp);
 23  ---------------fetch balance for the last month for IN-ACTIVE members
 24     cursor c2 is select net.member_id,balance,m,y,status
 25     from net,members
 26     where net.member_id=members.member_id
 27     and status=0
 28     and net.m=balance_month and y=balance_year;
 29  begin
 30        -------- for active members having record in net
 31         for x in c1 loop
 32         update net set arrears=x.balance where member_id=x.member_id and m=mp and y=yp;
 33         dbms_output.put_line(x.member_id||' '||x.balance||''||mp||' '||yp);
 34         end loop;
 35        -------- for in-active members, insert a record in outstanding table
 36        begin
 37            for x2 in c2 loop
 38              dbms_output.put_line(x2.member_id||' '||x2.balance||''||mp||' '||yp);
 39              insert into outstanding (member_id,amount,outs_date,remarks) 
values(x2.member_id,x2.balance,SYSDATE,'Graduated/Left Institute');
 40            end loop;
 41       end;
 42        -------- for active members having no record in net
 43        for x3 in c3 loop
 44        insert into net(member_id,arrears,m,y) 
values (x3.member_id,x3.balance,mp,yp);
 45        end loop;
 46  END;
 47  END;
 48  /

Procedure created.

SQL> ed
Wrote file afiedt.buf

  1  create or replace procedure bbf4      (mp IN char, yp IN number) as
  2    balance_month varchar2(3);
  3    balance_year number(4);
  4  BEGIN
  5    ------- GET LAST MONTH AND YEAR (IF LAST MONTH IS JAN change year also
  6  balance_month:=to_char(add_months(to_date(mp||yp,'MONyyyy'),-1),'MON');
  7  balance_year:=to_char(add_months(to_date(mp||yp,'MONyyyy'),-1),'yyyy');
  8  DECLARE
  9  ----------------fetch balance for the last month for ACTIVE members and member_id found in NET
 10     cursor c1 is select net.member_id,balance,m,y,status
 11     from net,members
 12     where net.member_id=members.member_id
 13     and status=1
 14     and m=balance_month and y=balance_year
 15     and net.member_id IN(select member_id from net where m=mp and y=yp);
 16  ----------------fetch balance for the last month for ACTIVE members and and member_id  not found in NET
 17     cursor c3 is select net.member_id,balance,m,y,status
 18     from net,members
 19     where net.member_id=members.member_id
 20     and status=1
 21     and m=balance_month and y=balance_year
 22     and net.member_id NOT IN(select member_id from net where m=mp and y=yp);
 23  ---------------fetch balance for the last month for IN-ACTIVE members
 24     cursor c2 is select net.member_id,balance,m,y,status
 25     from net,members
 26     where net.member_id=members.member_id
 27     and status=0
 28     and net.m=balance_month and y=balance_year;
 29  begin
 30        -------- for active members having record in net
 31         for x in c1 loop
 32         update net set arrears=x.balance where member_id=x.member_id and m=mp and y=yp;
 33         dbms_output.put_line(x.member_id||' '||x.balance||''||mp||' '||yp);
 34         end loop;
 35        -------- for in-active members, insert a record in outstanding table
 36        begin
 37            for x2 in c2 loop
 38              dbms_output.put_line(x2.member_id||' '||x2.balance||''||mp||' '||yp);
 39              insert into outstanding (member_id,amount,outs_date,remarks) 
values(x2.member_id,x2.balance,SYSDATE,'Graduated/Left Institute');
 40   Exception when dup_val_on_index then
 41                  NULL;
 42                  dbms_output.put_line(' Already present in outstanding');
 43            end loop;
 44       end;
 45        -------- for active members having no record in net
 46        for x3 in c3 loop
 47        insert into net(member_id,arrears,m,y) values (x3.member_id,x3.balance,mp,yp);
 48        end loop;
 49  END;
 50* END;
 51  /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE BBF4:

LINE/COL ERROR                                                                                      
-------- -----------------------------------------------------------------                          
40/2     PLS-00103: Encountered the symbol "EXCEPTION" when expecting one                           
         of the following:                                                                          
         begin declare end exit for goto if loop mod null pragma raise                              
         return select update while <an identifier>                                                 
         <a double-quoted delimited-identifier> <a bind variable> <<                                
         close current delete fetch lock insert open rollback                                       
         savepoint set sql execute commit forall                                                    
         <a single-quoted SQL string>                                                               
                                                                                                    
44/6     PLS-00103: Encountered the symbol "END" when expecting one of the                          
         following:                                                                                 
         begin function package pragma procedure form                                               
                                                                                                    
SQL> 
SQL> spool off




riaz

[Updated on: Thu, 02 August 2007 05:28] by Moderator

Report message to a moderator

Re: continue after exception [message #255900 is a reply to message #255898] Thu, 02 August 2007 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't you see I put "begin" and "end" INSIDE the loop.

And take care your lines don't exceed 80 characters.

Regards
Michel
Re: continue after exception [message #255906 is a reply to message #255887] Thu, 02 August 2007 05:42 Go to previous message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Thank You,

Riaz
Previous Topic: how to list all existing users of database
Next Topic: Mutating
Goto Forum:
  


Current Time: Mon Dec 05 21:02:58 CST 2016

Total time taken to generate the page: 0.06473 seconds