Experiment No 23: Implement PL/SQL program based on Exception Handling (User-defined exceptions)

 Experiment No 23: Implement PL/SQL program based on Exception Handling (User-defined exceptions)

Result:
The program successfully handled all cases using user-defined exceptions—showing appropriate error messages for division by zero and when y > x, and displaying the correct result for valid inputs.

(Note: If space for writing the result is not provided in your manual, you may write the result on notebook pages and paste them into the manual.)

---

1. How to define the exception?

In Oracle PL/SQL, a user-defined exception is declared in the DECLARE section of a block using the EXCEPTION keyword. You can then raise it explicitly using the RAISE statement or RAISE_APPLICATION_ERROR.

Example:

DECLARE
   exp1 EXCEPTION; -- Declaring user-defined exception
   x NUMBER := &x;
   y NUMBER := &y;
BEGIN
   IF y = 0 THEN
      RAISE exp1; -- Raising the exception
   END IF;
EXCEPTION
   WHEN exp1 THEN
      DBMS_OUTPUT.PUT_LINE('Error: Division by zero is not allowed.');
END;

Explanation:

• DECLARE – Section where the exception is declared.

• RAISE exp1; – Explicitly raises the exception when the condition is met.

• EXCEPTION block – Handles the exception and displays a message.

---

2. Create the Procedure with the help of a user-defined exception

Here’s a complete PL/SQL procedure using a user-defined exception for division:

CREATE OR REPLACE PROCEDURE divide_numbers(x IN NUMBER, y IN NUMBER) IS
   exp1 EXCEPTION; -- Exception for division by zero
   exp2 EXCEPTION; -- Exception when y > x
   result NUMBER;
BEGIN
   -- Check for division by zero
   IF y = 0 THEN
      RAISE exp1;
   ELSIF y > x THEN
      RAISE exp2;
   ELSE
      result := x / y;
      DBMS_OUTPUT.PUT_LINE('The result is: ' || result);
   END IF;

EXCEPTION
   WHEN exp1 THEN
      DBMS_OUTPUT.PUT_LINE('Error: Division by zero not allowed.');
   WHEN exp2 THEN
      DBMS_OUTPUT.PUT_LINE('Error: y is greater than x, please check input.');
END;
/

How to execute:

BEGIN
   divide_numbers(10, 2);  -- Valid division
   divide_numbers(10, 0);  -- Division by zero
   divide_numbers(5, 10);  -- y greater than x
END;
/

Output examples:

• The result is: 5

• Error: Division by zero not allowed.

• Error: y is greater than x, please check input.

---

Exercise

1. PL/SQL Program Using a User-Defined Exception

This is a general program demonstrating a user-defined exception for dividing numbers (similar to your experiment):

DECLARE
    x NUMBER := &x;   -- User input for numerator
    y NUMBER := &y;   -- User input for denominator
    result NUMBER;
    div_by_zero EXCEPTION; -- User-defined exception
BEGIN
    -- Check for division by zero
    IF y = 0 THEN
        RAISE div_by_zero;
    ELSE
        result := x / y;
        DBMS_OUTPUT.PUT_LINE('The result is: ' || result);
    END IF;

EXCEPTION
    WHEN div_by_zero THEN
        DBMS_OUTPUT.PUT_LINE('Error: Division by zero is not allowed.');
END;
/

Sample Run:

• Input x = 10, y = 2 → Output: The result is: 5

• Input x = 10, y = 0 → Output: Error: Division by zero is not allowed.

---

2. PL/SQL Program to Check Customer ID

This program raises a user-defined exception invalid_id when the user enters a customer ID that does not exist in the table.

Assume we have a table customers:

CREATE TABLE customers (
    customer_id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    city VARCHAR2(50)
);

Sample data:

INSERT INTO customers VALUES (101, 'Ramesh', 'Mumbai');
INSERT INTO customers VALUES (102, 'Sita', 'Delhi');
INSERT INTO customers VALUES (103, 'Amit', 'Pune');
COMMIT;

PL/SQL Program:

DECLARE
    cust_id NUMBER := &cust_id;  -- User input
    cust_name VARCHAR2(50);
    invalid_id EXCEPTION;        -- User-defined exception
BEGIN
    -- Check if customer exists
    SELECT name INTO cust_name
    FROM customers
    WHERE customer_id = cust_id;

    DBMS_OUTPUT.PUT_LINE('Customer Name: ' || cust_name);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE invalid_id; -- Raise user-defined exception if ID not found
    WHEN invalid_id THEN
        DBMS_OUTPUT.PUT_LINE('Error: Invalid Customer ID entered. Please check.');
END;
/

Sample Run:

• Input cust_id = 102 → Output: Customer Name: Sita

• Input cust_id = 105 → Output: Error: Invalid Customer ID entered. Please check.

---