Exception Handling in PL/SQL

An exception is an error which disrupts the normal flow of program instructions. PL/SQL provides us the exception block which raises the exception thus helping the programmer to find out the fault and resolve it.

Syntax:

DECLARE	
-- Declaration statements;
BEGIN
	-- SQL statements;	
-- Procedural statements;
EXCEPTION	
-- Exception handling statements;
END;

There are two types of exceptions defined in PL/SQL

1. User defined exception.
2. System defined exceptions.

Types of Exception Handling

There are two types of exceptions defined in PL/SQL :

1. System-Defined Exceptions

These are predefined exceptions that occur when Oracle rules or constraints are violated. They include NO_DATA_FOUND, ZERO_DIVIDE, TOO_MANY_ROWS, etc.

Let's understand this with the help of example:

DECLARE
a NUMBER := 10; 
b NUMBER := 0;  
c NUMBER;
BEGIN 
c := a / b; 
-- Division by zero  DBMS_OUTPUT.PUT_LINE('Result: ' || c);
EXCEPTION  
WHEN ZERO_DIVIDE THEN    
DBMS_OUTPUT.PUT_LINE('Error: Division by zero is not allowed.');
END;

Output:

Error: Division by zero is not allowed.

In this example:

• Variable Declaration: a = 10, b = 0.
• Program tries to divide a / b, which causes an error.
• The EXCEPTION block catches the ZERO_DIVIDE error.
• It prints: "Error: Division by zero is not allowed.

Kinds of System -Define Exception

System-defined exceptions are further divided into two categories:

1. Named System Exception:

These exceptions have predefined names such as ACCESS_INTO_NULL, DUP_VAL_ON_INDEX, LOGIN_DENIED, etc.
Syntax:

DECLARE 
   -- Variable to simulate NULL access
    v_null_var VARCHAR2(10);   
 -- Variable to simulate duplicate insertion
    v_id NUMBER := 1;
BEGIN 

   -- 1. ACCESS_INTO_NULL example
 
   BEGIN        
-- Trying to access NULL variable       
 IF v_null_var = 'test' THEN    
        NULL;    
    END IF;
    EXCEPTION     
   WHEN ACCESS_INTO_NULL THEN  
          DBMS_OUTPUT.PUT_LINE('Caught ACCESS_INTO_NULL exception');
    END;

-- 2. DUP_VAL_ON_INDEX example

    BEGIN
        -- Attempt to insert duplicate value into unique index
        INSERT INTO employees (employee_id, name)
        VALUES (v_id, 'John Doe');
        
        -- Insert again with same employee_id to cause DUP_VAL_ON_INDEX
        INSERT INTO employees (employee_id, name)
        VALUES (v_id, 'Jane Doe');

        COMMIT;
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            DBMS_OUTPUT.PUT_LINE('Caught DUP_VAL_ON_INDEX exception: Duplicate value');
    END;

    -- 3. LOGIN_DENIED example cannot be simulated easily inside PL/SQL, usually raised on login failure.
END;

Let's understand this with the help of example:

So we will discuss some of the most commonly used exceptions. Lets create a table geeks.

--1.create a table name as geeks
CREATE TABLE geeks ( 
   g_id INT PRIMARY KEY,    -- Adding PRIMARY KEY will help trigger DUP_VAL_ON_INDEX 
   g_name VARCHAR(20),
    marks INT
);
--2.insert the value in geeks  table
INSERT INTO geeks VALUES (1, 'Suraj', 100);
INSERT INTO geeks VALUES (2, 'Praveen', 97);
INSERT INTO geeks VALUES (3, 'Jessie', 99);

Output:

In this example:

• A table named geeks is created with three columns: g_id (integer, primary key), g_name (text, up to 20 characters), and marks (integer).
• The primary key on g_id ensures each value in this column is unique.
• Three rows are inserted into the table with values for g_id, g_name, and marks, representing three students.

2. Unnamed System Exception:

Unnamed system exceptions are predefined by Oracle, but they don’t have a specific name like NO_DATA_FOUND. They occur less frequently and are identified by Oracle error codes .

Syntax:

DECLARE
  exception_name EXCEPTION; 
 PRAGMA EXCEPTION_INIT(exception_name, -error_code);
BEGIN  
-- Code that may raise the unnamed exception 
EXCEPTION  
WHEN exception_name THEN    
-- Exception handling code
END;

Let's understand this with the help of example:

DECLARE  
fk_violation EXCEPTION; 
 PRAGMA EXCEPTION_INIT(fk_violation, -2292);
BEGIN  
DELETE FROM departments WHERE department_id = 10;
EXCEPTION 
 WHEN fk_violation THEN  
  DBMS_OUTPUT.PUT_LINE('Error: Cannot delete department. Employees exist in it.');
END;

Output:

Error: Cannot delete department. Employees exist in it.

In this example

• Declares an exception fk_violation for foreign key violations.
• Associates it with error code -2292 (foreign key constraint violation).
• Attempts to delete the department with department_id = 10.
• If a foreign key violation occurs, it catches the error.
• Displays the message: "Error: Cannot delete department. Employees exist in it."

2. User Define Exception

User-defined exceptions are custom exceptions created by the programmer to handle specific business logic errors that are not covered by Oracle's predefined exceptions. These exceptions must be declared explicitly and are raised using the RAISE keyword.

Syntax:

DECLARE 
  exception_name EXCEPTION;  -- Declaration of user-defined exception
BEGIN   
-- Logic  
 IF condition THEN     
 RAISE exception_name;  -- Raising the exception explicitly  
 END IF;
EXCEPTION 
  WHEN exception_name THEN      -- Handling code 
     DBMS_OUTPUT.PUT_LINE('Exception handled');
END;

Let's understand this with the help of example:

So, we will discuss the devide check problem. Divide a non-negative integer x by y, such that:

• y ≠ 0 (no division by zero)
• Result must be ≥ 1 ⇒ y ≤ x

DECLARE  
 x INT := &x;  -- Runtime input 
  y INT := &y; 
  div_r FLOAT;  
 exp1 EXCEPTION;  -- For division by zero   exp2
 EXCEPTION; 
 -- For y > x
BEGIN   IF y = 0 THEN 
     RAISE exp1; 
  ELSIF y > x THEN 
     RAISE exp2;   
ELSE     
 div_r := x / y;    
  DBMS_OUTPUT.PUT_LINE('The result is ' || div_r);   
END IF;
EXCEPTION   
WHEN exp1 THEN      
DBMS_OUTPUT.PUT_LINE('Error: Division by zero is not allowed'); 
  WHEN exp2 THEN   
   DBMS_OUTPUT.PUT_LINE('Error: y is greater than x, please check the input');
END;

Output:

ZERO_DIVIDE: Cannot divide by zero

In this example:

• Takes runtime input for x and y.
• Checks if y = 0 (raises division by zero error) or y > x (raises input error).
• If no error, divides x by y and shows the result.
• Handles errors and displays relevant messages.

Syntax:

RAISE_APPLICATION_ERROR(error_number, 'error_message');

Let's understand this with the help of example:

DECLARE  
 myex EXCEPTION;
   n NUMBER := 10;
BEGIN 
  FOR i IN 1..n LOOP
      DBMS_OUTPUT.PUT_LINE(i*i);    
  IF i*i = 36 THEN      
   RAISE myex;     
 END IF;   
END LOOP;
EXCEPTION  
 WHEN myex THEN   
   RAISE_APPLICATION_ERROR(-20015, 'Welcome to GeeksForGeeks');
END;

Output:

1
4
9
16
25
36
Error Report:ORA-20015: Welcome to GeeksForGeeksORA-06512: at line 13