This discussion is archived
11 Replies Latest reply: Feb 28, 2013 9:04 PM by dilipkumar10285 RSS

Trapping errors in stored procedures

MrGibbage Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points