RSS

8.4 RAISE_APPLICATION_ERROR

02 May

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;

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 RAISE_APPLICATION_ERROR procedure can work with built-in exceptions as well. Consider the following 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 a value of 100 is entered for the student ID, the example produces the following output:

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 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 05/02/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: