exception handling

Exception handling in PL/SQL enables you to keep a procedure running when a minor error occurs, which would normally stop the entire procedure. Examples of this are when you try to retrieve data with an SQL query, but there are no records matching the criteria. In that case, you might want to insert a record, or assign a default value to a variable.

Exceptions are defined within a subprogram. The normal format of a PL/SQL program is this:

DECLARE
  declare variables

BEGIN
  main program

EXCEPTION
  WHEN exception name THEN
    exception routine

END;

You can use the predefined exceptions, or create your own. If you create your own exceptions, you must declare them in the DECLARE part of the program. When you want that exception to occur, you must raise it yourself, with the RAISE command.
An example:

DECLARE
  my_own_fault EXCEPTION;
  my_number NUMBER;

BEGIN
  SELECT amount
  INTO my_number
  FROM loan;

  IF amount = 0 THEN 
    RAISE my_own_fault;
  END IF;

EXCEPTION
  WHEN my_own_fault THEN
    DBMS_OUTPUT.PUT_LINE('Error!');
  WHEN no_data_found THEN
    my_number := 0;

END;

There are a number of predefined exceptions which you can use:

CURSOR_ALREADY_OPEN    (error ORA-06511).
This error occurs when a cursor is opened that is already open. It can easily be prevented by checking the state of the cursor before opening it.

DUP_VAL_ON_INDEX    (error ORA-00001).
This error occurs when you try to insert a record, but the unique key already exists in the table.

INVALID_CURSOR    (error ORA-01001).
Occurs when you try to do something with a cursor that isn't possible.

INVALID_NUMBER    (error ORA-01722).
This error occurs when you try to convert data to a number when it isn't a number.

LOGIN_DENIED    (error ORA-01017).
When you try to log in with the wrong username or password, this error occurs.

NO_DATA_FOUND    (error ORA-01403).
This error occurs when you try to retrieve data from a table, but no records match the criteria.

NOT_LOGGED_IN    (error ORA-01012).
This error occurs when you try to access the database when you're not logged in.

PROGRAM_ERROR    (error ORA-06501).
This error occurs when an internal error occurs.

STORAGE_ERROR    (error ORA-06500).
Occurs when there isn't any memory left to execute the program.

TIMEOUT_ON_RESOURCE    (error ORA-00051).
This error pops up when a time-out occurs with the Oracle database.

TOO_MANY_ROWS    (error ORA-01422).
This error occurs when you retrieve more then one row from the table, when you want to insert only one row into a variable.

TRANSACTION_BACKED_OUT    (error ORA-00061).
Occurs when a rollback occurs as result of a deadlock.

VALUE_ERROR    (error ORA-06502).
This error occurs when a conversion, mathematical or constraint error occurs.

ZERO_DIVIDE    (error ORA-01476).
This error occurs when the program tries to divide a number by zero.