What are PL/SQL exceptions? Tell me any three

In PL/SQL (Procedural Language/Structured Query Language), exceptions are events that occur during the execution of a block of code that disrupts the normal flow of the program. PL/SQL provides a way to handle these exceptions through the use of exception handling mechanisms. Here are three types of PL/SQL exceptions:

1. NO_DATA_FOUND:
   • This exception is raised when a SELECT INTO statement returns no rows.
   • For example:
     DECLARE
v_employee_name employees.employee_name%TYPE;
BEGIN
SELECT employee_name INTO v_employee_name FROM employees WHERE employee_id = 1000;
-- If no rows are found for the given condition, NO_DATA_FOUND is raised.
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found.');
END;

2. TOO_MANY_ROWS:
   • This exception is raised when a SELECT INTO statement returns more than one row.
   • For example:
     DECLARE
v_employee_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_employee_salary FROM employees WHERE department_id = 10;
-- If more than one row is returned for the given condition, TOO_MANY_ROWS is raised.
DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || v_employee_salary);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('More than one employee found for the given condition.');
END;

3. OTHERS:
   • The OTHERS exception is a catch-all for any unhandled exceptions. It is generally used at the end of an exception block to capture any unexpected errors.
   • For example:
     DECLARE
v_result NUMBER;
BEGIN
-- Some code that may raise an exception
v_result := 1 / 0;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Division by zero error.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');
END;


These examples illustrate the handling of specific exceptions like NO_DATA_FOUND and TOO_MANY_ROWS, as well as the generic OTHERS exception to catch any unforeseen errors.