We help IT Professionals succeed at work.

PLSQL Exception 'When Others' handling

JAFFA12
JAFFA12 asked
on
Medium Priority
2,644 Views
Last Modified: 2013-12-12
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)?

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

thanks
Comment
Watch Question

Commented:
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;

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.