Error Handling.

Ant well-written program must have the ability to handle errors intelligently and recover from them if possible. PL/SQL implements error handling with exceptions and exception handlers. Exceptions can be associated with Oracle errors or with your own user-defined errors.

Exceptions

Exceptions and exception handlers are the methods by which the program reacts and deals with run-time errors. When an error occurs, an exception is raised. When this happens, control is passed to the exception handler, which is a separate section of the program. This separates the error handling from the rest of the program, which makes the logic easier to understand. This also ensures that all errors will be trapped.

Predefined exceptions

PL/SQL declares predefined exceptions globally in package STANDARD, which defines the PL/SQL environment. So, you need not declare them yourself. You can write handlers for predefined exceptions using the names in the following list:
Exception Oracle Error Description
ACCESS_INTO_NULL ORA-06530 Attempt to assign values to the attributes of a NULL object
CASE_NOT_FOUND ORA-06592 No matching when clause in a case statement is found
COLLECTION_IS_NULL ORA-06531 Attempt to apply collection methods other than exists to a NULL PL/SQL table or varray
CURSOR_ALREADY_OPEN ORA-06511 Attempt to open a cursor that is already open.
DUP_VAL_ON_INDEX ORA-00001 Unique constraint violated.
INVALID_CURSOR ORA-01001 Illegal cursor operation.
INVALID_NUMBER ORA-01722 Conversion to a number failed.
LOGIN_DENIED ORA-01017 Invalid username/password.
NO_DATA_FOUND ORA-01403 No data found.
NOT_LOGGED_ON ORA-01012 Not connected to Oracle.
PROGRAM_ERROR ORA-06501 Internal PL/SQL error.
ROWTYPE_MISMATCH ORA-06504 Host cursor variable and PL/SQL cursor variable have incompatible row types.
SELF_IS_NULL ORA-30625 Attempt to call a method on a NULL object instance.
STORAGE_ERROR ORA-06500 Internal PL/SQL error raised if PL/SQL runs out of memory.
SUBSCRIPT_BEYOND_COUNT ORA-06533 Reference to a nested table or varray index higher than the number of elements in the collection.
SUBSCRIPT_OUTSIDE_LIMIT ORA-06532 Reference to a nested table or varray index outside the declared range.
SYS_INVALID_ROWID ORA-01410 Conversion to a universal rowid failed.
TIMEOUT_ON_RESOURCE ORA-00051 Time-out occurred while waiting for resource.
TOO_MANY_ROWS ORA-01422 A select ... into statement matches more than one row.
VALUE_ERROR ORA-06502 Truncation, arithmetic, or conversion error.
ZERO_DIVIDE ORA-01476 Division by zero.

User-defined exceptions

A user-defined exception is an error that is defined by the programmer. The error that it signifies is not necessarily an Oracle error - it could be an error with the data, for example. User-defined exceptions are declared in the declarative section of a PL/SQL block. Exceptions have a special type exception: For example: 
declare
  e_TooManyStudents exception;
  ...
When the error associated with the exceptions occurs, the exception is raised. User-defined exceptions are raised explicitly via the raise statement, while predefined exceptions (or user-defined exceptions associated with an Oracle error through the exception_init pragma) are raised implicitly when their associated Oracle error occurs.

For example, the following code may be a part of a procedure for registering student for the IST101 class: 

declare
 e_TooManyStudents exception; -- this is not an Oracle error. Just an error associated with data
 v_MaxNumStudents number;
 v_NumRegistered number;
begin
  select count(*)
    into v_NumRegistered
    from Register
   where CID = 'IST101';
  select max_num
    into v_MaxNumStudents
    from Atlas
   where CID = 'IST101';
  if v_NumRegistered > v_MaxNumStudents then
    raise e_TooManyStudents;
  end if;
  ...
exception
  ...
end;
When an exception is raised, control immediately passes to the exception section of the block. If there is no exception section, the exception is propagated to the enclosing block. Once control passes to the exception handler, there is no way to return to the executable section of the block.

Handling exceptions

The exception section consists of handlers for some or all of the exceptions. An exception handler contains the code that is executed when the error associated with the exception occurs, and exception is raised. The syntax for the exception sections is: 
exception
 when exception_name_1 then
     sequence_of_statements_1;
 when exception_name_2 then
     sequence_of_statements_2;
 ...
 [when others then
     sequence_of_statements;]
end;
Thus, the previous example can have the following exception section: 
exception
  when e_TooManyStudents then
    err_str := 'IST101 class has ' || v_NumRegistered || ' students: max allowed is ' || v_MaxNumStudents;
    insert into error_log values (err_str);
    dbms_output.put_line(err_str);
end;

A single handler can also be executed for more than one exception. Simply list the exception names in the when clause separated by the keyword or: 

exception
  when NO_DATA_FOUND or TOO_MANY_ROWS then
    dbms_output.put_line('NO_DATA_FOUND or TOO_MANY_ROWS');
end;
PL/SQL defines a special exception handler, known as when others. This handler will execute for all raised exceptions that are not handled by any other when clauses defined in the current exception section. It should always be the last handler in the block, so that all previous handlers will be scanned first. when others traps all exceptions, be they user-defined or predefined. It is highly recommended to have when others in the exception section. We would like to rewrite the previous example as follows: 
exception
  when e_TooManyStudents then
    err_str := 'IST101 class has ' || v_NumRegistered || ' students: max allowed is ' || v_MaxNumStudents;
    insert into error_log values (err_str);
    dbms_output.put_line(err_str);
  when others then
    insert into error_log values ('Another error occurred');
end;
Inside the others handler, it is often useful to know which Oracle error raised the exception. PL/SQL provides this information via two built-in functions:

Using raise_application_error

You can use the built-in function raise_application_error to create your own error messages, which can be more descriptive than named exceptions. User-defined error messages are passed out of the block the same way as Oracle errors to the calling environment. The syntax of raise_application_error is 
raise_application_error(error_number, error_message [, keep_errors])
where error_number is a value between -20000 and -20999, error_message is the text associated with this error, and keep_errors is a boolean value. The error_message parameter must be less than 512 characters. If the optional parameter keep_errors is true, the new error is added to the list of errors already raised (if one exists). If it is false, which is the default, the new error will replace the current list of errors.

This example shows how this function can be used to improve error handling in a stored procedure.

Additional information