Custom Exceptions
Handling Errors and Exceptions
Developing and Using Stored Procedures 4-43
Example 419 Handling exceptions
In the
emp_eval
Body pane, edit
eval_department
procedure to handle cases
where the query does not return a result set. New code is in bold font.
PROCEDURE eval_department(department_id IN employees.department_id%TYPE) AS
-- declaring the REF CURSOR
emp_cursor emp_refcursor_type;
department_curr departments.department_id%TYPE;
BEGIN
department_curr := department_id; -- starting with the first department
FOR loop_c IN 1..3 LOOP
OPEN emp_cursor FOR
SELECT *
FROM employees e
WHERE department_curr = e.department_id;
-- create employee evaluations is specific departments
DBMS_OUTPUT.PUT_LINE('Determining necessary evaluations in department #'
||
department_curr);
eval_loop_control(emp_cursor); -- call to process the result set
DBMS_OUTPUT.PUT_LINE('Processed ' || emp_cursor%ROWCOUNT || ' records.');
CLOSE emp_cursor;
department_curr := department_curr + 10;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('The query did not return a result set');
END eval_department;
Compile and save
emp_eval
Body.
Custom Exceptions
A package may contain custom exceptions for handling errors. Exceptions are declared
in the program, in any declarative region, depending on how it is used: a subprogram,
a package body, or a package specification.
ROWTYPE_MISMATCH
The host cursor variable and PL/SQL cursor variable involved in an
assignment have incompatible return types. When an open host cursor
variable is passed to a stored subprogram, the return types of the actual
and formal parameters must be compatible.
SUBSCRIPT_
BEYOND_COUNT
A program references a nested table or varray element using an index
number larger than the number of elements in the collection.
SUBSCRIPT_
OUTSIDE_LIMIT
A program references a nested table or varray element using an index
number (-1 for example) that is outside the legal range.
TOO_MANY_ROWS
A
SELECT
INTO
statement returns more than one row.
VALUE_ERROR
An arithmetic, conversion, truncation, or size-constraint error occurs.
For example, when your program selects a column value into a
character variable, if the value is longer than the declared length of the
variable, PL/SQL cancels the assignment and raises
VALUE_ERROR
. In
procedural statements,
VALUE_ERROR
is raised if the conversion of a
character string into a number fails. (In SQL statements,
INVALID_
NUMBER
is raised.)
ZERO_DIVIDE
A program attempts to divide a number by zero.
Exception
Description
