Exception handling plsql

by

Exception:

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 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:

Database server automatically raised the exceptions in case of any internal database error. But database exceptions can also be raised explicitly by using 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 need of 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 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 s 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 value in a variable having incompatible data type.
SELF_IS_NULL 30625 -30625 It is raised when a member method is invoked, but the instance of the object type was not initialized.
STORAGE_ERROR 06500 -6500 It is raised when PL/SQL ran out of memory or memory was corrupted.
TOO_MANY_ROWS 01422 -1422 It is raised when s 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.