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)?

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

thanks
JAFFA12Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Harish_RajaniCommented:

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.