1 2 3 Previous Next 30 Replies Latest reply on Oct 13, 2016 6:40 PM by cmit0227 Go to original post
      • 15. Re: How to throw error in When Others Exception
        cmit0227

        Sven W. wrote:

         

        cmit0227 wrote:

         

        I found a solution that works. I'm not sure if it's the best way but it gives everything I need:

         

        I'm keeping the When Others exception block in the proc that's throwing the error but have modified it as follows:

         

        WHEN OTHERS THEN

        p_Error := 1;
        ROLLBACK;

        vr_sqlerrm := SUBSTR( SQLERRM||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE(),0,999);

        raise_application_error(-20003,'EXCEPTION on household_id '||p_household_id||' is '||vr_sqlerrm);

         

        With the following output:

         

        ORA-20003: EXCEPTION on household_id 80004244008 is ORA-01422: exact fetch returns more than requested number of rowsORA-06512: at "COMPAS.PKG_ANNUAL_PAYER", line 180 ORA-06512: at "COMPAS.PKG_ANNUAL_PAYER", line 572 ORA-06512: at line 13

        That is not the recommended way to do it. You loose the error stack.

        Did you try the following?

         

        WHEN OTHERS THEN

        p_Error := 1;
        ROLLBACK;

        raise_application_error(-20003,'EXCEPTION on household_id '||p_household_id, true);

        Hi Sven, when I do that I get the following output, and line 385 is where the raise_application_error happens so I lose the fact that the actual error occurred on line 180.

         

        ORA-20003: EXCEPTION on household_id 80004244008
        ORA-06512: at "COMPAS.PKG_ANNUAL_PAYER", line 385
        ORA-01422: exact fetch returns more than requested number of rows
        ORA-06512: at "COMPAS.PKG_ANNUAL_PAYER", line 569
        ORA-06512: at line 13

        • 16. Re: How to throw error in When Others Exception

          I can't do that. There are other scenarios that must remain, and the calling procedure currently raising the error also closes the cursor and I don't want to lose that.

          Huh?

           

          Please explain that. In your original post you said this

          and they want execution halted if any error occurs)

          The cursor is useless if you 'halt' execution. You also said this:

          I have a procedure that currently has an exception handler for a particular exception

          That is also unnecessary if you want to 'halt' execution if any error occurs. Just have the calling procedure exit and return the exception to the caller.

           

          There must be something you aren't telling us.

          • 17. Re: How to throw error in When Others Exception
            Sven W.

            Ah now I remember how you can solve that if in 12c.

            It is a bit tricky to read, but it will keep the complete error stack on all levels.

            Still it is prefereable to avoid writeing an WHEN OTHERS exception.

             

            set serveroutput on

            declare

              procedure myProc is

              begin

                 raise no_data_found;

              exception

                when others then

                raise_application_error(-20000,'Original error at line '||utl_call_stack.backtrace_line(1), true);

              end myProc;

            begin

              myProc;

            exception

              when others then

                raise_application_error(-20001,'Some more error at line '||utl_call_stack.backtrace_line(1),true);

            end;

            /

             

            Result:

            Error starting at line : 2 in command -

            ...

            Error report -

            ORA-20001: Some more error at line 11

            ORA-06512: at line 14

            ORA-20000: Original error at line 4

            ORA-06512: at line 7

            ORA-01403: no data found

             

            And here is a version that even tells you in which module the error happend:

             

            declare 
              procedure myProc is
              begin
                 raise no_data_found;
              exception
                when others then 
                raise_application_error(-20000,'Original error at line '||utl_call_stack.backtrace_line(1)||' in '||UTL_CALL_STACK.SUBPROGRAM(1)(UTL_CALL_STACK.LEXICAL_DEPTH(1)+1) , true);
              end myProc;
            begin
              myProc;
            exception
              when others then 
                raise_application_error(-20001,'Some more error at line '||utl_call_stack.backtrace_line(1)||' in '||UTL_CALL_STACK.SUBPROGRAM(1)(UTL_CALL_STACK.LEXICAL_DEPTH(1)+1),true);
            end;
            /
            

             

            Error report -

            ORA-20001: Some more error at line 10 in __anonymous_block

            ORA-06512: at line 13

            ORA-20000: Original error at line 4 in MYPROC

            ORA-06512: at line 7

            ORA-01403: no data found

            • 18. Re: How to throw error in When Others Exception
              cmit0227

              Sven W. wrote:

               

              Ah now I remember how you can solve that if in 12c.

              It is a bit tricky to read, but it will keep the complete error stack on all levels.

              Still it is prefereable to avoid writeing an WHEN OTHERS exception.

               

              set serveroutput on

              declare

              procedure myProc is

              begin

              raise no_data_found;

              exception

              when others then

              raise_application_error(-20000,'Original error at line '||utl_call_stack.backtrace_line(1), true);

              end myProc;

              begin

              myProc;

              exception

              when others then

              raise_application_error(-20001,'Some more error at line '||utl_call_stack.backtrace_line(1),true);

              end;

              /

               

              Result:

              Error starting at line : 2 in command -

              ...

              Error report -

              ORA-20001: Some more error at line 11

              ORA-06512: at line 14

              ORA-20000: Original error at line 4

              ORA-06512: at line 7

              ORA-01403: no data found

               

              And here is a version that even tells you in which module the error happend:

               

              1. declare
              2. proceduremyProcis
              3. begin
              4. raiseno_data_found;
              5. exception
              6. whenothersthen
              7. raise_application_error(-20000,'Originalerroratline'||utl_call_stack.backtrace_line(1)||'in'||UTL_CALL_STACK.SUBPROGRAM(1)(UTL_CALL_STACK.LEXICAL_DEPTH(1)+1),true);
              8. endmyProc;
              9. begin
              10. myProc;
              11. exception
              12. whenothersthen
              13. raise_application_error(-20001,'Somemoreerroratline'||utl_call_stack.backtrace_line(1)||'in'||UTL_CALL_STACK.SUBPROGRAM(1)(UTL_CALL_STACK.LEXICAL_DEPTH(1)+1),true);
              14. end;
              15. /

               

              Error report -

              ORA-20001: Some more error at line 10 in __anonymous_block

              ORA-06512: at line 13

              ORA-20000: Original error at line 4 in MYPROC

              ORA-06512: at line 7

              ORA-01403: no data found

              Hi Sven - Not sure I understand....these version do still use a When Others exception block but you said "Still it is prefereable to avoid writeing an WHEN OTHERS exception.". 

              • 19. Re: How to throw error in When Others Exception
                cmit0227

                Andrew Sayer wrote:

                 

                Is there another raise statement at line 564 of that package?

                 

                Try this quick demo:

                1. createorreplacepackageas_exception_raising
                2. is
                3. proceduredemo_no_handle;
                4. proceduredemo_handle;
                5. endas_exception_raising;
                6. /
                7. showerr
                8. createorreplacepackagebodyas_exception_raising
                9. is
                10. procedureexception_no_handle
                11. is
                12. nDummynumber;
                13. begin
                14. select1intonDummyfromdual;
                15. select1intonDummyfromdualwhere1=0;
                16. endexception_no_handle;
                17. procedureexception_handle
                18. is
                19. nDummynumber;
                20. begin
                21. select1intonDummyfromdual;
                22. select1intonDummyfromdualwhere1=0;
                23. exception
                24. whenothersthenraise;
                25. endexception_handle;
                26. proceduredemo_no_handle
                27. is
                28. begin
                29. exception_no_handle;
                30. enddemo_no_handle;
                31. proceduredemo_handle
                32. is
                33. begin
                34. exception_handle;
                35. enddemo_handle;
                36. endas_exception_raising;
                37. /
                38. showerr

                 

                exec as_exception_raising.demo_handle

                BEGIN as_exception_raising.demo_handle; END;

                 

                 

                *

                ERROR at line 1:

                ORA-01403: no data found

                ORA-06512: at "AS.AS_EXCEPTION_RAISING", line 17

                ORA-06512: at "AS.AS_EXCEPTION_RAISING", line 27

                ORA-06512: at line 1

                 

                exec as_exception_raising.demo_no_handle

                ERROR at line 1:

                ORA-01403: no data found

                ORA-06512: at "AS.AS_EXCEPTION_RAISING", line 8

                ORA-06512: at "AS.AS_EXCEPTION_RAISING", line 22

                ORA-06512: at line 1

                 

                See how without that reraise the exception gets reported at the correct line

                Hi - thanks for the suggestion.  Unfortunately, this only works if there is also no exception handling in the calling proc which is not true in my case.  I definitely need the error handling that is in place, but thanks for the response!

                • 20. Re: How to throw error in When Others Exception
                  AndrewSayer

                  If the other block is handling the error then why do you need to keep raising it too?

                  Other block can handle specified exceptions that it should handle and then leave others alone (no when others) and it'll come through just fine.

                   

                  -Edit

                  Had a further thought, sure there are places where you would want to do things like tidy up OS related things / ensure cursors are closed etc no matter what. In this case you would raise the exceptions further using raise_application_error(-number, string, TRUE) after you've done your tidying up. This would go in your inner block - the one that is preventing your exceptions from raising properly.

                  • 21. Re: How to throw error in When Others Exception

                    Unfortunately, this only works if there is also no exception handling in the calling proc which is not true in my case. I definitely need the error handling that is in place, but thanks for the response!

                    Ok - but you haven't answered ANY of the several questions I ask you earlier.

                     

                    Also - make sure you don't confuse error 'handling' with error 'interception'.

                     

                    Just using a WHEN OTHERS to 'intercept' an exception without raising it again just makes it appear as if no exception occured. That isn't 'handling' the exception it is masking/hiding it.

                     

                    An exception should either be handled or it should be allowed to bubble up to the caller. Handling means taking action to correct whatever the problem was.

                     

                    If you just hide at exception at a lower level your code isn't modular, it makes it harder to determine the cause/location of the real exception and future maintenance/enhancement may actually break the code and introduce errors that won't be discovered until later.

                    • 22. Re: How to throw error in When Others Exception
                      cmit0227

                      Andrew Sayer wrote:

                       

                      If the other block is handling the error then why do you need to keep raising it too?

                      Other block can handle specified exceptions that it should handle and then leave others alone (no when others) and it'll come through just fine.

                       

                      -Edit

                      Had a further thought, sure there are places where you would want to do things like tidy up OS related things / ensure cursors are closed etc no matter what. In this case you would raise the exceptions further using raise_application_error(-number, string, TRUE) after you've done your tidying up. This would go in your inner block - the one that is preventing your exceptions from raising properly.

                      Hi - so just to recap.  I have the calling procedure (proc1) which has an existing exception block that closes the cursor and raises the error (that's the last raise so the error is returned to the calling batch program).

                       

                      The procedure needing supplemental exception handling (proc2) previously had only a very specific exception scenario (looking for dup val on index) and if that error is encountered it writes to a log table; all other errors are just falling out to proc1.  When proc1 raises the error it does provide the correct oracle error but lists only the line number of its RAISE statement, which obviously tells me nothing about where the actual error occurred.  So what I've needed to do is keep all existing exception handling and supplement with a When Others clause in proc2, including a raise_application_error statement that provides supplemental information about the error as well as the line number.

                       

                      This is what I've added and it is working:

                       

                      WHEN OTHERS THEN

                        p_Error := 1;

                        ROLLBACK;

                        vr_sqlerrm := SUBSTR( SQLERRM||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE(),0,999);

                        raise_application_error(-20003,'EXCEPTION on household_id '||p_household_id||' is  '||vr_sqlerrm);

                       

                      Suggestions have been made to use the following but when I do I lose the actual line number of where the error actually occurred:

                       

                      WHEN OTHERS THEN

                        p_Error := 1;

                        ROLLBACK;

                        raise_application_error(-20003,'EXCEPTION on household_id '||p_household_id, true);

                       

                      I hope this clarifies what I'm trying to do.

                       

                      Thanks again,

                      Christine

                      • 23. Re: How to throw error in When Others Exception
                        AndrewSayer

                        So proc1 calls proc2?

                         

                        I see no reason for the when others then section at all. It doesn't achieve anything other than a rollback - which IMO should be done in the calling the code (i.e. whatever is calling proc1).

                         

                        Once you take out these when others then... you won't need to play around at all, the exception will just carry on through as normal (with correct line numbers).

                        • 24. Re: How to throw error in When Others Exception

                          Let me repeat what I said earlier.

                          Just using a WHEN OTHERS to 'intercept' an exception without raising it again just makes it appear as if no exception occured. That isn't 'handling' the exception it is masking/hiding it.

                           

                          An exception should either be handled or it should be allowed to bubble up to the caller. Handling means taking action to correct whatever the problem was.

                           

                          If you just hide at exception at a lower level your code isn't modular, it makes it harder to determine the cause/location of the real exception and future maintenance/enhancement may actually break the code and introduce errors that won't be discovered until later.

                          Based ONLY on what you have posted you are just 'intercepting' most exceptions. You are NOT handling them.

                          The procedure needing supplemental exception handling (proc2) previously had only a very specific exception scenario (looking for dup val on index) and if that error is encountered it writes to a log table; all other errors are just falling out to proc1.

                           

                          Then I suggest that instead of merely 'intercepting' those WHEN OTHERS exceptions you write any info about them you want to capture to that same 'log table' and then re-raise the original exception so it bubbles up.

                           

                          That lets the lower layer capture the needed info but also prevents the exception from being masked.

                           

                          Tough to help someone that refuses to answer the questions people ask.

                          • 25. Re: How to throw error in When Others Exception
                            cmit0227

                            rp0428 wrote:

                             

                            Let me repeat what I said earlier.

                            Just using a WHEN OTHERS to 'intercept' an exception without raising it again just makes it appear as if no exception occured. That isn't 'handling' the exception it is masking/hiding it.

                             

                            An exception should either be handled or it should be allowed to bubble up to the caller. Handling means taking action to correct whatever the problem was.

                             

                            If you just hide at exception at a lower level your code isn't modular, it makes it harder to determine the cause/location of the real exception and future maintenance/enhancement may actually break the code and introduce errors that won't be discovered until later.

                            Based ONLY on what you have posted you are just 'intercepting' most exceptions. You are NOT handling them.

                            The procedure needing supplemental exception handling (proc2) previously had only a very specific exception scenario (looking for dup val on index) and if that error is encountered it writes to a log table; all other errors are just falling out to proc1.

                             

                            Then I suggest that instead of merely 'intercepting' those WHEN OTHERS exceptions you write any info about them you want to capture to that same 'log table' and then re-raise the original exception so it bubbles up.

                             

                            That lets the lower layer capture the needed info but also prevents the exception from being masked.

                             

                            Tough to help someone that refuses to answer the questions people ask.

                            Your posts are beyond insulting so this is the last time I will respond.  If you read my last post you will see that the whole point of the new When Others clause is to raise the error more fully and meaningfully it to the calling procedure which also raises it, so no, it is not just being intercepted.  The intention of the original dup val on index exception handler in proc2 was exactly for the purpose of intentional interception and absorption.

                            • 26. Re: How to throw error in When Others Exception
                              cmit0227

                              Andrew Sayer wrote:

                               

                              So proc1 calls proc2?

                               

                              I see no reason for the when others then section at all. It doesn't achieve anything other than a rollback - which IMO should be done in the calling the code (i.e. whatever is calling proc1).

                               

                              Once you take out these when others then... you won't need to play around at all, the exception will just carry on through as normal (with correct line numbers).

                              As mentioned, without it I was losing the exact line the error occurred.  With it that information is retained and supplemented (can provide exact household_id, etc.).  It also returns an error code which the calling procedure expects, letting it know that there was a problem.  As for the ROLLBACK, there are many inserts and updates that happen in that proc so including the rollback in the When Others section, for that particular household, makes sense to me.

                              • 27. Re: How to throw error in When Others Exception
                                AndrewSayer

                                cmit0227 wrote:

                                 

                                Andrew Sayer wrote:

                                 

                                So proc1 calls proc2?

                                 

                                I see no reason for the when others then section at all. It doesn't achieve anything other than a rollback - which IMO should be done in the calling the code (i.e. whatever is calling proc1).

                                 

                                Once you take out these when others then... you won't need to play around at all, the exception will just carry on through as normal (with correct line numbers).

                                As mentioned, without it I was losing the exact line the error occurred. With it that information is retained and supplemented (can provide exact household_id, etc.). It also returns an error code which the calling procedure expects, letting it know that there was a problem. As for the ROLLBACK, there are many inserts and updates that happen in that proc so including the rollback in the When Others section, for that particular household, makes sense to me.

                                Should calling a procedure cause your current transaction to commit or roll back? I would say no, but it's your application.

                                 

                                Your line numbers are being lost because of how you are reraising the error in proc2. If you used raise_application_error with the third argument as TRUE then you would not lose the line number.

                                 

                                Try my demo from before but instead of the when others then raise, use when others then raise_application_error(-200000,'generic text',true);

                                EDIT-

                                Tried it myself and it didn't quite work. Will rethink if there's a nicer way.

                                • 28. Re: How to throw error in When Others Exception
                                  cmit0227

                                  Andrew Sayer wrote:

                                   

                                  cmit0227 wrote:

                                   

                                  Andrew Sayer wrote:

                                   

                                  So proc1 calls proc2?

                                   

                                  I see no reason for the when others then section at all. It doesn't achieve anything other than a rollback - which IMO should be done in the calling the code (i.e. whatever is calling proc1).

                                   

                                  Once you take out these when others then... you won't need to play around at all, the exception will just carry on through as normal (with correct line numbers).

                                  As mentioned, without it I was losing the exact line the error occurred. With it that information is retained and supplemented (can provide exact household_id, etc.). It also returns an error code which the calling procedure expects, letting it know that there was a problem. As for the ROLLBACK, there are many inserts and updates that happen in that proc so including the rollback in the When Others section, for that particular household, makes sense to me.

                                  Should calling a procedure cause your current transaction to commit or roll back? I would say no, but it's your application.

                                   

                                  Your line numbers are being lost because of how you are reraising the error in proc2. If you used raise_application_error with the third argument as TRUE then you would not lose the line number.

                                   

                                  Try my demo from before but instead of the when others then raise, use when others then raise_application_error(-200000,'generic text',true);

                                  EDIT-

                                  Tried it myself and it didn't quite work. Will rethink if there's a nicer way.

                                  Sven had suggested that as well, and I tried it and this was my response:

                                   

                                  That is not the recommended way to do it. You loose the error stack.

                                  Did you try the following?

                                   

                                  WHEN OTHERS THEN

                                  p_Error := 1;
                                  ROLLBACK;

                                  raise_application_error(-20003,'EXCEPTION on household_id '||p_household_id, true);

                                  Hi Sven, when I do that I get the following output, and line 385 is where the raise_application_error happens so I lose the fact that the actual error occurred on line 180.

                                   

                                  ORA-20003: EXCEPTION on household_id 80004244008
                                  ORA-06512: at "COMPAS.PKG_ANNUAL_PAYER", line 385
                                  ORA-01422: exact fetch returns more than requested number of rows
                                  ORA-06512: at "COMPAS.PKG_ANNUAL_PAYER", line 569
                                  ORA-06512: at line 13

                                  • 29. Re: How to throw error in When Others Exception
                                    Sven W.

                                    I posted a 12c version, that keeps the full error stack and also the line number. Although that is not the exact logic that I'm using in my projects. The reason mostly beeing that the error simply is logged away, and that helps to clarify some things.

                                     

                                    Just want to comment on the some of the argements before.

                                    My example used two when others expections. This is not how I would normally approach  such a situation. I would try to avoid the innermost when others if possible.

                                     

                                    My recommendation would be to avoid WHEN OTHERS. So the defualt would be not to write such a general excpetion handler.

                                    However there are some valid reasons when I would use one.

                                     

                                    A) Usually it is in the top level procedure (e.g. "main"). Which is often the one that switches from a technical error message to a user friendly error message. The full error stack is logged (format_error_stack and format_error_backtrace) and then a human readable error message is reraised, often without adding the previous errors, btu with a hint to the error log.

                                     

                                    B) The second case is when there is a larger loop running and I want to track which item is worked upon and during which ID/item/loop did the error happen.

                                    Sometimes this can also be solved by using code instrumentation. For example by adding some trace entries so that you know which ID was running if tracing is enabled.

                                     

                                    With regards to transaction handling. If you think that a rollback is needed in the execption handler, then the same argument would require a commit at the end of your module.

                                    Usually it would be the task of the caller to decide, what should happen in case of an error. What sometimes makes sense is to provide a savepoint at the beginning and rollback to that savepoint. But only a rollback seems wrong.