9-5. Raising User-Defined Exceptions Without an Exception Handler
Problem
Your application includes some error handling that is specific to your application. For instance, you want to ensure that the input value for a procedure is in the valid format to be an e-mail address. Rather than writing an exception handler for each user-defined exception, you want to simply raise the exception inline and provide an error number as well.
Solution
This scenario is perfect for using the RAISE_APPLICATION_ERROR procedure. Test the e-mail address that is passed into the procedure to ensure that it follows certain criteria. If it does not contain a specific characteristic of a valid e-mail address, use the RAISE_APPLICATION_ERROR procedure to display an exception message to the user. Here's an example:
CREATE OR REPLACE PROCEDURE obtain_emp_detail(emp_email IN VARCHAR2) IS
emp_qry VARCHAR2(500);
emp_first employees.first_name%TYPE;
emp_last employees.last_name%TYPE;
email employees.email%TYPE;
valid_id_count NUMBER := 0;
valid_flag BOOLEAN := TRUE;
temp_emp_info VARCHAR2(50);
BEGIN
emp_qry := 'SELECT FIRST_NAME, LAST_NAME, EMAIL FROM EMPLOYEES ';
IF emp_email LIKE '%@%' THEN
temp_emp_info := substr(emp_email,0,instr(emp_email,'@')-1);
emp_qry := emp_qry || 'WHERE EMAIL = :emp_email';
ELSIF emp_email NOT LIKE '%.mycompany.com' THEN
RAISE_APPLICATION_ERROR(-20001, 'Not a valid email address from ' ||
'this company!'),
ELSE
RAISE_APPLICATION_ERROR(-20002, 'Not a valid email address!'),
END IF;
IF valid_flag = TRUE THEN
EXECUTE IMMEDIATE emp_qry
INTO emp_first, emp_last, email
USING temp_emp_info;
DBMS_OUTPUT.PUT_LINE(emp_first || ' ' || emp_last || ' - ' || email);
ELSE
DBMS_OUTPUT.PUT_LINE('THE INFORMATION YOU HAVE USED DOES ' ||
'NOT MATCH ANY EMPLOYEE RECORD'),
END IF;
END;
As you can see, there is no exception handler in this example. When the conditions are met, an exception is raised inline via RAISE_APPLICATION_EXCEPTION.
How It Works
The RAISE_APPLICATION_EXCEPTION procedure can associate an error number with an error message. The format for calling the RAISE_APPLICATION_EXCEPTION procedure is as follows:
RAISE_APPLICATION_EXCEPTION(exception_number,
exception_message[, retain_error_stack]);
where exception_number is a number within the range of -20000 to -20999, and exception_message is a string of text that is equal to or less than 2KB in length. The optional retain_error_stack is a BOOLEAN value that tells Oracle whether this exception should be added to the existing error stack or whether the error stack should be wiped clean and this exception should be placed into it. By default, the value is FALSE, and all other exceptions are removed from the error stack, leaving this exception as the only one in the stack.
When you invoke the procedure, the current block is halted immediately, and the exception is raised. No further processing takes place within the current block, and control is passed to the program that called the block or an enclosing block if the current block is nested. Therefore, if you need to perform any exception handling, then it needs to take place prior to calling RAISE_APPLICATION_EXCEPTION. There is no commit or rollback, so any updates or changes that have been made will be retained if you decide to issue a commit. Any OUT and IN OUT values, assuming you are in a procedure or a function, will be reverted. This is important to keep in mind, because it will help you determine whether to use an exception handler or issue a call to RAISE_APPLICATION_ERROR.
When calling RAISE_APPLICATION_EXCEPTION, you pass an error number along with an associated exception message. Oracle sets aside the range of numbers from -20000 to -20999 for use by its customers for the purpose of declaring exceptions. Be sure to use a number within this range, or Oracle will raise its own exception to let you know that you are out of line and using one of its proprietary error numbers!
Note There are some numbers within that range that are still used by Oracle-specific exceptions. Passing a TRUE value as the last argument in a call to RAISE_APPLICATION_EXCEPTION will retain any existing errors in the error stack. Passing TRUE is a good idea for the purposes of debugging so that the stack trace can be used to help find the code that is raising the exception. Otherwise, the exception stack is cleared.
One may choose to create a function or procedure that has the sole purpose of calling RAISE_APPLICATION_EXCEPTION to raise an exception and associate an error number with an exception message. This technique can become quite useful if you are interested in using a custom error number for your exceptions, but you still need to perform proper exception handling when errors occur. You could use the OTHERS exception handler to call the function or procedure that uses RAISE_APPLICATION_EXCEPTION, passing the error number and a proper exception message.