Handling Custom Exceptions
Handling Errors and Exceptions
4-44 Oracle Database 2 Day Developer's Guide
An exception declaration has the following form:
exception_name
EXCEPTION;
To raise custom exceptions programmatically, based on incorrect values, you need to
use the following form:
IF
condition
THEN
RAISE
exception_name
;
To trap unexpected Oracle errors, you must include the exception handling
instructions in your code, typically as the last block within the body of your
subprogram or package. You should name the specific exceptions you are handling
(both standard and custom), and use the
OTHERS
handler to trap unexpected errors.
An exception body has the following form:
EXCEPTION
WHEN
exception_name_1
THEN
...;
DBMS_OUTPUT.PUT_LINE(
message_1
);
...
WHEN OTHERS THEN
...
DBMS_OUTPUT.PUT_LINE(
message_others
);
Alternatively, you may design your program to continue running after an exception is
raised. You must then enclose the code that may generate an exception in a
BEGIN...END
block with its own exception handler. For example, code that traps the
exception within a loop structure can handle the exception for an element that raises
an error, and then continue with the next iteration of the loop.
In the following task, you will redesign the function
calculate_score
to declare,
raise and trap two possible exceptions,
weight_wrong
and
score_wrong
.
Example 420 Handling custom exceptions
In the
emp_eval
Body pane, edit
calculate_score
function. New code is in bold
font.
FUNCTION calculate_score(evaluation_id IN scores.evaluation_id%TYPE
, performance_id IN scores.performance_id%TYPE)
RETURN NUMBER AS
weight_wrong EXCEPTION;
score_wrong EXCEPTION;
n_score scores.score%TYPE; -- from SCORES
n_weight performance_parts.weight%TYPE; -- from PERFORMANCE_PARTS
running_total NUMBER := 0; -- used in calculations
max_score CONSTANT scores.score%TYPE := 9; -- a constant limit check
max_weight CONSTANT performance_parts.weight%TYPE:= 1;
-- a constant limit check
BEGIN
SELECT s.score INTO n_score FROM scores s
WHERE evaluation_id = s.evaluation_id
AND performance_id = s.performance_id;
SELECT p.weight INTO n_weight FROM performance_parts p
WHERE performance_id = p.performance_id;
BEGIN -- check that weight is valid
IF n_weight > max_weight OR n_weight < 0 THEN
RAISE weight_wrong;
END IF;
END;
