aspose file tools*
The moose likes Oracle/OAS and the fly likes EXCEPTION inside FOR..LOOP Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "EXCEPTION inside FOR..LOOP" Watch "EXCEPTION inside FOR..LOOP" New topic
Author

EXCEPTION inside FOR..LOOP

raseena nm
Greenhorn

Joined: Nov 07, 2003
Posts: 25
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
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17259
    
    6

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


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17259
    
    6

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

Joined: Nov 07, 2003
Posts: 25
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
Sheriff

Joined: Feb 05, 2001
Posts: 17259
    
    6

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

Joined: Nov 07, 2003
Posts: 25
tatz funny.
thanx.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: EXCEPTION inside FOR..LOOP