Exception handling plsql

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!

Exception:

Pl sql exception handling:

Types of exceptions:

Syntax for exception handling:

Example:

Output:

Pl sql raise exception:

Syntax of raising an exception:

Pl sql user defined exception:

Syntax for declaring a user defined exception:

Example:

Output:

Pl sql predefined exceptions list: