9-8. Associating Error Numbers with Exceptions That Have No Name
Problem
You want to associate an error number to those errors that do not have predefined names.
Solution
Make use of PRAGMA EXCEPTION_INIT to tell the compiler to associate an Oracle error number with an exception name. This will allow the use of an easy-to-identify name rather than an obscure error number when working with the exception. The example in this recipe shows how an error number can be associated with an exception name and how the exception can later be raised.
CREATE OR REPLACE FUNCTION calculate_salary_hours(salary IN NUMBER,
hours IN NUMBER DEFAULT 1)
RETURN NUMBER AS
BEGIN
RETURN salary/hours;
END;
DECLARE
DIVISOR_IS_ZERO EXCEPTION;
PRAGMA EXCEPTION_INIT(DIVISOR_IS_ZERO, -1476);
per_hour NUMBER;
BEGIN
SELECT calculate_salary_hours(0,0)
INTO per_hour
FROM DUAL;
EXCEPTION WHEN DIVISOR_IS_ZERO THEN
DBMS_OUTPUT.PUT_LINE('You cannot pass a zero for the number of hours'),
END;
The exception declared within this example is associated with the ORA-01476 error code. When a divide-by-zero exception occurs, then the handler is executed.
How It Works
PRAGMA EXCEPTION_INIT allows an error number to be associated with an error name. Thus, it provides an easy way to handle those exceptions that are available only by default via an error number. It is much easier to identify an exception by name rather than by number, especially when you have been away from the code base for some length of time.
The PRAGMA EXCEPTION_INIT must be declared within the declaration section of your code. The exception that is to be associated with the error number must be declared prior to the PRAGMA declaration. The format for using PRAGMA EXCEPTION_INIT is as follows:
DECLARE
exception_name EXCEPTION;
PRAGMA EXCEPTION_INIT(exception_name, <<exception_code>>);
BEGIN
-- Perform statements
EXCEPTION
WHEN exception_name THEN
-- Perform error handling
END;
The exception_name in this pseudocode refers to the name of the exception you are declaring. The <<exception_code>> is the number of the ORA-xxxxx error that you are associating with the exception. In the solution to this recipe, ORA-01476 is associated with the exception. That exception in particular denotes divisor is equal to zero. When this exception is raised, it is easier to identify the cause of the error via the DIVISOR_IS_ZERO identifier than by the -01476 code.
Whenever possible, it is essential to provide an easy means of identification for portions of code that may be difficult to understand. Exception numbers by themselves are not easily identifiable unless you see the exception often enough. Even then, an exception handler with the number -01476 in it seems obscure. In this case, it is always best to associate a more common name to the exception so that the code can instantly have meaning to someone who is unfamiliar with the code or to you when you need to maintain the code for years to come.