Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Location
    Little Rock, AR
    Posts
    2

    Unanswered: Exception Handling in PL/SQL

    Hello all...
    I am relatively new to PL/SQL language and I am having trouble with
    exception handling and looping.

    I want to start a loop and if the select query in the loop fails because of TOO_MANY_ROWS, NO_DATA_FOUND OR OTHERS, I want to display an error message and continue in the loop until correct information is entered.

    Is there any way to do this? Any sample code and assistance would be greatly appreciated.
    Thanks.
    Ted

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    declare
    junk varchar2(10);
    x varchar2(1);
    begin
    x := 'N';
    loop while x = 'N'
    begin
    select field1
    into junk
    from my_table
    where xxx=21;
    exception
    when TOO_MANY_ROWS then
    dbms_output.put_line('Too many rows');
    when NO_DATA_FOUND then
    dbms_output.put_line('No data found');
    when OTHERS then
    null;
    end;
    if junk = 'YYYY' then
    x := 'Y';
    end loop;
    end;

    The secret is to have a sql block (begin,exception,end) within the loop.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Apr 2004
    Posts
    246
    I'd recommend one small change:
    when others then RAISE;

    If some other error occurs, you probably want to stop processing. Any unanticipated error should be considered fatal. Then, if you start getting errors that you want to ignore (such as dup val in index), then you can add exception handlers to ignore them as well.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  4. #4
    Join Date
    May 2003
    Location
    Little Rock, AR
    Posts
    2

    Thanks

    Thanks to beilstwh and shoblock. I appreciate your assistance.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •