12 Replies Latest reply: Jan 11, 2013 3:08 AM by BluShadow RSS

    Exception IF ELSE

    893566
      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
          Per your code you will not get the exception.. Post your exact code...
          • 2. Re: Exception IF ELSE
            Chanchal Wankhade
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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.