20 Apr

Lab Objective

After completing this lab, you will be able to


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);


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.


    v_student_id    student.student_id%type := &sv_student_id;

    v_total_courses NUMBER;


    IF v_student_id < 0 THEN

        RAISE_APPLICATION_ERROR(-20000, ‘An id cannot be negative’);


        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;


ERROR at line 1:

ORA-20000: An id cannot be negative

ORA-06512: at line 6




    v_student_id student.student_id%type := &sv_student_id;

    v_name varchar2(50);


    SELECT first_name||‘ ‘||last_name

        INTO v_name

        FROM student

        WHERE student_id = v_student_id;

    DBMS_OUTPUT.PUT_LINE (v_name);



        RAISE_APPLICATION_ERROR (-20001, ‘This ID is invalid’);


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.

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: Logo

You are commenting using your 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: