PLSQL Exception 'When Others' handling

Posted on 2006-03-28
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

    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;
    LVL 4

    Assisted Solution


    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;


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now