The Changes for Exception Handling
Notice in Example 4-3 the addition of some screen output in the form of the call:
DBMS_OUTPUT
.
PUT_LINE
(
‘Success – we got here 1!’
);
Normally only developers will see this screen output, but it can also become visible to other users. As you’ll see, output from DBMS_OUTPUT.PUT_LINE is very useful for providing feedback during coding and testing.
The other main change in Example 4-3 is the addition of the following EXCEPTION block:
EXCEPTION
WHEN
NO_DATA_FOUND
THEN
—
catches
all
‘no data found’
errors
DBMS_OUTPUT
.
PUT_LINE
(
‘Ouch, we hit an exception’
);
ROLLBACK
;
WHEN
OTHERS
THEN
—
handles
all
other
errors
DBMS_OUTPUT
.
PUT_LINE
(
‘We hit a general exception’
);
ROLLBACK
;
Notice how the exception block is separate and distinct from the application code. This is a nice feature of the PL/SQL exception model in that the exceptions are handled after the application code section. Thus, there’s no exception-handling code mixed in with (and cluttering up) the application code.
This exception block handles two exceptions, namely NO_DATA_FOUND and OTHERS. A NO_DATA_FOUND exception can occur when a SELECT INTO statement returns no rows. The second exception (OTHERS) catches all other exceptions. The NO_DATA_FOUND exception clause is an example of a specific exception, i.e., it checks for exactly one type of (specific) exception.
The OTHERS exception clause is an example of a general exception. It checks for a large number of potential exceptions. This type of exception case is very powerful. If it is not used correctly, then we may inadvertently run into an egregious antipattern: swallowing all exceptions.
Don’t Swallow All Exceptions
It is rarely a good idea to attempt to swallow all exceptions. Why is this? Well, by swallowing all exceptions, the code is essentially saying this to the runtime system:
I can handle all imaginable exceptions that can occur.
That’s a very bold statement! The more likely outcome is that your code is actually saying this to the runtime system:
I am a black hole for almost all possible exceptions and, in the event of an error, it will be very difficult for you to figure out why the overall PL/SQL solution has failed.
To illustrate why great care is needed in relation to the use of the OTHERS clause, what should the code in this clause do if the following exception occurs?
STORAGE_ERROR
PL
/
SQL
runs
out
of
memory
or
memory
has
been
corrupted
.
An error such as this is, of course, a critical runtime system or platform problem. It is highly unlikely that the PL/SQL code can do anything other than hand this type of exception off to the caller.
It is an old principle of software development to not attempt to over-deliver. In other words, just aim to solve the problem at hand without trying to solve all future potential problems. This is why swallowing all exceptions is an egregious antipattern and will almost certainly come back to haunt you at the most inconvenient moment, such as late on a Friday evening or just before you are due to go on vacation.
Having said all that, the code in Example 4-3 is intentionally swallowing all exceptions! But this is purely for the purposes of illustration. I will apply further code changes to better handle the exceptions. For the moment, we’ll continue to use the bad approach just to get used to the mechanisms involved. So, let’s run the code and see what happens.