EXCEPTION inside FOR..LOOP

[raseena nm]

HI,
declare
cursor c1 is
select sid,scity
from supplier;
wrong_city exception;
begin
for s_rec in c1 loop
if s_rec.scity = 'bangalore' then
raise wrong_city;
end if;
end loop;
exception
when wrong_city then
dbms_output.put_line('wrong city');
end;
result:
SQL> /
wrong city
PL/SQL procedure successfully completed.

Here in my table 'SUPPLIER' i've more than 1 record which has the value 'BANGALORE'. But this code ends as sson as it got one record with the value 'BANGALORE'.
I want continue the loop until all the records are checking.
How can i go back to that FOR ..LOOP after fetching and displaying the exception?

can anybody give me the solution?
thanx .

[Mark Spritzler]

When it equals Bangalore don't raise an expection. This will automatically send you to the Exception section. Now if you must raise this then just put a BEGIN/EXCEPTION/END around that part of the code. This way it will catch the exception, then continue after the END, which will be your remaining code, and stays inside the LOOP.
It looks like all you want to do is send the DBMS output. Why not just put that one line in the THEN of the IF statement.
putting the output in the If statement
This is the one I prefer.

 declare
cursor c1 is
select sid,scity
  from supplier;
begin
for s_rec in c1 loop
if s_rec.scity = 'bangalore' then
dbms_output.put_line('wrong city');
end if;
end loop;
exception
when others then
dbms_output.put_line('other exception');
end; 

If you must raise an exception.

 
declare
cursor c1 is
select sid,scity
  from supplier;
wrong_city exception;
begin
for s_rec in c1 loop
BEGIN
if s_rec.scity = 'bangalore' then
raise wrong_city;
end if;
EXCEPTION
when wrong_city then
dbms_output.put_line('wrong city');
END;
end loop;
exception
when others then
dbms_output.put_line('other exception');
end; 

Hope that helps
Mark

[Mark Spritzler]

Raseena, also, there is a "CODE" tag here that can keep you code formatted with indentation and everything and makes the code much more easier to read when you post it.
Thanks
Mark

[raseena nm]

tanQ Mark for your answer.
But can you tell me how to use the 'CODE' tag that u've mentioned in the reply?
Im getting Invalid SQL statement while giving
CODE
above procedure.
thanx.

[Mark Spritzler]

When you post a reply in any thread here at JavaRanch there are buttons under the Add Reply button.
Buttons like URL, EMAIL, BOLD, ITALICS, QUOTE, CODE, LIST-START, etc... These buttons are to the right of the Smiley's. When you want to post code in this forum. Click the CODE button and it will put tags in the Reply section that you can paste your code in between and they will look nice. Just like my reply above with the code.
This tag is only for the forums here, it has nothing to do with Oracle or Stored Procedures.
Mark

[raseena nm]

tatz funny.
thanx.