Lab 11.1 RAISE_APPLICATION_ERROR

Lab Objective

After this Lab, you will be able to:

Use RAISE_APPLICATION_ERROR

RAISE_APPLICATION_ERROR is a special built-in procedure provided by Oracle. This procedure allows programmers to create meaningful error messages for a specific application. The RAISE_APLICATION_ERROR procedure works with user -defined exceptions. The syntax of the RAISE_APPLICATION_ERROR is

 RAISE_APPLICATION_ERROR(error_number, error_message); 

or

 RAISE_APPLICATION_ERROR(error_number, error_message,    keep_errors); 

As you can see, there are two forms of the RAISE_APPLICATION_ERROR procedure. The first form contains only two parameters: error_number and error_message. The error_number is a number of the error that a programmer associates with a specific error message, and can be any number between -20,999 and -20,000. The error_message is the text of the error, and it can contain up to 512 characters .

The second form of RAISE_APPLICATION_ERROR contains one additional parameter: keep_errors. Keep_errors is an optional Boolean parameter. If keep_errors is set to TRUE, the new error will be added to the list of errors that has been raised already. If keep_errors is set to FALSE, the new error replaces the list of errors that has been raised already. The default value for the parameter keep_errors is FALSE.

It is important for you 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.

Consider the following example used in Chapter 10. This example illustrates the use of the named user-defined exception and the RAISE statement. Within the example you will be able to compare a modified version using the unnamed user-defined exception and the RAISE_APPLICATION_ERROR procedure.

FOR EXAMPLE

First, view the original example from Chapter 10. Notice that the named user-defined exception and the RAISE statement are shown in bold letters .

 DECLARE    v_student_id STUDENT.STUDENT_ID%TYPE := &sv_student_id;    v_total_courses NUMBER;  e_invalid_id EXCEPTION;  BEGIN    IF v_student_id < 0 THEN  RAISE e_invalid_id;  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;    DBMS_OUTPUT.PUT_LINE ('No exception has been raised'); EXCEPTION    WHEN e_invalid_id THEN       DBMS_OUTPUT.PUT_LINE ('An id cannot be negative'); END; 

Now, compare the modified example as follows (changes are shown in bold letters):

 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; 

The second version of the example does not contain the name of the exception, the RAISE statement, nor the error-handling section of the PL/SQL block. Instead, it has a single RAISE_APPLICATION_ERROR statement.

Even though the RAISE_APPLICATION_ERROR is a built-in procedure, it can be referred to as a statement when used in the PL/SQL block.

Both versions of the example achieve the same result: The processing stops if a negative number is provided for v_student_id . However, the second version of this example produces the output that has the look and feel of an error message. Now, run both versions of the example with the value of -4 for the variable v_student_id .

The first version of the example produces the following output:

  Enter value for sv_student_id: -4   old   2: v_student_id STUDENT.STUDENT_ID%TYPE := &sv_student_id;   new   2: v_student_id STUDENT.STUDENT_ID%TYPE := -4;   An id cannot be negative   PL/SQL procedure successfully completed.  

The second version of the example produces the following output:

  Enter value for sv_student_id: -4   old   2:    v_student_id STUDENT.STUDENT_ID%TYPE := &sv_student_id;   new   2:    v_student_id STUDENT.STUDENT_ID%TYPE := -4;   DECLARE   *   ERROR at line 1:   ORA-20000: An id cannot be negative   ORA-06512: at line 6  

The output produced by the first version of the example contains the error message "An id cannot be negative" and the message "PL/SQL completed...". The error message "An id cannot..." in the output generated by the second version of the example looks like the error message generated by the system, because the error number ORA-20000 precedes the error message.

The RAISE_APPLICATION_ERROR procedure can work with built-in exceptions as well. Consider the following example:

FOR EXAMPLE

 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; 

When the value of 100 is entered for the student ID, the example produces the output shown:

  Enter value for sv_student_id: 100   old   2:    v_student_id STUDENT.STUDENT_ID%TYPE := &sv_student_id;   new   2:    v_student_id STUDENT.STUDENT_ID%TYPE := 100;   DECLARE   *   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 there is no record in the STUDENT table corresponding 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 important for you to note that 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 the 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. Once 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.