OracleBlog

I've got a checklist of tasks I like to complete before beginning a new project. One important item on my list is to decide on how my exceptions are going to be handled.

By deciding in advance, I can save time by implementing exception handling only once, and also have much more consistent code. I also find that once I begin a project, I'm focusing on the logic and can easily miss handling exceptions properly, so my code is much more robust thinking about it in advance.

So what is an exception?

Well an exception is more about what it represents rather than what it IS. The existence of an exception means that something unexpected has failed in the run-time execution of your code. It also means that this execution has stopped.

Actually, I am slightly oversimplying the case, because you can actually create and raise an exception yourself without an unexpected failure, but generally you are only going to do this if you have either found something wrong, or you want to infuriate whoever is maintaining your code.

Ok, so you've got an exception. Now what?

Good question. Your first choice is to do nothing. That will cause your code block to fail, giving whatever executed this block an opportunity to do something. If you executed the block from SQL*Plus, (edit) all the (non-COMMITed) work in your block and all (non-autonomous) sub-blocks will be rolled back by the database.

Your other option is to do something. Your only opportunity to do this is in the EXCEPTION section of your block. In this section you can examine the type of exception, and handle it however you please.

Briefly, it looks like this:

EXCEPTION
 WHEN ONE_KIND_OF_ERROR THEN
  -- Do something here
 WHEN OTHERS THEN
  -- Handle all left over cases here
END

Once you're in there, you can get information about your error by looking at SQLCODE and SQLERRM, which contain the number and a text message for the latest exception. Oddly enough (here's a little "gotcha") you can't just insert these values to a table, for reasons I discovered last July.

Incidentally, here is what the "do nothing" (edit or "hide the error") option looks like:

EXCEPTION WHEN OTHERS THEN NULL;

Edit: Read Tom Kyte's comments to learn why some people will strangle you with their bare hands if you ever do this.

I want to do something. Uhh ... what should I do?

That's an even better question, and one that not a lot of books or articles actually focus on. That's because you have to figure out for yourself what you want to do when something goes wrong. Do you want to write a message to a table that you can query later? Are there some business-specific steps you want to do? Do you want to email yourself? Do you want to write to a log? Do you want to send a text message to output?

Whatever you decide to do, don't forget to do any clean up, because this is your only chance. Like I said, execution of your code block is over, and there is no avoiding that, so if you have something to do, do it now!

What if I don't want the execution to stop?

Too bad! The only way around that is to decide what code falls into this category, and wrap it in its own, nested block. Let me explain (and demonstrate).

Let's say you have several tasks to perform, and regardless of whether the first one succeeds or not, you want to continue to do the other tasks. Since execution stops the moment you get an exception, this is a case where you should wrap the first task in its own block, and handle the exception there. It doesn't even have to be a separate function/procedure, you can just create an anonymous block right then and there, like this:

BEGIN
 BEGIN
  -- Do task one.
 EXCEPTION
  -- Handle it!
 END
 -- Do task two
 -- Do task three ...
EXCEPTION
 -- Handle exceptions from task two, three, ...
END

Don't forget to actually handle the exception in your nested block! As I said before, unhandled exceptions stop the execution of whatever called your block, too.

Incidentally, in some cases you actually want to stop the execution of the calling block, but you still want to do something right now in your current, failed block. But if you handle it, then the exception is gone, and the calling block will therefore just continue its execution. In that case, you can re-raise the exception. The only caveat is that whatever called your block will think that the exception came from your exception handling code, not the original point of failure. Keep that in mind.

Good stuff. So what do you do?

Personally, I like to define an exception package.

There I can do a few things. I like to define my own exceptions (including number and text message) using PRAGMA EXCEPTION_INIT. Then I can throw my custom exceptions around.

But more importantly I can write a few different procedures that can handle exceptions in standard ways, and then all I have to do in my exception blocks is choose which way I want to handle an error, and then send the required information (usually SQLCODE and SQLERRM) to these standard procedures.

Needless to say, I can put these packages together quite quickly by re-using tested, trusted code from previous projects.

A word on rollback

I mentioned previously that if you don't handle your exceptions, SQL*Plus will rollback your work. It is probably better for you if you decide what gets committed, and what gets rolled back, and when. Therefore you should be sure to include COMMIT (preferably with SAVEPOINTs) and ROLLBACK appropriately in your code. And while ROLLBACK is a topic for another day, I should at least tell you to be aware of autonomous transactions, which are special blocks that will commit its work right away, regardless of whether its calling block rolled back or not.

Whew, anything else?

Yeah, one more thing. Exceptions in the DECLARE block can't be caught in the same block, but rather by the calling block. Keep that in mind in your EXCEPTION block when your block calls blocks with a DECLARE section.

In closing, it's important to note that this is a huge topic, and I can't do it justice in one page. Fortunately Oracle has a great write-up on handling PL/SQL Errors in the PL/SQL User's Guide and Reference, see Chapter 7. It even includes a list of pre-defined exceptions and what they mean.

But the true master of PL/SQL Exception Handling is doubtlessly Steven Feuerstein. I urge you to buy his book on Oracle PL/SQL Programming, and check out Chapter 8 on exception handling. If at all possible, attend one of his live presentations.