1 2 3 4 Previous Next 48 Replies Latest reply on Jul 10, 2015 5:15 AM by Marwim Go to original post
      • 15. Re: RAISE and RAISE_APPLICATION_ERROR
        Karthick2003

        Yes, now its named exception. But I get the point you are trying to make. My earlier sentence could have been misleading.

        • 16. Re: RAISE and RAISE_APPLICATION_ERROR
          2779499

          This is not your explanation.Kindly refrain from plagiarism

          • 17. Re: RAISE and RAISE_APPLICATION_ERROR

            OK!! I'll refrain that and thanks alot for replying me so late by spending your time!!!

            • 18. Re: Re: RAISE and RAISE_APPLICATION_ERROR
              2779499

              Hi Karthick ,

               

              IF :new.score < 1000000
                THEN
                RAISE_APPLICATION_ERROR (-20000,'Score is too low.');
              
              
              

               

              Is the above code snippet same as the below one.

              I mean both the snippets will have same functionality of displaying an error message to the user.

               

               

               

              declare
                invalid_score exception;
                ...
              begin
                if score > max_score then
                    raise invalid_score;
                end if;
              
              
                exception
                when invalid_score then
                   RAISE_APPLICATION_ERROR (-20000,'Score is too low.')
              end;
              
              
              
              
              • 19. Re: RAISE and RAISE_APPLICATION_ERROR
                Karthick2003

                Consider there are 40 places in your code where you check for score and you send user 'Score is too low.' Message. And lets say you have a error message table which stores error number and message (in your case -20000 and 'Score is too low'). Which approach do you think will be good?

                 

                My personal choice is not to use RAISE_APPLICATOIN_ERROR outside of EXCEPTION blocks.

                • 20. Re: RAISE and RAISE_APPLICATION_ERROR
                  2779499

                  You didn't answer my question.

                  Obviously 2nd  choice is better.

                   

                  So both code will work in the same way with a difference that second is more efficient than on.

                   

                   

                  Suppose i have a table err_msg (err_code,err_msg)

                   

                  How can i use the table err_msg with  RAISE_APPLICATION_ERROR

                  • 21. Re: RAISE and RAISE_APPLICATION_ERROR
                    Karthick2003

                    post.user_wrote.label:

                     

                    You didn't answer my question.

                    Obviously 2nd  choice is better.

                     

                    So both code will work in the same way

                     

                    If you are asking for the specific piece of code that you have posted then yes they do the same thing in terms of the end result. But both do have different condition checks (I guess its just a simple mistake).

                    • 22. Re: RAISE and RAISE_APPLICATION_ERROR
                      2779499

                      Thanks.

                      It will be very cumbersome and impossible to debug if we hard-code the error code and error messages directly in RAISE_APPLICATOIN_ERROR .

                      Suppose we store error code in table err_msg (err_code,err_msg).

                       

                      How can i pass the value to  RAISE_APPLICATION_ERROR

                      • 23. Re: RAISE and RAISE_APPLICATION_ERROR
                        Cool

                        Hi,

                         

                        I guess you can use a table like you mentioned in order keep track of the list of application_errors used and purpose. But in the procedure I guess you would have to hard code it ( you can query the table and put it, but not sure what is the value add here).

                         

                        Regards,

                        Wilson

                        • 24. Re: Re: RAISE and RAISE_APPLICATION_ERROR
                          Karthick2003

                          You need to device your own error management API.

                           

                          Here is a rough example.

                           

                          Connected to:
                          Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
                          With the Partitioning, OLAP, Data Mining and Real Application Testing options
                          
                          SQL> create table error_master (error_code number, error_message varchar2(100));
                          
                          Table created.
                          
                          SQL> insert into error_master values (-20001, 'Score too low');
                          
                          1 row created.
                          
                          SQL> insert into error_master values (-20002, 'Score too high');
                          
                          1 row created.
                          
                          SQL> create or replace package manage_error
                            2  as
                            3    error_code number;
                            4    function set_error_code(error_code number) return number;
                            5    function get_error_message return varchar2;
                            6  end;
                            7  /
                          
                          Package created.
                          
                          SQL> create or replace package body manage_error
                            2  as
                            3    function set_error_code(error_code number) return number
                            4    is
                            5    begin
                            6      manage_error.error_code := set_error_code.error_code;
                            7
                            8      return manage_error.error_code;
                            9    end;
                           10
                           11    function get_error_message return varchar2
                           12    is
                           13      error_message error_master.error_message%type;
                           14    begin
                           15      select e.error_message into get_error_message.error_message
                           16        from error_master e
                           17       where e.error_code = manage_error.error_code;
                           18
                           19      return get_error_message.error_message;
                           20    end;
                           21  end;
                           22  /
                          
                          Package body created.
                          
                          SQL> exec raise_application_error(manage_error.set_error_code(-20001), manage_error.get_error_message)
                          BEGIN raise_application_error(manage_error.set_error_code(-20001), manage_error.get_error_message); END;
                          *
                          ERROR at line 1:
                          ORA-20001: Score too low
                          ORA-06512: at line 1
                          
                          SQL> exec raise_application_error(manage_error.set_error_code(-20002), manage_error.get_error_message)
                          BEGIN raise_application_error(manage_error.set_error_code(-20002), manage_error.get_error_message); END;
                          *
                          ERROR at line 1:
                          ORA-20002: Score too high
                          ORA-06512: at line 1
                          
                          SQL>
                          
                          • 25. Re: RAISE and RAISE_APPLICATION_ERROR
                            Billy~Verreynne

                            2779499 wrote:

                             

                            You didn't answer my question.

                            Obviously 2nd  choice is better.

                             

                            So both code will work in the same way with a difference that second is more efficient than on.

                             

                             

                            Suppose i have a table err_msg (err_code,err_msg)

                             

                            How can i use the table err_msg with  RAISE_APPLICATION_ERROR

                             

                            I prefer the following method. Re: Catching User Exception name in a variable

                             

                            It is significantly easier for a developer to use. No meta data layer to update, manage via version control, and deploy, when defining and using application exceptions (and custom formatted messaging).

                             

                            It also provides a standard abstraction interface (similar to exception classes in other languages' frameworks), and can be implemented as an abstract class (not final) that can be subclassed for implementing application specific requirements.

                             

                            Directly calling Raise_Application_Error() in user or application code, violates basic software engineering principles.

                            • 26. Re: RAISE and RAISE_APPLICATION_ERROR
                              BrunoVroman

                              Hello,

                               

                              an important use of RAISE is to propagate the exception outside the current block (already written on 01-JUL by 2981581 but the example is maybe too simple, and I add a remark)

                              If you have for example a procedure


                              CREATE OR REPLACE PROCEDURE myprocedure ( ... )
                              ...
                              BEGIN
                                ...
                              END myprocedure;

                               

                              it is usually judged bad practice if there is no EXCEPTION block... So, switch to

                               

                              CREATE OR REPLACE PROCEDURE myprocedure ( ... )
                              ...
                              BEGIN
                                ...
                              EXCEPTION
                                WHEN aaa THEN xx;
                                WHEN bbb THEN yy;
                                WHEN OTHERS THEN zz;
                              END myprocedure;

                               

                              But we "create an issue": once an exception has been "treated", it 'disappears'...
                              Especially the "WHEN OTHERS" is often pointed as bad (Tom Kyte would like this exception to be unavailable), but even for the other cases:
                              it might be that you don't want to "switch off" the error once it has been treated in the exception block.
                              In this case: just add "RAISE;" to re-raise the same exception in the block calling the procedure

                               

                              CREATE OR REPLACE PROCEDURE myprocedure ( ... )
                              ...
                              BEGIN
                                ...
                              EXCEPTION
                                WHEN aaa THEN xx; /* in this case, error can be "switched off" */;
                                WHEN bbb THEN yy; /* in this case, propagate the error */ RAISE;
                                WHEN OTHERS THEN zz; /* when others should always propagate */ RAISE;
                              END myprocedure;

                               

                              Best regards,

                               

                              Bruno Vroman.

                              • 27. Re: Re: RAISE and RAISE_APPLICATION_ERROR
                                William Robertson
                                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.


                                I can't see how that would be a useful standard to have. So you:


                                1. define a no_cheese_on_tuesdays exception
                                2. if the condition occurs, have the body of the procedure raise no_cheese_on_tuesdays
                                3. include a "when no_cheese_on_tuesdays" exception handler that then uses raise_application_error to return an informative error message.


                                All that has done is increased the amount of code and obscured the line number. I would sooner use a RAE right where the condition occurs and have done with it.

                                • 28. Re: Re: RAISE and RAISE_APPLICATION_ERROR
                                  William Robertson

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

                                   

                                  Out of interest, why duplicate the "-20001" part?

                                  • 29. Re: RAISE and RAISE_APPLICATION_ERROR
                                    William Robertson

                                    That's very neat. However to me, half the point of RAE is the ability to construct an informative error message using information about the actual values involved at runtime. Of course you can extend the functions with parameterised placeholders e.g "Item %s1 is duplicated", however the two occasions when systems I worked on went down this path were both over 20 years ago and I think in hindsight we just made more work for ourselves. For one thing, it's almost impossible to maintain a consistent set of reusable error messages, and some developer will add "Item already exists" or "This cheese type has already been ordered" etc faster than you can police them. Also too much indirection could make it harder to find the procedure where an exception was raised.