Page 184
Listing 8.9 L_08_09.sqlRaising a No_Data_Found Exception with and Without an Exception Handler
SQL> set serveroutput on; SQL> declare 2 lname employee.last_name%type; 3 fname employee.first_name%type; 4 begin 5 select last_name, first_name 6 into lname, fname 7 from employee 8 where last_name = `BUCHANAN'; 9 end; 10 / declare * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 5 SQL> SQL> set serveroutput on; SQL> declare 2 lname employee.last_name%type; 3 fname employee.first_name%type; 4 begin 5 select last_name, first_name 6 into lname, fname 7 from employee 8 where last_name = `BUCHANAN'; 9 exception 10 when no_data_found then 11 dbms_output.put_line (`EMPLOYEE NOT FOUND'); 12 end; 13 / EMPLOYEE NOT FOUND PL/SQL procedure successfully completed. SQL>
The second block contains an exception section and an exception handler for the no_data_found exception. This block displays the message No Employee Found when the exception occurs. This is a much more preferable message to display when users execute the program.
Custom-named exceptions can make your programs easier to interpret. To name the exception, you must first declare an exception in the Declare section of the program. Next, you identify the named exception with an exception number. Table 8.1 lists standard exceptions. Each of the exceptions has a particular number. In fact, all Oracle errors have a number. The named
Page 185
exception is associated with this exception number. To identify the named exception with an exception number, use the pragma exception_init declaration. Pragma tells Oracle that the remainder of the statement is a directive to the compiler. Pragmas are special instructions to the compiler. The special instruction is the association of the new exception name with the Oracle error. Listing 8.10 illustrates a named exception called "way_too_many_rows". This exception will be associated to the _1422 error. This is the "way_too_many_rows" error.
Listing 8.10 L_08_10.sqlTXTDefining a User-Named Exception
SQL> set serveroutput on; SQL> declare 2 lname employee.last_name%type; 3 way_too_many_rows exception; 4 pragma exception_init (way_too_many_rows, -1422); 5 begin 6 select last_name 7 into lname 8 from employee; 9 exception 10 when way_too_many_rows then 11 dbms_output.put_line (`Too Many Records For The Value'); 12 end; 13 / Too Many Records For The Value PL/SQL procedure successfully completed.
In the third line, the exception "Way too many rows" is declared. This exception is associated to the ORA-01422 exception by using "pragma exception_init." The Select statement in the executable section attempts to place all the last name values from the Employee table into the local variable lname. This caused the ORA-01422 exception to occur. The application then moves to the exception section and performs the "way_too_many_rows" exception. The result of the exception is the message "Too Many Records For The Value."
You can use the Exception section for your own types of exceptions. You might have a program that is evaluating a set of records, for example. When an invalid condition exists in a record, you raise the exception through the use of an If statement. The exception handler causes the application to display a message telling the user an incorrect value exists. You can create an exception of this type by declaring and naming an exception in the Declare program. You do not have to create a pragma because this exception is not going to replace a standard exception. To use the exception, you place the exception name in the Executable section following the raise keyword. If a certain condition causes the exception to occur, you can place that condition in an If statement. When the exception name is encountered, the focus of the program shifts to the Exception section of the program. The exception handler is then executed. Listing 8.11 contains a program that looks at the first names of the employees. It attempts to find a
Page 186
misspelled first name. After the mistake is found, the exception "Incorrect First Name" is raised.
Listing 8.11 L_08_11.SQLRaising An Incorrect First Name Custom Exception
SQL> set serveroutput on; SQL> declare 2 incorrect_first_name exception; 3 fname employee.first_name%type; 4 lname employee.last_name%type; 5 cursor a is select last_name, first_name from employee; 6 begin 7 open a; 8 loop 9 fetch a into lname, fname; 10 dbms_output.put_line(fname); 11 if (a%notfound) then exit; 12 end if; 13 if (fname = `ELEANOR') then raise incorrect_first_name; 14 end if; 15 end loop; 16 exception 17 when incorrect_first_name then 18 dbms_output.put_line (fname||' `||lname||' has an incorrect spelling'); 19 end; 20 / CALVIN LYNDON . . SUSAN ELEANOR ELEANOR ROOSEVELT has an incorrect spelling PL/SQL procedure successfully completed.
In the Listing, the incorrect_first_name exception is named in the Declare section. An exception handler using this name is placed in the Exception section. The Executable section contains a cursor and a loop. This allows the program to look at each record in the Employee table until it finds a record with a first name value of `ELEANOR'. If this occurs before the cursor runs out of records, the custom exception Incorrect_First_Name is raised and the exception handler statements executed.
NOTE |
I write three types of PL/SQL programs. The first type consists of programs that I run from the SQL*PLUS prompt to convert data. I never put exceptions in this code because the program is used by me alone. The program will be thrown away when done and I know how to interpret the messages. Speed of writing these programs is more important than good error messages.The second type of program is one run in batch at night. There isn't anyone to see the messages at night, so I don't usually put messages in these.Those programs embedded in my Oracle Forms comprise the |
Page 187
third type of PL/SQL program I write. These programs are executed by the users I support. I always
put exception handlers and custom messages in these applications. It greatly increases the user-friendliness of the applications. The exception handlers I use cover the common database and Form errors the user may encounter. I can't, however, place a handler for all exceptions. I always use an "others" handler, which is a catch-all for any unnamed exception.I highly recommend that you include exception handlers, including the "others" exceptions, in all your Forms applications. Your users will appreciate your efforts.
PL/SQL is a Oracle's programming language. PL/SQL has three types of structures or
blocks: anonymous blocks, procedures, and functions. Anonymous blocks are not callable by
another object. Procedures are callable. Functions are also callable, but return a value that
procedures and anonymous blocks do not. A PL/SQL block contains several sections: the
Header section, which contains the name of the procedure and parameters; the
Declaration section, which
is used to name the local variables, exceptions, and cursors used in the application; the
Executable section, which contains the statements performed; and the
Exception section, which is used to handle errors. PL/SQL programs have several types of looping
procedures that allow them to perform the same section of code repeatedly. The simplest looping
procedure begins with the word loop and is completed by the phrase
end loop. Between the two, SQL statements perform functions and an exit command terminates the looping procedure.
Cursors are an important component of PL/SQL. They are used to retrieve a set of records one at
a time for processing. The Cursor is command declares the specifications of the cursor. The
Open command executes the cursor. The Fetch command retrieves values from the cursor to
local variables in the program. The Close command eliminates the cursor and frees memory.
Oracle has defined a number of standard errors. These errors are handled in the
Exception section of the program. You can rename these exceptions if you choose. You also cancreate your
own exceptions. Whenever either of these types of exceptions is encountered in the
Executable section, it causes the processor to skip to the
Exception section of the program and perform the statements in its handler.
The next chapter builds on what you learned in this chapter. It introduces you to the while loop and for loop. The for loop is an especially powerful loop that automatically performs many of the functions of a cursor. The next chapter also discusses the creation and use of named procedures and functions. It also covers methods of storing your procedures in the database.