Home » SQL & PL/SQL » SQL & PL/SQL » continue after exception
continue after exception [message #255887] |
Thu, 02 August 2007 05:03  |
rzkhan
Messages: 370 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   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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   |
rzkhan
Messages: 370 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
|
|
|
|
|
Goto Forum:
Current Time: Sat Feb 15 02:21:48 CST 2025
|