Scale of Resilience Requirement 1: Capture All Errors and Exceptions
Referring to Table 4-1, the score for this requirement is 0. The basic problem in relation to error and exception handling in Example 4-2 is that there isn’t any! In other words, if an error or exception occurs, it will result in an immediate termination and exit. This is rarely a desirable outcome because it may prevent an orderly exit and could potentially lead to data inconsistencies.
Let’s apply a few minor additions, as shown in Example 4-3.
Example 4-3. Adding exception handling to the earlier version of the PL/SQL procedure
CREATE
OR
REPLACE
PROCEDURE
update_employees
IS
CURSOR
c1
is
SELECT
ename
,
empno
,
sal
FROM
emp
ORDER
BY
sal
DESC
;
— start with highest paid employee
my_ename
VARCHAR2
(
10
);
my_empno
NUMBER
(
4
);
my_sal
NUMBER
(
7
,
2
);
BEGIN
OPEN
c1
;
FOR
i
IN
1
..
5
LOOP
FETCH
c1
INTO
my_ename
,
my_empno
,
my_sal
;
EXIT
WHEN
c1
%
NOTFOUND
;
/* in case the number requested */
/* is more than the total */
/* number of employees */
DBMS_OUTPUT
.
PUT_LINE
(
‘Success – we got here 1!’
);
INSERT
INTO
temp
VALUES
(
my_sal
,
my_empno
,
my_ename
);
DBMS_OUTPUT
.
PUT_LINE
(
‘Successful insert!’
);
COMMIT
;
END
LOOP
;
CLOSE
c1
;
DBMS_OUTPUT
.
PUT_LINE
(
‘Success – we got here!’
);
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
;
END
;
Notice that, for the purpose of illustration, I’ve also reversed the original PL/SQL fix so that we are once again intentionally attempting to:
- Write the salary value into the ename column.
- Write the name into the salary column.
This bug just won’t go away! Obviously, I’m just using it for the purposes of illustration and you’ll soon see how to actually catch the exception raised by this bug.
I’ve also added a few extra lines to Example 4-3 using DBMS_OUTPUT to produce some developer output. Can you see the differences? I’ll break down the Example 4-3 changes in the next section.