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

        The first one is SQLCODE and the second one is part of the text returned by SQLERRM.

        • 31. Re: Re: RAISE and RAISE_APPLICATION_ERROR
          Marwim


          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.

          Named exceptions give you more flexibility. In Re: Catching User Exception name in a variable I showed a simplified example of our exception handling.

          When the exception handler encounters a named exception it can simply reraise it and at the top of the call stack it is translated into a message considering the user language. Our framework even allows parameterized text so you can include parameter values.

          Line numbers should be logged when the exception is thrown and should not be part of the displayed error message.

           

          Regards

          Marcus

          • 32. Re: RAISE and RAISE_APPLICATION_ERROR
            William Robertson

            James Su wrote:

             

            The first one is SQLCODE and the second one is part of the text returned by SQLERRM.

             

            I know. I was actually wondering why you would want to repeat the error code in the message. It seems a bit redundant.

            • 33. Re: RAISE and RAISE_APPLICATION_ERROR
              James Su

              I didn't do it intentionally, it was just some test code.

              • 34. Re: RAISE and RAISE_APPLICATION_ERROR

                +1 - amazing how so many other folks seem to forgot the basics of modular programming when they use PL/SQL.

                 

                Even writing a simple custom procedure to use (similar to custom logging calls) would be better than the other embedded solutions being proposed.

                • 35. Re: RAISE and RAISE_APPLICATION_ERROR
                  James Su

                  How do you pass the application context raised with the named exception? By global variables?

                  • 36. Re: RAISE and RAISE_APPLICATION_ERROR
                    William Robertson

                    I agree, a wrapper like that is probably the only way to implement multi-language error messages, and of course you can integrate it neatly with your timing and logging instrumentation. I just think these grand projects for centralising error codes tend to be overkill for the kind of warehouse/batch systems I generally work on, where the error code itself never really matters, while a table of messages is just a painful maintenance overhead. What matters above all else is the original line number. Error handling code tends to obscure that by adding more re-raise points, and often discard the error stack accidentally as a side effect.

                    • 37. Re: RAISE and RAISE_APPLICATION_ERROR
                      Sven W.

                      brunovroman wrote:

                       

                      ...

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

                       

                      I think it is GOOD practice to have no exception block. So that the exceptions propagate all the way through the call stack, until the outermost level where it should be handled (and reraised).

                       

                      Too many (unneeded) exception blocks just make it harder to find out where the error was in the first place.

                      • 38. Re: RAISE and RAISE_APPLICATION_ERROR
                        William Robertson

                        Sven W. wrote:

                         

                        I think it is GOOD practice to have no exception block. So that the exceptions propagate all the way through the call stack, until the outermost level where it should be handled (and reraised).

                         

                        +1

                         

                        Handling exceptions in PL/SQL (or I think most other languages - I've seen the same issues in Perl) is like measuring the properties of a particle in quantum physics. You capture some information, but in the very act of doing so you necessarily lose some other information. You can sometimes recover it with dbms_utility.format_error_backtrace, but it takes more effort, it's not perfect, and in the kind of application where they think exception blocks are always a good idea, developers usually don't know how.

                        • 39. Re: Re: Re: RAISE and RAISE_APPLICATION_ERROR
                          Sven W.

                          2779499 wrote:

                           

                          Hi Karthick ,

                           

                          1. IF :new.score < 1000000 
                          2.   THEN 
                          3.   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.

                           

                           

                           

                          1. declare 
                          2.   invalid_score exception; 
                          3.   ... 
                          4. begin 
                          5.   if score > max_score then 
                          6.       raise invalid_score; 
                          7.   end if; 
                          8.  
                          9.  
                          10.   exception 
                          11.   when invalid_score then 
                          12.     RAISE_APPLICATION_ERROR (-20000,'Score is too low.'
                          13. end

                           

                          No this code is not identical! It has a major difference. William Robertson already pointed that out, but I have the feeling it was somehow overlooked in the discussion.

                          See for yourself:

                           

                           

                          begin
                            begin
                              IF 1 < 1000000  THEN
                                RAISE_APPLICATION_ERROR (-20000,'Score is too low.');
                              end if;
                            end;
                          end;
                          /
                          
                          
                          
                          

                          ORA-20000: Score is too low.

                          ORA-06512: at line 4

                           

                          begin
                            declare
                              invalid_score exception;
                            begin
                              IF 1 < 1000000  THEN
                                raise invalid_score;
                              end if;
                            exception
                              when invalid_score then
                                RAISE_APPLICATION_ERROR (-20000,'Score is too low.');
                            end;
                          end;
                          /
                          
                          
                          
                          

                          ORA-20000: Score is too low.

                          ORA-06512: at line 10

                           

                          Problem with the second version is that you lost the information where the error occured. The error did not occur in line 10, it did occur in line 6!

                           

                          My preferred way it to use raise_application_error on the point where I identify the error. I try to avoid it in extra exception handlers. If you add it to an exception handler in the same block where you raise the error, then you will loose the information about the line of error. That happens even when you add the often forgotten third parameter.

                           

                          Example (not so recommended)

                          SQL> set serveroutput on size unlimited
                          SQL>
                          SQL> declare
                            2    procedure logError
                            3    is
                            4    begin
                            5      dbms_output.put_line('ErrorBacktrace="'||dbms_utility.format_error_backtrace||'"');
                            6      dbms_output.put_line('ErrorCallStack="'||dbms_utility.format_error_stack||'"');
                            7    end logError;
                            8  begin
                            9
                          10    declare
                          11      invalid_score exception;
                          12      pragma exception_init(invalid_score, -20000);
                          13    begin
                          14      IF 1 < 1000000  THEN
                          15         raise invalid_score;
                          16      end if;
                          17    exception
                          18      when invalid_score then
                          19         RAISE_APPLICATION_ERROR (-20001,'Score is too low.',true);
                          20    end;
                          21
                          22  exception
                          23    when others then
                          24      logError;
                          25      raise;
                          26  end;
                          27  /
                          ErrorBacktrace="ORA-06512: at line 19
                          "
                          ErrorCallStack="ORA-20001: Score is too low.
                          ORA-20000:
                          "
                          declare
                          *
                          ERROR at line 1:
                          ORA-20001: Score is too low.
                          ORA-20000:
                          ORA-06512: at line 25
                          
                          
                          
                          

                          Because I added TRUE to the raise_application_error we now got the infromation that Ora-20000 happend. But not in which line.

                           

                           

                          Example (sometimes usefull) - less is better!

                          SQL> set serveroutput on size unlimited
                          SQL> declare
                            2    procedure logError
                            3    is
                            4    begin
                            5      dbms_output.put_line('ErrorBacktrace="'||dbms_utility.format_error_backtrace||'"');
                            6      dbms_output.put_line('ErrorCallStack="'||dbms_utility.format_error_stack||'"');
                            7    end logError;
                            8  begin
                            9
                          10    begin
                          11      IF 1 < 1000000  THEN
                          12         RAISE_APPLICATION_ERROR (-20001,'Score is too low.');
                          13      end if;
                          14    end;
                          15
                          16  exception
                          17    when others then
                          18      logError;
                          19      raise;
                          20  end;
                          21  /
                          ErrorBacktrace="ORA-06512: at line 12
                          "
                          ErrorCallStack="ORA-20001: Score is too low.
                          "
                          declare
                          *
                          ERROR at line 1:
                          ORA-20001: Score is too low.
                          ORA-06512: at line 19
                          SQL>
                          
                          
                          
                          

                           

                           

                          On the innermost level, raise the error with some meaningful error message. On the outermost level capture and log all errors.

                          The error backtrace informs us that the real error happend in line 12. This is important information for the developer. It is of no interest for the end user.

                           

                          But as always it depends. There are also good reasons for doing it differently (get out of recursive calls for example).

                           

                          Another possibility is to first log the error stack, then reraise and translate the error using raise_application_error. I think this is along the lines that Karthick2003 suggests.

                          • 40. Re: RAISE and RAISE_APPLICATION_ERROR
                            I think it is GOOD practice to have no exception block. So that the exceptions propagate all the way through the call stack, until the outermost level where it should be handled (and reraised).

                            Sorry - I couldn't disagree more with such 'one size fits all' statements like those..

                             

                            The proper place for any given exception to be 'handled' is HIGHLY dependent on the processing that is being done and the recovery, if any, that is needed after that exception occurs.

                             

                            For batch processes like those used in warehouse applications, you seldom want a single exception in one step to abort the entire process. A single exception could just mean that one piece of data is bad or one mod to a table. So you trap the exception, log it, and keep on going.

                             

                            Too many (unneeded) exception blocks just make it harder to find out where the error was in the first place.


                            That just isn't clear no matter which interpretation you use. You should never have 'too many' to begin with. Neither should you have 'unneeded' ones.

                             

                            If that statement is meant to say "don't write sloppy code" then I agree. And if someone doesn't know when, or how, to properly use exception blocks then they should not use any at all and let someone more senior deal with that aspect of the code.

                            .

                            • 41. Re: RAISE and RAISE_APPLICATION_ERROR
                              William Robertson

                              rp0428 wrote:

                               

                              And if someone doesn't know when, or how, to properly use exception blocks then they should not use any at all and let someone more senior deal with that aspect of the code.

                               

                              Unfortunately nobody will leave exception handling to someone more senior. There is a myth out there that thorough exception handling is a good thing and therefore every procedure should have an OTHERS exception handler. This damages overall exception handling and ultimately, if widespread, our profession.

                              • 42. Re: Re: RAISE and RAISE_APPLICATION_ERROR
                                Marwim

                                James Su wrote:

                                 

                                How do you pass the application context raised with the named exception? By global variables?

                                This requires a more detailed description of our framework :-)

                                We have a table for named exceptions

                                 

                                ne_sqlcodene_namene_textne_levelne_typene_rcne_et_id
                                -20500wrongIntervalParameter is not within defined interval5tech3100
                                -20999terminateTerminate processing. Error is already logged5tech5

                                 

                                Then there is a package namederrors with automatically generated code for each entry

                                 

                                    e_wrongInterval EXCEPTION;
                                    c_wrongInterval CONSTANT INTEGER := -20500;
                                    PRAGMA EXCEPTION_INIT (e_wrongInterval, -20500);
                                
                                FUNCTION getWrongInterval
                                    RETURN INTEGER;
                                

                                 

                                Now I can raise an exception either with a simple RAISE namederrors.e_terminate (should be selfexplanatory) or with raise_appliction_error(namederrors.c_wrongInterval,'|p_input|30-60|61')

                                Whereever I handle the error I can use functions isNamedError(SQLCODE) and getErrorMessage(SQLCODE,SQLERRM). getErrorMessage checks whether there is an entry in ne_et_id for the given SQLCODE. If not, then it returns ne_text. Otherwise it looks in another table for the error text. There are entries for each supported user language. This string is the parsed and every occurence of ~ is replaced with a part of SQLERRM. In my example it might be "Parameter ~ is not within defined interval. Expected ~, is ~." and would become "Parameter p_input is not wihin defined interval. Expected 30-60, is 61."

                                • 43. Re: RAISE and RAISE_APPLICATION_ERROR
                                  2779499

                                  Sven W. wrote:

                                   

                                  2779499 wrote:

                                   

                                  Hi Karthick ,

                                   

                                  1. IF :new.score < 1000000 
                                  2.   THEN 
                                  3.   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.

                                   

                                   

                                   

                                  1. declare 
                                  2.   invalid_score exception; 
                                  3.   ... 
                                  4. begin 
                                  5.   if score > max_score then 
                                  6.       raise invalid_score; 
                                  7.   end if; 
                                  8.  
                                  9.  
                                  10.   exception 
                                  11.   when invalid_score then 
                                  12.     RAISE_APPLICATION_ERROR (-20000,'Score is too low.'
                                  13. end

                                   

                                  No this code is not identical! It has a major difference. William Robertson already pointed that out, but I have the feeling it was somehow overlooked in the discussion.

                                  See for yourself:

                                   

                                   

                                  1. begin 
                                  2.   begin 
                                  3.     IF 1 < 1000000  THEN 
                                  4.       RAISE_APPLICATION_ERROR (-20000,'Score is too low.'); 
                                  5.     end if; 
                                  6.   end
                                  7. end

                                  ORA-20000: Score is too low.

                                  ORA-06512: at line 4

                                   

                                  1. begin 
                                  2.   declare 
                                  3.     invalid_score exception; 
                                  4.   begin 
                                  5.     IF 1 < 1000000  THEN 
                                  6.       raise invalid_score; 
                                  7.     end if; 
                                  8.   exception 
                                  9.     when invalid_score then 
                                  10.       RAISE_APPLICATION_ERROR (-20000,'Score is too low.'); 
                                  11.   end
                                  12. end

                                  ORA-20000: Score is too low.

                                  ORA-06512: at line 10

                                   

                                  Problem with the second version is that you lost the information where the error occured. The error did not occur in line 10, it did occur in line 6!

                                   

                                  My preferred way it to use raise_application_error on the point where I identify the error. I try to avoid it in extra exception handlers. If you add it to an exception handler in the same block where you raise the error, then you will loose the information about the line of error. That happens even when you add the often forgotten third parameter.

                                   

                                  Example (not so recommended)

                                  1. SQL> set serveroutput on size unlimited 
                                  2. SQL> 
                                  3. SQL> declare 
                                  4.   2    procedure logError 
                                  5.   3    is 
                                  6.   4    begin 
                                  7.   5      dbms_output.put_line('ErrorBacktrace="'||dbms_utility.format_error_backtrace||'"'); 
                                  8.   6      dbms_output.put_line('ErrorCallStack="'||dbms_utility.format_error_stack||'"'); 
                                  9.   7    end logError; 
                                  10.   8  begin 
                                  11.   9 
                                  12. 10    declare 
                                  13. 11      invalid_score exception; 
                                  14. 12      pragma exception_init(invalid_score, -20000); 
                                  15. 13    begin 
                                  16. 14      IF 1 < 1000000  THEN 
                                  17. 15         raise invalid_score; 
                                  18. 16      end if; 
                                  19. 17    exception 
                                  20. 18      when invalid_score then 
                                  21. 19         RAISE_APPLICATION_ERROR (-20001,'Score is too low.',true); 
                                  22. 20    end
                                  23. 21 
                                  24. 22  exception 
                                  25. 23    when others then 
                                  26. 24      logError; 
                                  27. 25      raise; 
                                  28. 26  end
                                  29. 27  / 
                                  30. ErrorBacktrace="ORA-06512: at line 19 
                                  31. ErrorCallStack="ORA-20001: Score is too low. 
                                  32. ORA-20000: 
                                  33. declare 
                                  34. ERROR at line 1: 
                                  35. ORA-20001: Score is too low. 
                                  36. ORA-20000: 
                                  37. ORA-06512: at line 25 

                                  Because I added TRUE to the raise_application_error we now got the infromation that Ora-20000 happend. But not in which line.

                                   

                                   

                                  Example (sometimes usefull) - less is better!

                                  1. SQL> set serveroutput on size unlimited 
                                  2. SQL> declare 
                                  3.   2    procedure logError 
                                  4.   3    is 
                                  5.   4    begin 
                                  6.   5      dbms_output.put_line('ErrorBacktrace="'||dbms_utility.format_error_backtrace||'"'); 
                                  7.   6      dbms_output.put_line('ErrorCallStack="'||dbms_utility.format_error_stack||'"'); 
                                  8.   7    end logError; 
                                  9.   8  begin 
                                  10.   9 
                                  11. 10    begin 
                                  12. 11      IF 1 < 1000000  THEN 
                                  13. 12         RAISE_APPLICATION_ERROR (-20001,'Score is too low.'); 
                                  14. 13      end if; 
                                  15. 14    end
                                  16. 15 
                                  17. 16  exception 
                                  18. 17    when others then 
                                  19. 18      logError; 
                                  20. 19      raise; 
                                  21. 20  end
                                  22. 21  / 
                                  23. ErrorBacktrace="ORA-06512: at line 12 
                                  24. ErrorCallStack="ORA-20001: Score is too low. 
                                  25. declare 
                                  26. ERROR at line 1: 
                                  27. ORA-20001: Score is too low. 
                                  28. ORA-06512: at line 19 
                                  29. SQL> 

                                   

                                   

                                  On the innermost level, raise the error with some meaningful error message. On the outermost level capture and log all errors.

                                  The error backtrace informs us that the real error happend in line 12. This is important information for the developer. It is of no interest for the end user.

                                   

                                  But as always it depends. There are also good reasons for doing it differently (get out of recursive calls for example).

                                   

                                  Another possibility is to first log the error stack, then reraise and translate the error using raise_application_error. I think this is along the lines that Karthick_Arp suggests.

                                   

                                  Hi Sven,

                                   

                                  So is it a good practice to use RAISE_APPLICATION_ERROR () outside the exception block.

                                  With so many different opinions i have lost the plot.

                                  • 44. Re: RAISE and RAISE_APPLICATION_ERROR
                                    Karthick2003

                                    Hi Sven,

                                     

                                    So is it a good practice to use RAISE_APPLICATION_ERROR () outside the exception block.

                                    With so many different opinions i have lost the plot.

                                     

                                    Good practices are always debatable. RAISE_APPLICATION_ERROR basically breaks the flow of your program. That's one reason I don't prefer using it outside of EXCEPTION block. But there could be valid reason for using it in the body as well. So its just for you to decide based on the knowledge (Your organization standards, project standards and your specific requirement) you posses and proceed accordingly.