Previous | Table of Contents | Next

Page 184

Listing 8.9 L_08_09.sql—Raising 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.

Renaming Standard Errors with Your Own Names

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.sqlTXT—Defining 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."

Using the Exception Section for Your Own Exceptions

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.SQL—Raising 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.

Summary

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.

From Here…

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.

Review Exercises

  1. Create a PL/SQL anonymous block that displays the words "I am a PL/SQL guru".

  2. Create a PL/SQL anonymous block that computes and displays the average starting age of all employees.

Previous | Table of Contents | Next