Listing 11.4. continued

  7  Additional_Fees = to_number(Bogus_Value);
  8
  8  exception
  9    when INVALID_NUMBER then
 10      dbms_output.put_line(`INVALID_NUMBER exception raised');
 11  end;
 12  /
INVALID_NUMBER exception raised

PL/SQL procedure successfully completed.

The NO_DATA_FOUND Exception

The NO_DATA_FOUND exception is raised when a SELECT statement doesn't return any rows, as shown in Listing 11.5.

Listing 11.5. The NO_DATA_FOUND exception is raised without an exception handler.

SQL> declare
  2
  2  Course_Rec  Course%ROWTYPE;
  3
  3  begin
  4
  4  dbms_output.enable;
  5
  5  select *
  6  into Course_Rec
  7  from Course
  8  where
  9  Course_ID = `777';
 10
 10  end;
 11  /
declare
 *
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5

After you add an exception handler for NO_DATA_FOUND (in line 11), PL/SQL no longer returns the error—no data returned—to the calling environment, as shown in Listing 11.6.

Listing 11.6. Handling the NO_DATA_FOUND exception.

SQL> declare
  2
  2  Course_Rec Course%ROWTYPE;
  3
  3  begin
  4
  4  dbms_output.enable;
  5
  5  select *
  6  into Course_Rec
  7  from Course
  8  where
  9  Course_ID = `777';
 10
 10  exception
 11    when NO_DATA_FOUND then
 12      dbms_output.put_line(`No data returned');
 13    when OTHERS then
 14      NULL;
 15  end;
 16  /
No data returned

PL/SQL procedure successfully completed.

The TOO_MANY_ROWS Exception

In the PL/SQL environment, a SELECT statement cannot retrieve more than one row without raising the TOO_MANY_ROWS exception. To retrieve an arbitrary number of rows from a query, you can use a cursor, which you can think of as a window on the results returned by a query. Listing 11.7 provides an example of how an exception handler is used for the TOO_MANY_ROWS exception.

Listing 11.7. Handling the TOO_MANY_ROWS exception.

SQL> declare
  2
  2  Course_Rec   Course%ROWTYPE;
  3
  3  begin
  4
  4  dbms_output.enable;
  5
  5  select *
  6  into Course_Rec
  7  from Course
  8  where

                                                     continues

Listing 11.7. continued

  9  Department_ID = `BIO';
 10
 10  exception
 11    when TOO_MANY_ROWS then
 12      dbms_output.put_line(`TOO_MANY_ROWS raised - use a cursor');
 13    when OTHERS then
 14      NULL;
 15  end;
 16  /
TOO_MANY_ROWS raised - use a cursor

PL/SQL procedure successfully completed.

The VALUE_ERROR Exception

The VALUE_ERROR exception is raised in a number of situations related to truncation and conversion errors. For example, Listing 11.8 illustrates a PL/SQL block (beginning with the first line) that tries to assign the string More than 5 characters to a variable that has been declared as VARCHAR2(5).

Listing 11.8. Handling the VALUE_ERROR exception.

SQL> declare
  2
  2  xyz  varchar2(5);
  3
  3  begin
  4
  4  dbms_output.enable;
  5
  5  xyz := `More than 5 characters';
  6
  6  exception
  7
  7    when VALUE_ERROR then
  8      dbms_output.put_line(`VALUE_ERROR raised');
  9
  9    when OTHERS then
 10      NULL;
 11
 11  end;
 12  /
VALUE_ERROR raised

Declaring an Exception

In addition to dealing with the pre-defined exceptions, you also can define application-specific exceptions and declare them in the following way:

exception-name EXCEPTION;

The variable exception-name is the declared exception and subject to PL/SQL object-naming restrictions.

Listing 11.9 provides an example that declares an exception named Life_Threatening_Fever that is raised if a patient's body temperature exceeds 106 degrees Fahrenheit.

Listing 11.9. Declaring an application exception.

SQL> declare
  2
  2  Life_Threatening_Fever  exception;
  3  Patient_ID   Patient.Patient_ID%TYPE;
  4
  4  begin
  5
  5  dbms_output.enable;
  6
  6  for Patient_Rec in
  7    (select Patient_ID, Body_Temp_Deg_F from Patient) loop
  8
  8    if Patient_Rec.Body_Temp_Deg_F > 106.0 then
  9
  9      Patient_ID := Patient_Rec.Patient_ID;
 10      raise Life_Threatening_Fever;
 11
 11    end if;
 12  end loop;
 13
 13  exception
 14
 14    when Life_Threatening_Fever then
 15      dbms_output.put_line(Patient_ID || ` has a life ` ||
 16                           `threatening fever!');
 17
 17  end;
 18  /
GG9999 has a life threatening fever!

Success or Failure: Inspecting SQLCODE and SQLERRM

SQLCODE is a pre-defined symbol that contains the Oracle error status of the previously executed PL/SQL statement. If an SQL statement executes without errors, SQLCODE is equal to 0.

SQLERRM is a PL/SQL symbol that contains the error message associated with SQLCODE. If an SQL statement executes successfully, SQLCODE is equal to 0 and SQLERRM contains the string ORA-0000: normal, successful completion, as shown in Listing 11.10.

Listing 11.10. Referencing SQLCODE and SQLERRM.

SQL> declare
  2
  2  begin
  3
  3  dbms_output.enable;
  4
  4  dbms_output.put_line(`SQLCODE: ` || to_char(SQLCODE));
  5  dbms_output.put_line(`SQLERRM: ` || SQLERRM);
  6
  6  end;
  7  /
SQLCODE: 0
SQLERRM: ORA-0000: normal, successful completion

If an error actually occurs, SQLCODE and SQLERRM contain the applicable code and message, respectively—as shown in the output of Listing 11.11.

Listing 11.11. Referencing SQLCODE and SQLERRM in the exception section.

SQL> declare
  2  Class_Rec   Class%ROWTYPE;
  3
  3  begin
  4
  4  dbms_output.enable;
  5
  5  select *
  6  into Class_Rec
  7  from Class;
  8
  8  exception
  9    when OTHERS then
 10      dbms_output.put_line(`SQLCODE: ` || to_char(SQLCODE));

 11      dbms_output.put_line(SQLERRM);
 12  end;
 13  /
SQLCODE: -1422
ORA-01422: exact fetch returns more than requested number of rows

PL/SQL procedure successfully completed.

Returning Errors with
RAISE_APPLICATION_ERROR

Oracle provides a procedure named RAISE_APPLICATION_ERROR in the DBMS_STANDARD package. You can use this procedure to return application-specific error messages to a caller—such as SQL*Plus, a PL/SQL subprogram, or a client application. Oracle reserves error codes in the range of _20000 to _20999 for these user-defined errors. For instance, Listing 11.12 illustrates a PL/SQL block that declares an exception named Fever_Out_of_Range. A cursor FOR LOOP (in line 6) reads through each row in the Patient table. If a patient's temperature exceeds 115 degrees Fahrenheit, the Fever_Out_of_Range exception is raised (in line 8). In the exception section, the exception handler for Fever_Out_of_Range calls RAISE_APPLICATION_ERROR and passes it an error code of _20000 and a relevant error message (in line 14).

Listing 11.12. Raising an application-specific exception.

SQL> declare
  2
  2  Fever_Out_of_Range exception;
  3  Patient_ID         Patient.Patient_ID%TYPE;
  4
  4  begin
  5
  5  dbms_output.enable;
  6
  6  for Patient_Rec in
  7    (select Patient_ID, Body_Temp_Deg_F from Patient) loop
  8
  8    if Patient_Rec.Body_Temp_Deg_F > 115.0 then
  9
  9      raise Fever_Out_of_Range;
 10
 10    end if;
 11
 11  end loop;
 12
 12  exception
 13
 

                                                     continues