11 Replies Latest reply: Feb 28, 2013 11:04 PM by dilipkumar10285 RSS

    Trapping errors in stored procedures

    MrGibbage
      I have to run queries on a gov't computer. There are errors in some of the stored procedures that will NEVER go away. Fact of life, and I have to live with it (I'm just a data analyst--not the developer of the procedures)
      So I do this, in the hope of trapping the error
      BEGIN
           run_stored_proc (my_cursor, my_id, param3);
      EXCEPTION
           WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('ERROR IN ID (' || my_id || '). The error was ' || SQLCODE || ' -ERROR- ' ||SQLERRM);
      END;
      Well, it can still crash.
      SQL> @c:\mysql\run_test
      ERROR IN ID (2692). The error was -20100 -ERROR- ORA-20100: Exception occurred in some_other_stored_procedure
      ORA-01422: exact fetch returns more than requested number of rows
      So it looks like run_stored_proc calls some_other_stored_procedure which is having issues. I was hoping that by trying to capture the error in the top level procedure, it would also capture the error in a lower-level "sub-procedure". This is the way a TRY-CATCH in java works. If an error happens anywhere along the way inside the TRY-CATCH, it is caught. Sure, PL/SQL ins't java, but is there a way for me to catch a lower level error and not have this code crash?

      Fortunately for me, it actually doesn't crash that often. But I have to put this code in a loop, and run it for many different my_id's. And when it does crash, it isn't significant from a data analysis standpoint. I can't do without a small percentage of the data. But I'd love to be able to just run my script and not have to watch it for unexpected crashes.

      This is not a question about how to fix the too many rows error in the procedure. I don't have access to the procedure, and it isn't going to be fixed. I just need to know how to deal with catching the error, if it is possible at all.
        • 1. Re: Trapping errors in stored procedures
          John Spencer
          I'm not quite sure I understan what you are asking here. Your script is not "crashing", it is just reporting the error through the dbms_output.put_line statement you added. When you say "I have to put this code in a loop, and run it for many different my_id's", do you mean that you want to call run_stored_proc multiple times with different values of my_id?

          If so, then it would look something like:
          begin
             for rec in (select my_id from wherever) loop
                BEGIN
                     run_stored_proc (my_cursor, my_id, param3);
                EXCEPTION
                     WHEN OTHERS THEN
                      DBMS_OUTPUT.PUT_LINE ('ERROR IN ID (' || my_id || '). The error was ' || SQLCODE || ' -ERROR- ' ||SQLERRM);
                END;
             end loop;
          end;
          The when others handler will swallow the error and control will go back to the top of the loop statement and execute the procedure again wiht the next value of my_id.

          John
          • 2. Re: Trapping errors in stored procedures
            Billy~Verreynne
            MrGibbage wrote:
            I have to run queries on a gov't computer.
            Irrelevant.
            There are errors in some of the stored procedures that will NEVER go away. Fact of life, and I have to live with it (I'm just a data analyst--not the developer of the procedures)
            All s/w modules (methods, procedures and functions) can run into errors. That is the nature of s/w. Not sure what point you are trying to make.
            So I do this, in the hope of trapping the error
            Why?

            There are 3 basic reasons for trapping an error.
            - it is not an error ito business logic (exception handler does the error fixing)
            - a reaction is needed to the error, prior to passing the error up the call stack (e.g. resource protection block implemented by the exception handler)
            - making the error meaningful (changing a technical error into a business, e.g. a no_data_found exception becomes a business no_such_invoice exception

            Exceptions are not handled just because.... Like every other single line of programming code, those code statements need a sound reason to justify their existence.
            BEGIN
                 run_stored_proc (my_cursor, my_id, param3);
            EXCEPTION
                 WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('ERROR IN ID (' || my_id || '). The error was ' || SQLCODE || ' -ERROR- ' ||SQLERRM);
            END;
            Wrong. Wrong. And wrong.
            Well, it can still crash.
            No it does not. It completes without an exception being raised. The caller (sqlplus in this case) assumes successful execution of the code. Which is NOT the case.

            The code's exception handler did the idiotic thing. It wrote the exception error message into a server buffer (array in DBMS_OUTPUT package).

            The client (due to serveroutput set to on), queried this server buffer directly after executing the PL/SQL code successfully. The client reads the text in this buffer. The client displays it.

            There was no error raised from the caller's (sqlplus) perspective.
            • 3. Re: Trapping errors in stored procedures
              Karthick_Arp
              So it looks like run_stored_proc calls some_other_stored_procedure which is having issues. I was hoping that by trying to capture the error in the top level procedure, it would also capture the error in a lower-level "sub-procedure". This is the way a TRY-CATCH in java works. If an error happens anywhere along the way inside the TRY-CATCH, it is caught. Sure, PL/SQL ins't java, but is there a way for me to catch a lower level error and not have this code crash?
              In a perfect world you must not be catching this errors, just let them occur and you will have all the details you want :)

              But we don't live in one such world, so oracle has offered the following

              FORMAT_CALL_STACK
              FORMAT_ERROR_BACKTRACE
              FORMAT_ERROR_STACK

              All available in [url http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_util.htm#CHDGHICD] DBMS_UTILITY

              Note: Remember WHEN OTHERS exception without RAISE can and will lead to catastrophe
              • 4. Re: Trapping errors in stored procedures
                dilipkumar10285
                hi,

                i think you need something like this.
                CREATE OR REPLACE PROCEDURE PROC_T1 AS
                BEGIN
                     DBMS_OUTPUT.PUT_LINE('INSIDE  PROC_T1');
                     RAISE_APPLICATION_ERROR(-20001,' MY EXCEPTION');
                END PROC_T1;
                /
                
                CREATE OR REPLACE PROCEDURE PROC_T2 AS
                BEGIN
                     DBMS_OUTPUT.PUT_LINE('INSIDE  PROC_T2');
                     PROC_T1;
                END PROC_T2;
                /
                
                
                BEGIN
                     PROC_T2;
                     EXCEPTION
                          WHEN OTHERS THEN
                               DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
                
                END;
                /
                output
                INSIDE  PROC_T2
                INSIDE  PROC_T1
                ORA-06512: at "HR.PROC_T1", line 4
                ORA-06512: at "HR.PROC_T2", line 4
                ORA-06512:
                at line 2
                Hope this helps

                Cheers
                Dilipkumar
                • 5. Re: Trapping errors in stored procedures
                  Billy~Verreynne
                  dilipkumar vishwakarma wrote:

                  i think you need something like this.
                  No!! This is totally unnecessary. The FULL error stack is already available to the client, like SQL*Plus.

                  Observe. Your procedures. Called WITHOUT a silly exception handler that attempts to do what sqlplus already does:
                  SQL> begin
                    2          Proc_T2;
                    3  end;
                    4  /
                  INSIDE  PROC_T2
                  INSIDE  PROC_T1
                  begin
                  *
                  ERROR at line 1:
                  ORA-20001:  MY EXCEPTION
                  ORA-06512: at "BILLY.PROC_T1", line 4
                  ORA-06512: at "BILLY.PROC_T2", line 4
                  ORA-06512: at line 2
                  
                  
                  SQL> 
                  Why on earth mess around with the error stack, write it to a server side buffer as plain text, and then call that an improvement as the client now knows what errors happened where?

                  Especially when that is NOT the case and the client sees text in a server buffer and no error!!

                  STOP ABUSING EXCEPTION HANDLERS.
                  • 6. Re: Trapping errors in stored procedures
                    BluShadow
                    MrGibbage wrote:
                    I have to run queries on a gov't computer. There are errors in some of the stored procedures that will NEVER go away. Fact of life, and I have to live with it (I'm just a data analyst--not the developer of the procedures)
                    So, they are what we call "expected exceptions".
                    You are getting an exception, and you expect that they can occur under certain conditions. Therefore you have to locate the actual cause of the exception, and handle that specific cause.

                    You say the error is: "ORA-01422: exact fetch returns more than requested number of rows" in a procedure "some_other_stored_procedure", so go to that procedure and either:

                    a) fix the query so that it doesn't try and return more than the requested number of rows. This should be feasible if the database design and data relationships are known, as you should be able to identify which of the rows is the one that is required for the query, or if it really doesn't matter (a very rare logic) then you could just apply some sort of rownum = 1 clause to the subquery to ensure it only ever brings back 1 random row from the selected set of rows. Therefore it doesn't have to be a "fact of life" that you have to live with... that's not something that really applies to good software design.

                    b) apply an appropriate exception handler to specifically capture that exception (not a stupid generic WHEN OTHERS) at source, perhaps log it somewhere in a log table if appropriate, and allow processing to carry on. This of course assumes the business logic allows for this exceptional record to fail and be, what is essentially, ignored.
                    procedure some_other_procedure(my_id in number is
                      cursor cur_qry is
                        select ...
                        from   ...
                        where  ... = my_id;
                      qry_result cur_qry%rowtype;
                    
                      e_too_many_rows exception;
                      pragma exception_init(e_too_many_rows, -1422);
                    begin
                    ..
                      open cur_qry;
                      fetch cur_qry into qry_result;
                      close cur_qry;
                    ..
                    ..
                    exception
                      when e_too_many_rows then
                        -- aside from logging it we're just going to explcitly ignore such an error
                        -- because our business doesn't care if these fail
                        ..
                        ... log details of the id that failed to our log table ...
                    end;
                    So I do this, in the hope of trapping the error
                    BEGIN
                         run_stored_proc (my_cursor, my_id, param3);
                    EXCEPTION
                         WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('ERROR IN ID (' || my_id || '). The error was ' || SQLCODE || ' -ERROR- ' ||SQLERRM);
                    END;
                    Agree with all others... this is not only wrong, it is (and no personal offence intended) just plain stupid.
                    What Billy was explaining is that the DBMS_OUTPUT buffer is only displayed on a client display, if the client that initiated the process has a display to show it and it has been told to display the server output from the dbms_output buffer. If you were to automate your process to be called from the server, or from another scheduled procedure on the database, then typically these errors are just going to get lost into obscurity and everyone will just thing everything is working fine.
                    Aside from the DBMS_OUTPUT issue, using a WHEN OTHERS exception in this way will hide other unexpected errors that may occur... so essentially you will never know about any error that happens... congratulations, you've just built a bullet proof system... or so it looks on the surface... but underneath all your data is getting corrupted and you know nothing about it.
                    Well, it can still crash.
                    SQL> @c:\mysql\run_test
                    ERROR IN ID (2692). The error was -20100 -ERROR- ORA-20100: Exception occurred in some_other_stored_procedure
                    ORA-01422: exact fetch returns more than requested number of rows
                    Good. Exceptions are there for a reason... to highlight that there's a problem somewhere that needs to be dealt with. Just trying to hide from the issue isn't the answer.
                    This is not a question about how to fix the too many rows error in the procedure. I don't have access to the procedure, and it isn't going to be fixed. I just need to know how to deal with catching the error, if it is possible at all.
                    Well... it should be a question about how to fix the issue. So, YOU don't have access to the procedure to fix it, but somebody must have, even if that's a 3rd party supplier, so that issue needs raising (just like the exception) to the appropriate person(s) who can fix it.
                    • 7. Re: Trapping errors in stored procedures
                      MrGibbage
                      Thanks for the answers. I knew there would be people that would say "fix the procedure", but like I said, it won't happen. This is a gov't database that isn't being supported any more, but holds the data that I need. There isn't any money to pay people to fix the queries. It holds business sensitive information that they can't just give anyone access to fix it, so I have to live with it. You guys are pretty intimidating! :) I should have some of you come over here to help me convince the gov't that they really need to fix this...

                      Anyway, while indeed it does look like I am trapping the error, I need it to not abort all execution. I know, I didn't say that in the OP. :( This changes everything, I know...
                      OPEN my_id_cur;
                      FETCH my_id_cur INTO my_id;
                      WHILE my_id_cur%FOUND
                      LOOP
                      BEGIN
                           run_stored_proc (my_cursor, my_id, param3);
                      EXCEPTION
                           WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('ERROR IN ID (' || my_id || '). The error was ' || SQLCODE || ' -ERROR- ' ||SQLERRM);
                      END;
                      -- PRINT OUT my_cursor
                      FETCH my_id_cur INTO my_id;
                      END LOOP;
                      So, the problem is if there is an error in any of the id's, then the whole thing stops. I want it to keep on running with the next FETCH. I thought that catching the error with the exception handling meant that the code would continue processing, but in my case, it handles the exception and then aborts completely.
                      • 8. Re: Trapping errors in stored procedures
                        John Spencer
                        While in general, I agree with the others that swallowing exceptions is a bad thing, but I clearly got the impression form your original post that you were just the caller and did not really care (probably within some limits) if a particular call failed. That is why I posted what I did yesterday. Did you read it?

                        It will do exactly what you say you want. Just put any processing you want to do on the cursor returned from the procedure call between the procedure call and the inner exception statement.

                        John
                        • 9. Re: Trapping errors in stored procedures
                          MrGibbage
                          Hi John, thanks for writing back. Yes, I did read your post from yesterday, and it really doesn't look any different from what I just posted. What I am seeing is that if there is an error in one of the my_id's, it will process up to that particular one and then print out the error and not loop any more. Completely aborting. If you are saying that it should continue with the next item, then I must be missing some other important nuance in my code that is preventing that from happening. Unfortunately I can't post my real code here because of business sensitivities, so I will be on my own.
                          • 10. Re: Trapping errors in stored procedures
                            John Spencer
                            Given this table data:
                            SQL> select * from t order by id;
                            
                                    ID DESCR
                            ---------- ----------
                                     1 One
                                     2 Two
                                     2 Deux
                                     3 Three
                                     4 Four
                                     5 Five
                            
                            6 rows selected.
                            and this function:
                            SQL> create function f (p_id in number) return varchar2 as
                              2     l_descr varchar2(10);
                              3  begin
                              4     select descr into l_descr
                              5     from t
                              6     where id = p_id;
                              7     return l_descr;
                              8  end;
                              9  /
                            
                            Function created.
                            I would expect to see a too many rows exception when it got to the second loop iteration. It sounds to me like your code looks something like:
                            SQL> declare
                              2     l_descr varchar2(10);
                              3     l_num   number;
                              4  begin
                              5     for i in 1 .. 5 loop
                              6        l_num := i;  -- just to have it for exception
                              7        l_descr := f(i);
                              8        dbms_output.put_line('value '||i||' got: '||l_descr);
                              9     end loop;
                             10  exception
                             11     when others then
                             12        dbms_output.put_line ('ERROR IN ID ('||l_num||'). The error was '||
                             13                              SQLCODE||' -ERROR- '||SQLERRM);
                             14  end;
                             15  /
                            value 1 got: One
                            ERROR IN ID (2). The error was -1422 -ERROR- ORA-01422: exact fetch returns more
                            than requested number of rows
                            
                            PL/SQL procedure successfully completed.
                            So. the exception handler is outside of the loop, therfore control passes completely out of the loop when you hit the first exception. I am suggexting something like:
                            SQL> declare
                              2     l_descr varchar2(10);
                              3     l_num   number;
                              4  begin
                              5     for i in 1 .. 5 loop
                              6        l_num := i;  -- just to have it for exception
                              7        begin
                              8           l_descr := f(i);
                              9           dbms_output.put_line('value '||i||' got: '||l_descr);
                             10        exception
                             11           when others then
                             12              dbms_output.put_line ('ERROR IN ID ('||l_num||'). The error was '||
                             13                                    SQLCODE||' -ERROR- '||SQLERRM);
                             14        end;
                             15     end loop;
                             16  exception
                             17     when others then
                             18        dbms_output.put_line ('This should never happen. The error was '||
                             19                              SQLCODE||' -ERROR- '||SQLERRM);
                             20  end;
                             21  /
                            value 1 got: One
                            ERROR IN ID (2). The error was -1422 -ERROR- ORA-01422: exact fetch returns more
                            than requested number of rows
                            value 3 got: Three
                            value 4 got: Four
                            value 5 got: Five
                            
                            PL/SQL procedure successfully completed.
                            So, the error handling is within the loop, so it appears to the code that nothing bad happened. You would put any and all processing that you want to do based on the return from your procedure where I have dbms_output.put_line('value '||i||' got: '||l_descr);.

                            John
                            • 11. Re: Trapping errors in stored procedures
                              dilipkumar10285
                              Hi,

                              first of all thank to Billy for correcting me.
                              The FULL error stack is already available to the client, like SQL*Plus
                              The solution given by BluShadow and John Spencer are very good. I will keep it in mind.
                              Handle the exception in such a way that it will not break current execution but log the occurred exception for future solving.

                              Please correct me, if i am wrong.

                              by the way thanks everyone for nice analysis on various approaches.

                              Cheers
                              Dilipkumar