PLSQL Exception 'When Others' handling

Hi all

I have a cursor that retrieves data.  However when an exception occures e.g. WHEN NO DATA FOUND  the script will stop or if there is a problem with the current line of data it will stop and raise an exception.

Is there any way of getting the cursor to restart again at the next row of data in the case of a problem with the current line of data  (exception raised)?

Is there any way of getting the cursor to restart again completely when no data found exception is raised

Who is Participating?
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
  -- do some stuff here that could fail that you want to capture
   when any_particular_exception then
       -- handle the excpetion
 -- 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;
 end loop;
end loop;

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
Select  col1 into var1 from my_table
where =
When No Data Found
Goto << loopend >>   --<-- force the loop to reiterate

--some more for loop logic here
End Loop;

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.