The Oracle Error Messages and Codes manual lists all error codes and messages, not including operating-specific errors. Your application will probably encounter some of these errors at some point. PL/SQL refers to Oracle errors as exceptions. Some of the exceptions have predefined names that can be referenced in PL/SQL subprograms. In addition to these predefined Oracle exceptions, you can define application-specific exceptions in a PL/SQL subprogram.
One method for handling errors in a PL/SQL subprogram is to check for any Oracle error code after each SQL statement. The problem with this approach is that the resulting subprogram can be difficult to follow. As an alternative, PL/SQL enables you to specify what processing should take place for a particular exception. This section of the PL/SQL subprogram is called the exception section. A predefined exception is said to be "raised" when an Oracle error occurs during the execution of a PL/SQL subprogram. You raise a user-defined exception by invoking the RAISE statement at an appropriate location in the PL/SQL code.
This chapter explains how to define an exception handler for Oracle errors and user-defined exceptions and presents some examples of predefined exceptions.
The exception section is an optional section of a PL/SQL subprogram that tells PL/SQL how to handle particular exceptions. The syntax for the exception section is
EXCEPTION
WHEN exception-name1 THEN
PL/SQL-statements;
...
WHEN exception-nameN THEN
PL/SQL-statements;
...
[WHEN OTHERS THEN
PL/SQL-statements;]
END;
where exception-name1 through exception-nameN are the names of predefined and user-defined exceptions and PL/SQL-statements is one or more PL/SQL statements that are executed when the exception is raised.
To illustrate, here is a PL/SQL block that contains an exception section. Notice that the exception section contains two exception handlers: one for a predefined exception--;the TOO_MANY_ROWS exception--;and one for all other exceptions--;signified by the word OTHERS.
SQL> declare
2
2 Product_Rec Product%ROWTYPE;
3
3 begin
4
4 dbms_output.enable;
5
5 select *
6 into Product_Rec
7 from Product;
8
8 exception
9
9 when TOO_MANY_ROWS then
10 dbms_output.put_line('TOO_MANY_ROWS Exception Raised');
11 dbms_output.put_line('Occurred in anonymous block');
12
12 when OTHERS then
13 NULL;
14
14 end;
15 /
TOO_MANY_ROWS Exception Raised
Occurred in anonymous block
If you remove the exception handler for OTHERS and cause an exception to be raised that does not have an exception handler, PL/SQL returns an error message. In the following example, a string of 18 characters is assigned to a variable that can store up to 5 characters, resulting in an Oracle error.
SQL> declare
2
2 xyz varchar2(5);
3
3 begin
4
4 dbms_output.enable;
5
5 xyz := 'This will not fit!';
6
6 exception
7
7 when TOO_MANY_ROWS then
8 dbms_output.put_line('TOO_MANY_ROWS Exception Raised');
9 dbms_output.put_line('Occurred in anonymous block');
10
10 end;
11 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 5
Exceptions are either predefined or user defined. Predefined exceptions are automatically raised; for example, a SQL statement that references a table that doesn't exist results in an Oracle error. Predefined exceptions have meaningful names. Here are some predefined exceptions that you might encounter in developing an Oracle application:
The following sections explain the conditions that cause these predefined exceptions.
The DUP_VAL_ON_INDEX is raised when a SQL statement attempts to create a duplicate value in a column on which a unique index exists. To illustrate, the following PL/SQL anonymous block tries to update the product table so that all rows have the same value for Product_ID and Manufacturer_ID, thereby raising the DUP_VAL_ON_INDEX exception.
SQL> declare
2
2 begin
3
3 dbms_output.enable;
4
4 update Product
5 set
6 Product_ID = 'A1234',
7 Manufacturer_ID = 'SEN101';
8
8 exception
9
9 when DUP_VAL_ON_INDEX then
10 dbms_output.put_line('DUP_VAL_ON_INDEX exception raised');
11
11 end;
12 /
DUP_VAL_ON_INDEX exception raised
The INVALID_NUMBER exception is raised when a SQL statement specifies an invalid number. For instance, the following example attempts to update a table in which a numeric column, Initial_Retail_Value, is assigned a value that can't be converted to a legal number.
SQL> declare
2
2 Bogus_Value varchar2(30) := 'NOT A NUMBER';
3
3 begin
4
4 dbms_output.enable;
5
5 update Product
6 set
7 Initial_Retail_Value = to_number(Bogus_Value);
8
8 exception
9
9 when INVALID_NUMBER then
10 dbms_output.put_line('INVALID_NUMBER exception raised');
11
11 end;
12 /
INVALID_NUMBER exception raised
The NO_DATA_FOUND exception is raised when a SELECT statement doesn't return any rows, as shown:
SQL> declare 2 2 Product_Rec Product%ROWTYPE; 3 3 begin 4 4 dbms_output.enable; 5 5 select * 6 into Product_Rec 7 from Product 8 where 9 Product_ID = 'NOSUCH'; 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, PL/SQL no longer returns the error to the calling environment--;in this case, SQL*Plus.
SQL> declare
2
2 Product_Rec Product%ROWTYPE;
3
3 begin
4
4 dbms_output.enable;
5
5 select *
6 into Product_Rec
7 from Product
8 where
9 Product_ID = 'NOSUCH';
10
10 exception
11
11 when NO_DATA_FOUND then
12 dbms_output.put_line('No data returned');
13
13 when OTHERS then
14 NULL;
15
15 end;
16 /
No data returned
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. Chapter 22, "Retrieving Data with Cursors," focuses on the use of cursors in PL/SQL. Here is an example of how an exception handler is used for the TOO_MANY_ROWS exception.
SQL> declare
2
2 Product_Rec Product%ROWTYPE;
3
3 begin
4
4 dbms_output.enable;
5
5 select *
6 into Product_Rec
7 from Product
8 where
9 Manufacturer_ID = 'SEN101';
10
10 exception
11
11 when TOO_MANY_ROWS then
12 dbms_output.put_line('TOO_MANY_ROWS raised - use a cursor');
13
13 when OTHERS then
14 NULL;
15
15 end;
16 /
TOO_MANY_ROWS raised - use a cursor
The VALUE_ERROR exception is raised in a number of situations related to truncation and conversion errors. For example, the following PL/SQL block attempts to assign the string More than 5 characters to a variable that has been declared as VARCHAR2(5).
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
In addition to dealing with the predefined exceptions, you can also define application-specific exceptions and declare them as
exception-name EXCEPTION;
where exception-name is the declared exception and subject to PL/SQL object-naming restrictions.
The following example declares an exception named Life_Threatening_Fever that is raised if a patient's body temperature exceeds 106 degrees Fahrenheit.
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!
SQLCODE is a predefined symbol that contains the Oracle error status of the previously executed PL/SQL statement. If a 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 a SQL statement executes successfully, SQLCODE is equal to 0 and SQLERRM contains the string ORA-0000: normal, successful completion, as shown:
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.
SQL> declare
2
2 Product_Rec Product%ROWTYPE;
3
3 begin
4
4 dbms_output.enable;
5
5 select *
6 into Product_Rec
7 from Product;
8
8 exception
9
9 when OTHERS then
10 dbms_output.put_line('SQLCODE: ' || to_char(SQLCODE));
11 dbms_output.put_line(SQLERRM);
12
12 end;
13 /
SQLCODE: -1422
ORA-01422: exact fetch returns more than requested number of rows
Oracle provides a procedure in the DBMS_STANDARD package named RAISE_APPLICATION_ERROR. 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, here is a block that declares an exception named Fever_Out_of_Range. A cursor FOR LOOP 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 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.
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
13 when Fever_Out_of_Range then
14 raise_application_error (-20000,
'Fever is out of the range 65 Deg. F to 115 Deg. F');
15
15 end;
16 /
declare
*
ERROR at line 1:
ORA-20000: Fever is out of the range 65 Deg. F to 115 Deg. F
ORA-06512: at line 14
This chapter addresses the following important facts about handling errors and exceptions: