Lab Objective
After completing this lab, you will be able to
- Use RAISE_APPLICATION_ERROR
RAISE_APPLICATION_ERROR is a special built-in procedure provided by Oracle. It allows programmers to create meaningful error messages for a specific application. The RAISE_APPLICATION_ERROR procedure works with user-defined exceptions; its syntax is
RAISE_APPLICATION_ERROR(error_number, error_message);
or
RAISE_APPLICATION_ERROR(error_number, error_message, keep_errors);
As you can see, the RAISE_APPLICATION_ERROR procedure has two forms. The first form contains only two parameters: error_number and error_message. error_number is a number that a programmer associates with a specific error message. It can be any number between –20,999 and –20,000. error_message is the text of the error; it can contain up to 2,048 characters.
The second form of RAISE_APPLICATION_ERROR contains one additional parameter: keep_errors, which is an optional Boolean parameter. If keep_errors is set to TRUE, the new error is added to the list of errors that have been raised already. This list of errors is called the error stack. If keep_errors is set to FALSE, the new error replaces the error stack that has been raised already. The default value for the parameter keep_errors is FALSE. It is important to note that the RAISE_APPLICATION_ERROR procedure works with unnamed user-defined exceptions. It associates the number of the error with the text of the error. Therefore, the user-defined exception does not have a name associated with it.
DECLARE
v_student_id student.student_id%type := &sv_student_id;
v_total_courses NUMBER;
BEGIN
IF v_student_id < 0 THEN
RAISE_APPLICATION_ERROR(-20000, ‘An id cannot be negative’);
ELSE
SELECT COUNT(*)
INTO v_total_courses
FROM enrollment
WHERE student_id = v_student_id;
DBMS_OUTPUT.PUT_LINE(‘The student is registered for ‘ || v_total_courses || ‘ courses’);
END IF;
END;
ERROR at line 1:
ORA-20000: An id cannot be negative
ORA-06512: at line 6
DECLARE
v_student_id student.student_id%type := &sv_student_id;
v_name varchar2(50);
BEGIN
SELECT first_name||‘ ‘||last_name
INTO v_name
FROM student
WHERE student_id = v_student_id;
DBMS_OUTPUT.PUT_LINE (v_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20001, ‘This ID is invalid’);
END;
ERROR at line 1:
ORA-20001: This ID is invalid
ORA-06512: at line 12
The built-in exception NO_DATA_FOUND is raised because no record in the STUDENT table corresponds to this value of the student ID. However, the number of the error message does not refer to the exception NO_DATA_FOUND. It refers to the error message This ID is invalid.
The RAISE_APPLICATION_ERROR procedure allows programmers to return error messages in a manner that is consistent with Oracle errors. However, it is up to a programmer to maintain the relationship between the error numbers and the error messages. For example, you have designed an application to maintain enrollment information on students. In this application you have associated the error text This ID is invalid with the error number ORA-20001. This error message can be used by your application for any invalid ID. After you have associated the error number (ORA-20001) with a specific error message (This ID is invalid), you should not assign this error number to another error message. If you do not maintain the relationship between error numbers and error messages, the error-handling interface of your application might become very confusing to the users and to yourself.
Xavier Ding 