You cannot account for all exceptional cases unless you have a ton of time and money. The more interconnects in a software system the more complex it is and the more it take to cover even the standard test cases. We aren't even necessarily talking about code you wrote. Remember anytime your software isn't operating purely out of memory (that doesn't grow) you will have many more exception cases. Accessing network, disk, display, any other devices can all cause an exception. Growing memory can cause an exception. In some cases even detecting that the software was improperly stopped may be an exception case. The question is do you really need to cover every exception case.
If your working on software where lives hang in the balance, literally. Then yeah, you probably do need to at least consider every exception cases. Surgery software, Space Shuttle software, weapon system software, etc. In each of these cases there is often explicit analysis of what the all the exceptions are and if they need to be covered. These kinds of projects often end up spending alot more time and money to cover these cases. That is the cost of covering "all" exception cases.
Are you working on a social networking software. A Facebook app, an iPhone app, some motorcycle enthusiast site, etc. This kind of software doesn't usually warrant the kind of consideration I talked about above. Usually there is a cost to getting to the market late, and covering even some of the exceptional cases may be prohibitively expensive. This can often be a decision that will have business implications, especially in a startup. Do you cover all those cases and drive your company out of business, or lose a large portion of revenue?
So the answer is "it depends". It depends on the needs of the company, the cost of those exceptions, the cost of the extra time and effort required to guard against those exceptions, and the time involved.
Ideally you strike a balance. Using practices like automated tests to get at least as much coverage on your code as possible without too much cost in time or money. You can use practices like unit testing, functional testing, fuzz testing, etc; so that the software isn't too buggy, that it's easy to maintain and extend, and so you can look at it and take some pride in what you wrote.