Exception Handling PL/SQL

by

Exception:

The exception refers to an exceptional event. Exception is an event that disrupts the normal flow of the program, during program execution.

PL/SQL exception handling:

PL/SQL provides a mechanism to handle such exceptions so that the normal flow of the program can be maintained.

Types of exceptions:

1. System-defined exceptions.
2. User-defined exceptions.

Syntax for exception handling:

DECLARE
   //Declaration section 
 BEGIN 
   //Exception section 
 EXCEPTION 
 WHEN ex_name1 THEN 
    //Error handling statements 
 WHEN ex_name2 THEN 
    -Error handling statements 
 WHEN Others THEN 
   //Error handling statements 
END; 
/

Example:

DECLARE
   s_rollNo students.rollNo%type := 10;
   s_name students.name%type;
   s_address students.address%type;
BEGIN
   SELECT rollNo, name, address FROM students WHERE rollNo = s_rollNo;
   dbms_output.put_line(s_rollNo || ' ' || s_name || ' ' || s_address);
   EXCEPTION
   WHEN no_data_found THEN
      dbms_output.put_line('No such student!');
   WHEN others THEN
      dbms_output.put_line('Error!');
END;
/

Output:

No such student!

PL/SQL raise exception:

The database server automatically raised the exceptions in case of any internal database error. But database exceptions can also be raised explicitly by using the RAISE command.

Syntax of raising an exception:

DECLARE
   exception_name EXCEPTION;
BEGIN
   IF condition THEN
      RAISE exception_name;
   END IF;
EXCEPTION
   WHEN exception_name THEN
   statement;
END;
/

PL/SQL user-defined exception:

The PL/SQL provides the facility to define the custom or user-defined exceptions according to the needs of the program. A user-defined exception must be declared and then raised explicitly, using either a RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.

Syntax for declaring a user-defined exception:

DECLARE custom-exception EXCEPTION;

Example:

DECLARE
   s_rollNo students.rollNo%type := &ss_rollNo;
   s_name students.name%type;
   s_address students.address%type;   
    -- user defined exception
   ex_invalid_rollNo  EXCEPTION;
BEGIN
   IF c_id <= 0 THEN
      RAISE ex_invalid_rollNo;
   ELSE
    SELECT rollNo, name, address FROM students WHERE rollNo = s_rollNo;
    dbms_output.put_line(s_rollNo || ' ' || s_name || ' ' || s_address);
   END IF;
   EXCEPTION
     WHEN ex_invalid_rollNo THEN
      dbms_output.put_line('rollNo must be greater than zero!');
     WHEN no_data_found THEN
      dbms_output.put_line('No such student!');
     WHEN others THEN
      dbms_output.put_line('Error!');
END;
/

Output:

(Enter a value less than 0 for rollNo)

rollNo must be greater than zero!

PL/SQL predefined exceptions list:

Exception
Oracle Error
SQLCODE
Description
ACCESS_INTO_NULL
 06530 -6530 It is raised when a null object is automatically assigned a value.
CASE_NOT_FOUND
 06592 -6592 It is raised when none of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause.
COLLECTION_IS_NULL
 06531 -6531 It is raised when a program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
DUP_VAL_ON_INDEX
 00001 -1 It is raised when duplicate values are attempted to be stored in a column with a unique index.
INVALID_CURSOR
 01001 -1001 It is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor.
INVALID_NUMBER
 01722 -1722 It is raised when the conversion of a character string into a number fails because the string does not represent a valid number.
LOGIN_DENIED
 01017 -1017 It is raised when the program attempts to log on to the database with an invalid username or password.
NO_DATA_FOUND
 01403 +100 It is raised when a SELECT INTO statement returns no rows.
NOT_LOGGED_ON
 01012 -1012 It is raised when a database call is issued without being connected to the database.
PROGRAM_ERROR
 06501 -6501 It is raised when PL/SQL has an internal problem.
ROWTYPE_MISMATCH
 06504 -6504 It is raised when a cursor fetches a value in a variable having an incompatible data type.
SELF_IS_NULL
 30625 -30625 It is raised when a member method is invoked, but the instance of the object type is not initialized.
STORAGE_ERROR
 06500 -6500 It is raised when PL/SQL runs out of memory or memory is corrupted.
TOO_MANY_ROWS
 01422 -1422 It is raised when the SELECT INTO statement returns more than one row.
VALUE_ERROR
 06502 -6502 It is raised when an arithmetic, conversion, truncation, or size-constraint error occurs.
ZERO_DIVIDE
 01476 1476 It is raised when an attempt is made to divide a number by zero.