5 Replies Latest reply on Oct 17, 2012 8:15 AM by BluShadow

    Error handling

    Rahul_India
      What is the difference between "raise application error" and "exception when others then" ?
      Which to use and when?
        • 1. Re: Error handling
          vijayrsehgal-Oracle
          Hi,
          read the PL/SQL Language Reference manuals please, however, RAISE_APPLICATION_ERROR Procedure : you invoke this procedure to raise a user-defined exception and return its error code and error message to the invoker and exception when others or when <some exception> is used to handle the exception raise, perform some action when the exception is raised
          • 2. Re: Error handling
            jeneesh
            Rahul_India wrote:
            What is the difference between "raise application error" and "exception when others then" ?
            Which to use and when?
            You cannot compare "raise application error" and "exception when others then" . They are used for two different purposes.

            "exception when others then" is used to handle all other exceptions which are not explicitly handled in your exception part.
            exception
              when no_data_found then
               .....
              when others then
                .....
                raise;
            end;
            raise_application_error is used to raise a user defined error message and code
            SQL> declare
              2   ln_cnt number;
              3  begin
              4   select 1
              5   into ln_cnt
              6   from dual
              7   where 1 = 2;
              8  exception
              9   when no_data_found then
             10    raise_application_error(-20001,
             11              'Data Not present');
             12  end;
             13  /
            declare
            *
            ERROR at line 1:
            ORA-20001: Data Not present
            ORA-06512: at line 10
            Refer Error handling

            And as per Tom Kyte, "when others then" can be treated as a bug, if it is not followed by a RAISE (which will raise the exception again)

            Edited by: jeneesh on Oct 17, 2012 12:33 PM
            Note: you would have got hundreds of results, if you spend 10 seconds with google...
            • 3. Re: Error handling
              yoonas
              Hi,

              see this - http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1155066278457

              Regards
              Yoonas
              • 4. Re: Error handling
                myOra_help
                Hi Rahul,
                Try to search in Google OR refer some Oracle docs for this to get the broader idea. However, raise_application_error is a Procedure which relate a ORA code with user defined error massage, while WHEN OTHERs clause is used to handle any Execeptions raised in code. Do not use OTHERs as far as you can as it can handle any exceptions and your code might become unstable and unpredictable.
                • 5. Re: Error handling
                  BluShadow
                  Or:

                  {thread:id=697262}

                  which is also in the FAQ: {message:id=9360013}