1 2 3 4 Previous Next 48 Replies Latest reply on Jul 10, 2015 5:15 AM by Marwim

    RAISE and RAISE_APPLICATION_ERROR

    2779499

      Is there any difference between RAISE and RAISE_APPLICATION_ERROR?

       

      As per my knowledge by  RAISE we can throw  an user defined exception.

        • 1. Re: RAISE and RAISE_APPLICATION_ERROR
          James Su

          In RAISE_APPLICATION_ERROR you can throw a user defined message.

          • 2. Re: RAISE and RAISE_APPLICATION_ERROR

             

            Is there any difference between RAISE and RAISE_APPLICATION_ERROR?

            The Oracle documentation clearly explains each of those and how the are different.

            http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/raise_statement.htm

            RAISE Statement

            The RAISE statement stops normal execution of a PL/SQL block or subprogram and transfers control to an exception handler.

            RAISE statements can raise predefined exceptions, such as ZERO_DIVIDE or NO_DATA_FOUND, or user-defined exceptions whose names you decide.
            . . .
            In an exception handler, you can omit the exception name in a RAISE statement, which raises the current exception again.

            RAISE is a statement

            http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/errors.htm#i1871

            RAISE_APPLICATION_ERROR Procedure

            You can invoke the RAISE_APPLICATION_ERROR procedure (defined in the DBMS_STANDARD package) only from a stored subprogram or method. Typically, you invoke this procedure to raise a user-defined exception and return its error code and error message to the invoker.

            The other is a procedure.

             

            The syntax diagrams for each of those shows the differences.

            • 3. Re: RAISE and RAISE_APPLICATION_ERROR
              Ashokram

              Using  Raise --------- you can Raise only Oracle Errors (Predefined Exceptions) (Syntax/Compilation)  Or

              raise an exception that means error in your business case, i.e. User_defined exceptions and Display a message just using dbms_output.put_line

               

              If you want your exception to display application error message same as Oracle In-built error you can use RAISE_APPLICATION_ERROR

              • 5. Re: RAISE and RAISE_APPLICATION_ERROR
                Cool

                Hi,

                 

                RAISE_APPLICATION_ERROR usually would be used to throw an exception based on application logic. using this we can map an custom error message and number to the exception, so that the application can map user friendly error message in the UI.

                 

                Regards,

                Cool

                • 6. Re: RAISE and RAISE_APPLICATION_ERROR

                  Hi,

                   

                  I have seen your query that the difference between the raise and raise application error.

                   

                  The RAISE_APPLICATION_ERROR built-in (defined in the DBMS_STANDARD package) should be used for just a single scenario: you need to communicate an application-specific error back to the user.

                   

                  Suppose, for example, I have a rule for the employees table that the minimum salary allowed is $100,000 (ah, wouldn't that be nice?). I want to enforce that rule through a database trigger:

                  TRIGGER employees_minsal_tr
                    BEFORE INSERT OR UPDATE
                    ON employees
                    FOR EACH ROW
                  BEGIN
                    IF :new.salary < 100000
                    THEN
                    /* communicate error */
                    NULL;
                    END IF;
                  END;

                  I can stop the DML from completing by issuing a RAISE statement, such as:

                  RAISE PROGRAM_ERROR;

                  But I would not be able to communicate back to the user what the actual problem was.

                   

                  If, on the other hand, I use RAISE_APPLICATION_ERROR, I can specify the error number (of little interest to my users, but a good "identifier" for support) and, more importantly, the error message, as in:

                  TRIGGER employees_minsal_tr
                    BEFORE INSERT OR UPDATE
                    ON employees
                    FOR EACH ROW
                  BEGIN
                    IF :new.salary < 1000000
                    THEN
                    RAISE_APPLICATION_ERROR (-20000,
                    'Salary of '|| :new.salary ||
                    ' is too low. It must be at least $100,000.');
                    END IF;
                  END;

                  And that, dear reader, is the motivation for using RAISE_APPLICATION_ERROR: the ability to communicate a custom, application-specific error message to your users.

                   

                  Use RAISE when you want to raise an already-defined exception, whether one of Oracle's (such as NO_DATA_FOUND) or one of your definition, as in:

                  DECLARE
                    e_bad_value EXCEPTION;
                  BEGIN
                    RAISE e_bad_value;
                  END;

                  but if it is one of your own user-defined exceptions, it only makes sense to raise it this way if you are going to trap it inside the backend as well, and then do something in response to the error.

                   

                   

                  I hope this will be helpful for you for the clear understanding and from this you can able to know the topics very clearly.

                   

                  Let me know if there is any more issues.

                   

                  Regards,

                  Vinoth.

                  • 7. Re: RAISE and RAISE_APPLICATION_ERROR
                    Yahoo!

                    2962860 wrote:

                     

                    Hi,

                     

                    I have seen your query that the difference between the raise and raise application error.

                     

                    The RAISE_APPLICATION_ERROR built-in (defined in the DBMS_STANDARD package) should be used for just a single scenario: you need to communicate an application-specific error back to the user.

                     

                    Suppose, for example, I have a rule for the employees table that the minimum salary allowed is $100,000 (ah, wouldn't that be nice?). I want to enforce that rule through a database trigger:

                    TRIGGER employees_minsal_tr
                      BEFORE INSERT OR UPDATE
                      ON employees
                      FOR EACH ROW
                    BEGIN
                      IF :new.salary < 100000
                      THEN
                      /* communicate error */
                      NULL;
                      END IF;
                    END;

                    I can stop the DML from completing by issuing a RAISE statement, such as:

                    RAISE PROGRAM_ERROR;

                    But I would not be able to communicate back to the user what the actual problem was.

                     

                    If, on the other hand, I use RAISE_APPLICATION_ERROR, I can specify the error number (of little interest to my users, but a good "identifier" for support) and, more importantly, the error message, as in:

                    TRIGGER employees_minsal_tr
                      BEFORE INSERT OR UPDATE
                      ON employees
                      FOR EACH ROW
                    BEGIN
                      IF :new.salary < 1000000
                      THEN
                      RAISE_APPLICATION_ERROR (-20000,
                      'Salary of '|| :new.salary ||
                      ' is too low. It must be at least $100,000.');
                      END IF;
                    END;

                    And that, dear reader, is the motivation for using RAISE_APPLICATION_ERROR: the ability to communicate a custom, application-specific error message to your users.

                     

                    Use RAISE when you want to raise an already-defined exception, whether one of Oracle's (such as NO_DATA_FOUND) or one of your definition, as in:

                    DECLARE
                      e_bad_value EXCEPTION;
                    BEGIN
                      RAISE e_bad_value;
                    END;

                    but if it is one of your own user-defined exceptions, it only makes sense to raise it this way if you are going to trap it inside the backend as well, and then do something in response to the error.

                     

                     

                    I hope this will be helpful for you for the clear understanding and from this you can able to know the topics very clearly.

                     

                    Let me know if there is any more issues.

                     

                    Regards,

                    Vinoth.

                     

                    Hi,

                     

                    IMHO, when copy and paste the text from some other site, you should give the link to that URL (so credit will go to them). Like rp and sgalaxy did.

                     

                    RAISE vs RAISE_APPLICATION_ERROR? - Oracle - Oracle - Toad World

                    • 8. Re: RAISE and RAISE_APPLICATION_ERROR

                      Oh O-O

                       

                      forgot to include it.

                       

                      RAISE vs RAISE_APPLICATION_ERROR? - Oracle - Oracle - Toad World

                       

                      Take it.

                       

                      Regards,

                      Vinoth.

                      • 9. Re: RAISE and RAISE_APPLICATION_ERROR
                        Saubhik

                        This is consider very unethical to copy paste some others work without mentioning the original author's name. Please understand that every body in this forum has sufficient expertise to surf the internet, So if you are not adding anything additional then it's better to refrain from posting.

                        • 10. Re: RAISE and RAISE_APPLICATION_ERROR
                          2981581

                          To add to others, You can re-raise the last exception using RAISE;

                           

                          Below from Oracle documentation:

                           

                          DECLARE

                            salary_too_high EXCEPTION;

                            current_salary NUMBER := 20000;

                            max_salary NUMBER := 10000;

                            erroneous_salary NUMBER;

                          BEGIN

                           

                            BEGIN

                            IF current_salary > max_salary THEN

                             RAISE salary_too_high; -- raise exception

                            END IF;

                            EXCEPTION

                             WHEN salary_too_high THEN -- start handling exception

                            erroneous_salary := current_salary;

                            DBMS_OUTPUT.PUT_LINE('Salary ' || erroneous_salary ||' is out of range.');

                            DBMS_OUTPUT.PUT_LINE ('Maximum salary is ' || max_salary || '.');

                             RAISE; -- reraise current exception (exception name is optional)

                            END;

                           

                          EXCEPTION

                            WHEN salary_too_high THEN -- finish handling exception

                            current_salary := max_salary;

                           

                            DBMS_OUTPUT.PUT_LINE (

                            'Revising salary from ' || erroneous_salary ||

                            ' to ' || current_salary || '.'

                            );

                          END;

                          • 11. Re: Re: RAISE and RAISE_APPLICATION_ERROR
                            sgalaxy

                            "forgot to include it."

                             

                            Don't worry... I have already posted it over 1/2 of hour ago.

                            • 13. Re: RAISE and RAISE_APPLICATION_ERROR
                              Karthick2003

                              RAISE is used to invoke named exceptions. The benefit of this is that you can handle that specific exception in your EXCEPTION block.

                               

                              For example if a students total score is greater than max score then you want to raise an exception. So you can do something like

                               

                              declare
                                invalid_score exception;
                                ...
                              begin
                                if score > max_score then
                                    raise invalid_score;
                                end if;
                                ...
                              
                              

                               

                              Now this named exception INVALID_SCORE can be handled in the exception block like this

                               

                              exception
                                when invalid_score then
                                  ... do something...
                              end;
                              
                              

                               

                              But when you use RAISE_APPLICATION_ERROR the only way to catch it is to use WHEN OTHERS.

                               

                              So generally the purpose of using RAISE_APPLICATION_ERROR is not to handle a exception but to return a error message to the client.

                               

                              So in your exception block you will do something like this

                               

                              exception 
                                when invalid_score then
                                   raise_application_error(-20001, 'Entered score is greater than MAX score');
                              end;
                              

                               

                              In some of the projects that I have worked in the past did have a standard of not using RAISE_APPLICAITON_EXCEPTION in the Body of a procedure. This will be used only in the exception block. And exceptional case will be raised using RAISE in the body and will be handled in the exception block of the code. In the exception block if necessary RAISE_APPLICATION_ERROR would be used to return a custom message to the client.

                              • 14. Re: RAISE and RAISE_APPLICATION_ERROR
                                James Su

                                "But when you use RAISE_APPLICATION_ERROR the only way to catch it is to use WHEN OTHERS."

                                 

                                Please see the below example:

                                 

                                DECLARE

                                  invalid_score   EXCEPTION;

                                  PRAGMA EXCEPTION_INIT (invalid_score, -20001);

                                BEGIN

                                  raise_application_error(-20001, 'Entered score is greater than MAX score');

                                EXCEPTION

                                  WHEN invalid_score THEN

                                       DBMS_OUTPUT.PUT_LINE('exception captured! '||SQLCODE||' '||SQLERRM);

                                END;

                                /

                                 

                                exception captured! -20001 ORA-20001: Entered score is greater than MAX score

                                 

                                 

                                PL/SQL procedure successfully completed.

                                1 2 3 4 Previous Next