• 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.