• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

EXCEPTION inside FOR..LOOP

 
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 .
 
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.


If you must raise an exception.


Hope that helps
Mark
 
Mark Spritzler
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
tatz funny.
thanx.
 
Ew. You guys are ugly with a capital UG. Here, maybe this tiny ad can help:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic