Experiment No 22: Implement PL/SQL program based on Exception Handling (Pre-defined exceptions) application

Experiment No 22: Implement PL/SQL program based on Exception Handling (Pre-defined exceptions) applicationHere’s a shorter version of the Result:

Result

The PL/SQL program using pre-defined exceptions was successfully implemented. Errors like NO_DATA_FOUND, ZERO_DIVIDE, and VALUE_ERROR were handled properly, making the code more reliable and preventing crashes.

---

Practical Related Questions

Q1. Distinguish between user-defined exception and pre-defined exception

Answer:

• Pre-defined Exception:

  • Already defined by Oracle.

  • Automatically raised when a common error occurs (e.g., NO_DATA_FOUND, ZERO_DIVIDE).

  • No need for explicit declaration by the programmer.

• User-defined Exception:

  • Created explicitly by the programmer using EXCEPTION keyword.

  • Must be raised manually using the RAISE statement.

  • Useful when Oracle does not provide a built-in exception for a specific condition.

---

Q2. Explain pre-defined exception with an example

Answer:
A pre-defined exception is an error condition already defined by Oracle PL/SQL. For example:

Example Program (Oracle)

DECLARE
   v_marks NUMBER;
BEGIN
   -- Attempting to divide by zero
   v_marks := 90/0;

   DBMS_OUTPUT.PUT_LINE('Marks: ' || v_marks);

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.

Here, ZERO_DIVIDE is a pre-defined exception automatically raised by Oracle when division by zero occurs.

---

Exercise 1: Division with ZERO_DIVIDE Exception

DECLARE
   num1 NUMBER;
   num2 NUMBER;
   result NUMBER;
BEGIN
   -- Input numbers (for demonstration, using assignment)
   num1 := 50;  -- You can change or take input using substitution variables
   num2 := 0;   -- Change this to test division or zero scenario

   result := num1 / num2;  -- Attempt division

   DBMS_OUTPUT.PUT_LINE('Result of division: ' || result);

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

Sample Output:

Error: Division by zero is not allowed.

Explanation:

• If num2 is 0, the predefined exception ZERO_DIVIDE is raised and handled gracefully.

• Otherwise, the result of the division is displayed.

---

Exercise 2: Retrieve Employee Salary with NO_DATA_FOUND Exception

DECLARE
   v_emp_id employees.emp_id%TYPE := 101;  -- Change as needed
   v_salary employees.salary%TYPE;
BEGIN
   -- Attempt to retrieve salary
   SELECT salary 
   INTO v_salary 
   FROM employees
   WHERE emp_id = v_emp_id;

   DBMS_OUTPUT.PUT_LINE('Salary of employee ' || v_emp_id || ' is: ' || v_salary);

EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Employee ID not found.');
END;
/

Sample Output (if ID does not exist):

Employee ID not found.

Explanation:

• NO_DATA_FOUND is a predefined exception that occurs when a SELECT INTO query returns no rows.

• This program retrieves salary if the employee exists; otherwise, it shows an appropriate message.

---