PLSQL Exception 'When Others' handling

Posted on 2006-03-28
Medium Priority
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)?

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

Question by:JAFFA12
LVL 16

Accepted Solution

RCorfman earned 252 total points
ID: 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
  -- 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;

Assisted Solution

Harish_Rajani earned 248 total points
ID: 16399729

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 my_table.id = X.id
When No Data Found
Goto << loopend >>   --<-- force the loop to reiterate

--some more for loop logic here
End Loop;


Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month17 days, 10 hours left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question