Wednesday, March 01, 2006

Handling exceptions

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:

-- Do something here
-- Handle all left over cases here

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:


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:

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

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.

This comment has been removed by a blog administrator.
I wish we didn't have when others.

the use of when others is probably the single largest cause of logic bugs in PLSQL.

"when others then null" is not the "do nothing" exception - it is almost certainly a bug in the developed code.

A when others that is not followed by RAISE to re-raise the exception is almost always a bug. You have just HIDDEN THE ERROR from the caller - they have no clue (don't even think about using return codes - people/code ignore those too)

I hate when others. Think about it - you DON'T KNOW WHAT the error was, you are not equipped to deal with it.

Also, you say:

SQL*Plus will rollback your work.

sqlplus doesn't do that, the database does. It makes all calls to the database 'atomic', the statement (the call) either succeeds or fails.

begin p; end;

that is the CALL to the database, if it fails, the work done by P will be undone - but not other work!! for example:

ops$tkyte@ORA10GR2> create table t ( x int check (x>0));
Table created.
ops$tkyte@ORA10GR2> create or replace procedure p(p_x in number)
2 as
3 begin
4 insert into t values(100);
5 insert into t values(p_x);
6 end;
7 /
Procedure created.

ops$tkyte@ORA10GR2> insert into t values (1);
1 row created.

ops$tkyte@ORA10GR2> exec p(-1);
BEGIN p(-1); END;
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.SYS_C007585) violated
ORA-06512: at "OPS$TKYTE.P", line 5
ORA-06512: at line 1

ops$tkyte@ORA10GR2> select * from t;

ops$tkyte@ORA10GR2> rollback;
Rollback complete.

See how the entire set of work done by P is undone (there are implicit savepoints wrapped around the procedure) but the work done before P was invoked IS NOT undone. It (the database) did not rollback your transaction -- it just makes your statements atomic.

Now, add that "when others" bug:

ops$tkyte@ORA10GR2> create or replace procedure p(p_x in number)
2 as
3 begin
4 insert into t values(100);
5 insert into t values(p_x);
6 exception
7 when others
8 then
9 dbms_output.put_line( 'I have utterly failed' );
10 end;
11 /
Procedure created.

ops$tkyte@ORA10GR2> insert into t values (2);
1 row created.

ops$tkyte@ORA10GR2> exec p(-1);
I have utterly failed
PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> select * from t;


See how you totally changed the behavior - you have half of P's work now - ugh.

I also wish plsql didn't have commit and rollback - only the invoking client knows when the transaction is complete. A simply piece of code doesn't.

Say you have an API to update an employee status and an API to change their address.

Now, you need a transaction that does both. If those API's had the audacity to commit (or rollback) - this would not be possible - you'd have to write yet another API to do it. Ugh.


sqlplus doesn't rollback your work, the database doesn't rollback your work, the database makes statements atomic.

when coding plsql you should thing 1,000,000 times before adding the words commit or rollback to your routines.
Tom said:

>> I also wish plsql didn't have commit and rollback - only the invoking client
>> knows when the transaction is complete. A simply piece of code doesn't.

Except that sometimes the invoking client _is_ PL/SQL, programs invoked by dbms_job , cron or some other automatic process. In those cases the PL/SQL has to be able to issue a commit or rollback. Of course, the proportion of PL/SQL that is at the top of the callstack and so needs to manage transactions is usually a fairly small slice of the total body of PL/SQL.
Thanks Tom! Addressing your points:

1. Avoiding use of "WHEN OTHERS"

I agree that I generally like to explicitly list the exceptions I consider possible. However, sometimes I don't care! If anything went wrong, just log it. That's when I'd use WHEN OTHERS. If I think I've anticipated every possible exception, I might still have a WHEN OTHERS so I can flag this new situation and I can go update my code with this new possibility.

2. "the use of when others is probably the single largest cause of logic bugs in PLSQL."

In my opinion, the single largest cause of logic bugs in PL/SQL is probably misunderstanding what NULL is.


You're right that you are merely hiding the errror. But that's not necessarily a bug. If that is what you've written, you are probably saying "exceptions aren't relevant to this block." You don't care if it succeeds or fails, and you don't want to bother any calling block. I'm not saying that's advisable ...

4. WHEN OTHERS followed by a (re-)RAISE)

Standard practise for me (except, of course, if I don't want to bother the calling block). The only problem is that the calling block thinks the error came from the exception area instead of the true source of the exception. Therefore, I always like to at least write something to a log before re-raising.

5. SQL*Plus doesn't roll back your work, the database does

I originally thought this I was merely guilty of an oversimplification - that SQL*Plus instructs the database to roll back, but upon reflection it crossed the line into falsehood. Plus I like your wording so much better, so I'm revising the original.

6. Your first example

In fairness, when I said "rollback your work" I meant the work within your block (and its non-autonomous sub-blocks) - not any work performed by any other block. So I'll replace "work" with "block" to make that clear.

7. Your second example

Of course the work won't be rolled back. You "handled" the exception yourself. Of course, you handled it by printing "I have utterly failed", but if that's how you chose to handle it, that's your call. As far as the calling block is concerned (in this case, it was right from SQL*Plus) there IS NO EXCEPTION and therefore no reason to rollback. If that's not the behaviour you want, then re-RAISE the exception, or ROLLBACK yourself.

Sometimes that is your desired behaviour. Ask yourself this, if you wanted the 100 INSERTed regardless of whether or not the second insert failed, how would you write that code? You could make the INSERT of 100 an autonomous sub-block (BLECH!), you could issue an immediate COMMIT (which you also poo-pood), or you could make the second insert a sub-block that CAUGHT ALL EXCEPTIONS. (Note: in this case, that second insert was all that remained, so it wasn't necessary to put it in a sub-block). Is there any other way to achieve your goal?

8. COMMIT and ROLLBACK within your code

This deserves a blog of its own, but I will grant you the final word: "when coding plsql you should thing 1,000,000 times before adding the words commit or rollback to your routines."
Robert --

if something goes wrong, feel free to log it but you better re-raise it. It would be irresponsible to do anything else. The TOP LEVEL caller needs to know. Period. when others then null is almost certainly A BUG.

Look at it this way - if it is OK to ignore any error, then basically it matters NOT if your routine runs or not - therefore JUST DON'T RUN YOUR ROUTINE AT ALL, it obviously does NOT MATTER.

If it doesn't matter if a block

a) runs all the way
b) not at all
c) partitally

then I would opt for NOT RUNNING IT AT ALL - it just doesn't matter.

Don't replace work with block replace it with STATEMENT as that is what gets rolled back - the failing STATEMENT submitted by the caller of the database - that statement might invoke hundreds of blocks (triggers, anonymous blocks, whatever). It is statement level atomicity.

The reason I'm being so picky here is because....

This is a huge cause of bugs, and needs serious deep attention to detail.

Showing the when others then null and calling it the null exception - I actually think of that as "irresponsible".

This is a very hard topic to deal with in snippets, this is important stuff. It doesn't seem like it is but it is very very very much misunderstood.

when others then null; <<=== almost certainly a bug.

I hear what you're saying here:

"If it doesn't matter if a block

a) runs all the way
b) not at all
c) partitally

then I would opt for NOT RUNNING IT AT ALL - it just doesn't matter."

But I have had situations where this was the case. Just because something doesn't matter doesn't mean you shouldn't do it.

For example, just because our email server is down and someone can't get an informational message about an update doesn't mean I want my whole nightly batch to fail.

Tell you what - I'll update my article to say you've got important thoughts about this, and link to your blog. Of course I'm assuming you're about to put something on your blog on this topic. :)

Oh, and what's the difference between a statement and a block?

Can you post a real world example where you didn't care if

a) it runs all of the way
b) not at all
c) partially

cause, if b is EVER true, then b can ALWAYS be true - no?


A statement is something issued by a client to the database.
That was a real-world example. There was a stored procedure that ran every night and did certain business-related modifications. One step involved sending an informational email to one of the analysts. Sometimes the mail server was down, in which case we merrily continued our modifications. We certainly didn't want to abort our tasks just because we couldn't send an email! If the analyst didn't get an email, he'd check if the mail server was down, and then he'd check the data.

Yes, we could forget about emailing the analyst altogether, but that email spared him the time it would take to check that certain steps were completed.

Don't get me wrong, I do see your point, but I still think that "I don't care" is a legitimate case.
But the sending of the email would raise a set of KNOWN exceptions that you could deal with. You did not need a when others, what if the exception was "ora-4031", or something else as serious.

continue? I think not.

And it seems the analyst would "sometimes get email, sometimes not". Seems analyst should have been provided with LINK they could click on to generate said report at their leisure (so you could see if said analyst actually READ the report, so the analyst could be ASSURED of always getting the report if they wanted it)

The day that email didn't show up, what does that poor analyst do?

That, and the email should have been scheduled via DBMS_JOB - to make it transactional with regards to the rest of the larger transaction. When you commit - the job would be free to run and if it failed, the system would keep trying until it did or the job broke and then the DBA fixes the issue.

So, I would still say "when others = evil", even in this rather simple example.

The analyst is hosed when the mail doesn't get sent.

You don't know what the error was further downstream in your application (what if it had nothing to do with the email - but an oracle error of a serious nature, you can catch the SMTP related - documented exceptions)

The sending of the email should have been made transactional via dbms_job anyway (meaning the SMTP error would never happen as part of your transaction).

When others then null -> almost certainly a bug and I would never simply write:

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



and leave it at that. Too many people read too much stuff and pick up too many bad habits - this when others, big big big bad habit.

Sorry, this "when others" stuff, along with abuse of autonomous transactions coupled with lack of binds - my TOP THREE PET PEEVES.

Add triggers to round out the top four, wish they did not exist at this point in my life.

I use triggers all the time. Show me a better way to enforce a business contraint that involves more than one table, or something non-trivial, and I'll switch!

Use Constraints
Variables in Constraints

I'll make one final edit to add a word of warning on that line you find particularly dangerous. We both agree its to be avoided, we just disagree on the degree of absolution.

I will tell you one of my pet peeves, though. When something trivial fails, lets exceptions slip out, and I have to abort and rollback non-dependent, important work that otherwise would have succeeded.

If it failed because of something important, then it will soon affect something important and can get handled then.

That's why its legitimate to insist that certain blocks include WHEN OTHERS. It is your way of saying "This is not a deal-breaker, I do not want other stuff rolling back or aborting because of it." Use it wisely (or, I can concede, don't use it at all).

Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?