You can achieve with GoTo..
Whenever you expect a No Data Found exception, just ignore the rest of logic in For Loop and go straight to the End Loop.
Using GoTO is not a very good programming practice but may be quite handy sometimes.
For X in my_cursor
Loop
Select col1 into var1 from my_table
where my_table.id = X.id
Exception
When No Data Found
Goto << loopend >> --<-- force the loop to reiterate
End;
--some more for loop logic here
<<loopend>>
End Loop;
Rgds,
HR
Main Topics
Browse All Topics
by: RCorfmanPosted on 2006-03-28 at 20:22:08ID: 16317952
Yes to both... sort of.
First, for the continuing piece. Inside the loop that is retrieving the cursor, you can put an exception block, handle the excpetion, then just continue on. Depending on the exception, you can either re-raise it, or handle it.
For example:
for x in my_cursor loop
begin
-- do some stuff here that could fail that you want to capture
exception
when any_particular_exception then
-- handle the excpetion
end;
-- now we will continue on here even though we had an exception and continue with the loop
end loop;
For the other case, restarting the cursor, you just put the cursor loop inside another loop.
Be careful though, you usually want your routine to exit at some point....
(I like cursor loops, but the same would hold with open,fetch,close loops...
found_data := 0;
while found_data = 0 loop
for x in my_cursor loop
found_data := 1;
process_here;
end loop;
end loop;