RSS

7.6 RAISE_APPLICATION_ERROR

20 Apr

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.

Advertisements
 
Leave a comment

Posted by on 04/20/2010 in ORACLE

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: