RSS

7.2 Built-in Exceptions

11 Apr

After completing this lab, you will be able to

  • Use built-in exceptions

DECLARE

    v_student_name  VARCHAR2(50);

BEGIN

    SELECT first_name || ‘ ‘ || last_name

        INTO v_student_name

        FROM student

        WHERE student_id = 101;

    DBMS_OUTPUT.PUT_LINE (‘Student name is ‘||v_student_name);

EXCEPTION

    WHEN NO_DATA_FOUND THEN

        DBMS_OUTPUT.PUT_LINE(‘There is no such student’);

END;

 

You have probably noticed that, although every Oracle runtime error has a number associated with it, it must be handled by its name in the exception-handling section. One of the outputs from the example used in the previous lab in this chapter has the following error message:

ORA-01476: divisor is equal to zero

where ORA-01476 is the error number. This error number refers to the error named ZERO_DIVIDE. Some common Oracle runtime errors are predefined in PL/SQL as exceptions.

The following list describes some commonly used predefined exceptions and how they are raised:

  • NO_DATA_FOUND: This exception is raised when a SELECT INTO statement that makes no calls to group functions, such as SUM or COUNT, does not return any rows. For example, suppose you issue a SELECT INTO statement against the STUDENT table where student ID equals 101. If there is no record in the STUDENT table passing this criteria (student ID equals 101), the NO_DATA_FOUND exception is raised. When a SELECT INTO statement calls a group function, such as COUNT, the result set is never empty. When used in a SELECT INTO statement against the STUDENT table, function COUNT returns 0 for the value of student ID 123. Hence, a SELECT statement that calls a group function never raises the NO_DATA_FOUND exception.
  • TOO_MANY_ROWS: This exception is raised when a SELECT INTO statement returns more than one row. By definition, a SELECT INTO can return only a single row. If a SELECT INTO statement returns more than one row, the definition of the SELECT INTO statement is violated. This causes the TOO_MANY_ROWS exception to be raised. For example, suppose you issue a SELECT INTO statement against the STUDENT table for a specific zip code. There is a good chance that this SELECT statement will return more than one row, because many students can live in the same zip code area.
  • ZERO_DIVIDE: This exception is raised when a division operation is performed in the program and a divisor is equal to 0. An example in the previous lab of this chapter illustrated how this exception is raised.
  • LOGIN_DENIED: This exception is raised when a user tries to log in to Oracle with an invalid username or password.
  • PROGRAM_ERROR: This exception is raised when a PL/SQL program has an internal problem.
  • VALUE_ERROR: This exception is raised when a conversion or size mismatch error occurs. For example, suppose you select a student’s last name into a variable that has been defined as VARCHAR2(5). If the student’s last name contains more than five characters, the VALUE_ERROR exception is raised.

 

  • DUP_VALUE_ON_INDEX: This exception is raised when a program tries to store a duplicate value in the column or columns that have a unique index defined on them. For example, suppose you are trying to insert a record into the SECTION table for course number 25, section 1. If a record for the given course and section number already exists in the SECTION table, the DUP_VAL_ON_INDEX exception is raised, because these columns have a unique index defined on them.

 

So far, you have seen examples of programs that can handle only a single exception. For example, a PL/SQL block contains an exception handler with a single exception ZERO_DIVIDE. However, many times you need to handle different exceptions in the PL/SQL block. Moreover, often you need to specify different actions that must be taken when a particular exception is raised, as the following example illustrates:

DECLARE

    v_student_id    NUMBER := &sv_student_id;

    v_enrolled      VARCHAR2(3) := ‘NO’;

BEGIN

    DBMS_OUTPUT.PUT_LINE(‘Check if the student is enrolled’);

    SELECT ‘YES’

        INTO v_enrolled

        FROM enrollment

        WHERE student_id = v_student_id;

    DBMS_OUTPUT.PUT_LINE(‘The Student is enrolled into one course’);

EXCEPTION

    WHEN NO_DATA_FOUND THEN

        DBMS_OUTPUT.PUT_LINE(‘The student is not enrolled’);

    WHEN TOO_MANY_ROWS THEN

        DBMS_OUTPUT.PUT_LINE(‘The student is enrolled in too many courses’);

END;

 

So far, you have seen examples of exception-handling sections that have particular exceptions, such as NO_DATA_FOUND and ZERO_DIVIDE. However, you cannot always predict what exception might be raised by your PL/SQL block. For cases like this, there is a special exception handler called OTHERS. All predefined Oracle errors (exceptions) can be handled with the use of the OTHERS handler.

EXCEPTION

    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE(‘An error has occured’);

Advertisements
 
Leave a comment

Posted by on 04/11/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: