After completing this lab, you will be able to
- Use built-in exceptions
SELECT first_name || ‘ ‘ || last_name
WHERE student_id = 101;
DBMS_OUTPUT.PUT_LINE (‘Student name is ‘||v_student_name);
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘There is no such student’);
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:
v_student_id NUMBER := &sv_student_id;
v_enrolled VARCHAR2(3) := ‘NO’;
DBMS_OUTPUT.PUT_LINE(‘Check if the student is enrolled’);
WHERE student_id = v_student_id;
DBMS_OUTPUT.PUT_LINE(‘The Student is enrolled into one course’);
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’);
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.
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘An error has occured’);