8 Replies Latest reply: May 8, 2014 3:45 AM by smon RSS

    exception handling

    smon

      Just wondering if I could get some opinion on best practice for PLSQL exception handling in a multi-tier environment (.Net frontend & Oracle 11g backend).

       

      I've read Tom Kyte on how exceptions should propogate back to the client/top-level, and I've read/heard other opinion on how they should be caught at the source (lowest level), logged and manually sent back up the chain to the top.

       

      My initial thinking is if all the PLSQL exceptions are allowed to propogate back up then the handling/logging code only needs to be written in one place, in the client. What are the negatives to this approach? What are the positives to doing it the other way?

       

      thanks in advance.

        • 1. Re: exception handling
          bencol

          If I get an exception in the database code, I would want to log it and its stack in a database table. I would do this in a autonomous logging procedure. I would then allow the original exception to be raised back to the client, so that the end user can see it. The front end code can then decide if it wants to obfuscate the error message, possibly giving the user "Database error, log ref xxx, please contact you system administrator". You can then look up the error in the logging table to find where what code/inputs caused the exception.

           

          In short, handle and re-raise in the db code.

          • 2. Re: exception handling
            BluShadow

            As is usual with these things, the answer is "it depends".

             

            "Exception" doesn't necessarily mean an error; it rather means an exception to normal processing.  However, "normal processing" could be related to a particular flow of business, and it's just an exception that something may happen another way.  In that case it may be something that you want to capture and handle, because you have a method for handling those exception(al) circumstances.  In that case you write an exception handler to do what needs to be done, and allow the code to continue, or raise up to an appropriate calling point in the code so that it can continue from there.

             

            If the exception is something you're not expecting, then you are best to let this raise up the calling code to the "top" level where it can be recorded (autonomously written to a database table/logs) and then indicated to the front end user, if appropriate.  To put exception handlers (especially WHEN OTHER ones) in every bit of code, just in order to log it and then raise it, not only creates lots more code, but makes it harder to maintain if you want to change the way exception handling is done.  Generally it's considered poor practice to use WHEN OTHER exception handlers anyway, as the general principle is that you code exception handlers for exceptions that you expect to happen.

             

            PL/SQL 101 : Exception Handling

            • 3. Re: exception handling
              Ric Van Dyke

              The key here is the word "Exception"  as the other folk who have replied say, think of this as something out of the norm that you want to handle in a special way at the database level.  Not something that you want to want or can handle at the application level.  Maybe there is a duplicate row for example that isn't really an issue for the application but might be an issue that should be addressed to clean up the data.  So you handle the exception locally in the database (log the duplicate row IDs for example in an exception table) and carry on.  The folks at the app level never need to be aware of the issue.  Or maybe then do need to be alerted, still handle it locally then either re-raise the exception or raise a new one (maybe a user defined one). 

               

              The when others is very dangerous only in how some folks use it.  If you do something like "when others null;" then if an exception is raised, nothing will happen and no one will know something went wrong.  The PL/SQL compiler will now let you know about this and at least warn you of a potential problem. Use correctly the when others can be a good tool, unfortunately it's used incorrectly more often. 

              • 4. Re: exception handling
                Billy~Verreynne

                PL/SQL and architecture do not change how exceptions are dealt with.

                 

                Which is that exceptions need to be propagated to the caller to inform it of failures (unless said exception is not a failure, in which case it is silently handled locally - as Blu mentioned).

                 

                Whether the caller is remote or local, does not change the principle.

                 

                A decision to log of exceptions locally should not impact this principle. By all means log exceptions locally in the code if that is needed. But make sure that does not impact the propagation of the exception to the caller.

                 

                The code contract between caller and local code is simple. If the local code fails or is unable to successfully complete its tasks, it MUST propagate an exception to inform the caller of that. Do not break this contract.

                • 5. Re: exception handling
                  smon

                  thanks for the replies. With regard to logging unhandled exceptions, as mentioned above my initial thinking is to let the exceptions propogate all the way back up to the client where they'd be caught and logged, rather than have x number of packages with logging calls in and having to re-raise back up the chain etc.

                   

                  Is there anything wrong with this idea? Exceptions will go back up the chain naturally, why stop them and re-raise when you could just catch and handle once, at the top?

                   

                  thanks again.

                  • 6. Re: exception handling
                    John Stegeman

                    smon,

                     

                    Either approach has its proponents - I think the important thing is to be consistent.

                     

                    I've done it both ways - one of the benefits of the catch-log-reraise approach is that you can have your logs centralised in the database. Another benefit is that if you view PL/SQL as your transactional API, you can build logging into that API and the apps themselves don't have to re-implement logging. One of the worst things you can do, in my opinion, is catch exceptions only to re-reraise them as a custom exception without putting the original exception information in there. I've seen it done, and it makes things so difficult to debug. Letting the exceptions bubble up to the very top is a simple and workable approach, as long as you ensure the clients (whatever is calling your API) can deal with exceptions (logging, display to the user without just crashing, etc).

                     

                    J

                    • 7. Re: exception handling
                      BluShadow

                      smon wrote:

                       

                      thanks for the replies. With regard to logging unhandled exceptions, as mentioned above my initial thinking is to let the exceptions propogate all the way back up to the client where they'd be caught and logged, rather than have x number of packages with logging calls in and having to re-raise back up the chain etc.

                       

                      Is there anything wrong with this idea? Exceptions will go back up the chain naturally, why stop them and re-raise when you could just catch and handle once, at the top?

                       

                      thanks again.

                       

                      Again... it depends.

                       

                      If you log calls locally before passing them up, then you may be logging an exception that is actually handled by some calling code, so never makes it to the top.  Of course, that depends if you're interested in logging such exceptions, or whether you want to only log exceptions that are not handled.

                       

                      As John says, either approach has its proponents because either approach is acceptable depending on your actual requirements.

                      • 8. Re: exception handling
                        smon

                        ok that's great, thanks all.