This discussion is archived
12 Replies Latest reply: Jan 11, 2013 3:08 AM by BluShadow RSS

Exception IF ELSE

893566 Newbie
Currently Being Moderated
I have a variable option
option number;

I have an if condition as

begin
option :=3;
IF (option = 1)
dbms_output.put_line('1');
elsif (option=2)
dbms_output.put_line('2');
end if;
update table emp.....
end

If the option value is not 1 or 2, I get an exception. So any statements after if are not being executed and I get the exception message. Please help me to ignore this exception and continue exceuting with the statements below if statement
  • 1. Re: Exception IF ELSE
    971895 Journeyer
    Currently Being Moderated
    Per your code you will not get the exception.. Post your exact code...
  • 2. Re: Exception IF ELSE
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi,

    As your code seems partial, you can add exception as
    exception 
    when others then
    dbms_output.put_line('Error ocured');
  • 3. Re: Exception IF ELSE
    971895 Journeyer
    Currently Being Moderated
    if any exception will raise it will suppress..this is not good to write the code..


    exception
    when others then
    null;
    end;
    /
  • 4. Re: Exception IF ELSE
    Paul Horth Expert
    Currently Being Moderated
    Chanchal Wankhade wrote:
    Hi,

    As your code seems partial, you can add exception as
    exception 
    when others then
    dbms_output.put_line('Error ocured');
    No! That is completely wrong. You are hiding the error.
  • 5. Re: Exception IF ELSE
    Paul Horth Expert
    Currently Being Moderated
    You don't say what the exception is.
    You don't give complete code.

    I can't give you an answer.

    Please read {message:id=9360002}
  • 6. Re: Exception IF ELSE
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi Paul,

    If he wanted to see the error then he can add error massega and number as well.
    exception
    when other then
    --assing value to variables like
    -- a :=sqlerrm;
    --b :=sqlmsg.
    and now
    
    dbms_output.put_line(a,b);
  • 7. Re: Exception IF ELSE
    Paul Horth Expert
    Currently Being Moderated
    Chanchal Wankhade wrote:
    Hi Paul,

    If he wanted to see the error then he can add error massega and number as well.
    exception
    when other then
    --assing value to variables like
    -- a :=sqlerrm;
    --b :=sqlmsg.
    and now
    
    dbms_output.put_line(a,b);
    Why bother. If you're running this in a client while you are testing, if you completely
    leave out the exception block, it will report the error anyway.

    If it is production code, you wouldn't be using dbms_output.

    In fact, in a lot of cases you wouldn't have an exception block. You would pass the error up
    to the caller.
    If you do have an exception block for logging you should then re-raise the error to the caller.

    If you wanted to handle the error yourself and not pass it up, you wouldn't use 'when others'
    as most errors you won't handle: so you would handle a specific error.
  • 8. Re: Exception IF ELSE
    Purvesh K Guru
    Currently Being Moderated
    As Paul has correctly pointed, you have not provided us with many vital information. And that leaves us to Guess about your situation.

    1. What is your oracle version
    select * from v$version;
    2. What is the Exception you are encountering?
    3. Is that Exception raised to the following Update statement?
    4. How do you ascertain that the exception is being raised because Option variable is not 1 or 2?

    If you would provide us with this information, people would be happy to help you.

    Assuming you are correct to say that the exception is being raised due to Option <> 1, 2;
    begin
    option :=3; 
      begin
        IF (option = 1)
          dbms_output.put_line('1');
        elsif (option=2)
          dbms_output.put_line('2');
        end if;
      exception
        when your_exception
          then
            your_exception_handling_code follows here
      end;
    
      update emp...
    exception
      when some_known_exception then
        your_exception_handling_code;
        raise;
      when others then
        log_exception; -- This should be your Error Logging code, that uses Pragma_autonomous transaction to store exception data for further referral.
        raise;
    end;
  • 9. Re: Exception IF ELSE
    893566 Newbie
    Currently Being Moderated
    I am sorry I had the exception because of a missing values in the update statement.I wrote the exception handler as

    exception when others then
    DBMS_output.put_line('error');
    raise;
    end;

    this solved my problem as I could trace the error

    thanks
  • 10. Re: Exception IF ELSE
    ranit B Expert
    Currently Being Moderated
    Purvesh K wrote:
    begin
    option :=3; 
    begin
    IF (option = 1)
    dbms_output.put_line('1');
    elsif (option=2)
    dbms_output.put_line('2');
    end if;
    exception
    when your_exception
    then
    your_exception_handling_code follows here
    end;
    
    update emp...
    exception
    when some_known_exception then
    your_exception_handling_code;
    raise;
    when others then
    log_exception; -- This should be your Error Logging code, that uses Pragma_autonomous transaction to store exception data for further referral.
    raise;
    end;
    I have a very 'basic' doubt here...
    Why is it always advised to use the exact 'error' handler and 'WHEN OTHERS' as the last one?

    Using WHEN OTHERS can catch all errors, so why should we write specific ERROR blocks?

    Can anybody please explain me this?

    TIA.
    Ranit B.
  • 11. Re: Exception IF ELSE
    BluShadow Guru Moderator
    Currently Being Moderated
    ranit B wrote:
    I have a very 'basic' doubt here...
    Why is it always advised to use the exact 'error' handler and 'WHEN OTHERS' as the last one?

    Using WHEN OTHERS can catch all errors, so why should we write specific ERROR blocks?

    Can anybody please explain me this?

    TIA.
    Ranit B.
    Because "exception handling" means that you should handle exceptions that you are expecting to happen.
    If you don't expect an exception you shouldn't try and capture it using a WHEN OTHERS, you should allow the exception to raise up to the calling code, as it may be designed to handle that particular exception that that level of code is not designed to handle. Including a WHEN OTHERS exception captures all exceptions, but doesn't allow you to handle them (although you could log them, but that still doesn't actually handle the exceptions).

    If a WHEN OTHERS is included to log any exceptions it should always include a RAISE statement so that the calling code can potentially handle it or raise it up further.

    As a general rule of thumb, you are better to only write exception handlers for those exceptions you expect to happen in your code e.g. checking for the existence of a record and using a WHEN NO_DATA_FOUND exception to deal with that, or inserting records and using a DUP_VAL_ON_INDEX or suchlike exception to prevent duplicate entries being recorded, which can be gracefully reported back to the user as with a friendly message e.g. "The room you are trying to book is already booked between those times." etc.

    Unless you have good error logging, you should never use a WHEN OTHERS, and if you do use a WHEN OTHERS it should always raise an exception, otherwise, as Tom Kyte says, it's a bug in the application code.
  • 12. Re: Exception IF ELSE
    BluShadow Guru Moderator
    Currently Being Moderated
    ranit B wrote:
    I have a very 'basic' doubt here...
    Why is it always advised to use the exact 'error' handler and 'WHEN OTHERS' as the last one?

    Using WHEN OTHERS can catch all errors, so why should we write specific ERROR blocks?
    On top of what I've just posted, you may want to handle different exceptions in different ways.
    One exception may roll back the data and raise the issue with the user, another may need to just commit the data anyway and silently carry on, another may need to just raise the issue with the user to indicate their 'input' is wrong so they can correct it and try again. Also, user defined exceptions can be used to check assertions/conditions on data and break the sequence of execution for a block if the conditions are not met.

    I rarely use a WHEN OTHERS clause in any of my code, except perhaps at the top of the call stack to log the error (with the full call stack recorded) before raising it anyway.
    Well designed and written code should rarely get to such a situation where the WHEN OTHERS is reached.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points