PL/SQL Exception Handling 🪂¶

Mentor's Note: In programming, things will go wrong. A user might enter a wrong ID, or a table might be empty. Exception Handling is your Parachute. It ensures that when your code "falls," it lands safely instead of crashing your entire application. 💡

🌟 The Scenario: The Fire Drill 🧯¶

Imagine an office building. - Normal Operation: Everyone works at their desks (The BEGIN block). - The Exception: Someone smells smoke (An error occurs). - The Trap: Instead of panicking, everyone follows the "Fire Drill" plan (The EXCEPTION block). - The Result: The building stays safe, and everyone knows exactly what to do. ✅

💻 1. The Core Structure¶

BEGIN
   -- ⚙️ Regular code here
   NULL;
EXCEPTION
   -- 🧯 Rescue plan here
   WHEN some_error THEN
      handle_it;
END;

💻 2. Types of Exceptions¶

A. Predefined (Built-in)¶

Oracle has named the most common errors for you. - NO_DATA_FOUND: SELECT INTO returned zero rows. - TOO_MANY_ROWS: SELECT INTO returned more than one row. - ZERO_DIVIDE: Trying to divide by zero.

DECLARE
   v_result NUMBER;
BEGIN
   v_result := 10 / 0;
EXCEPTION
   WHEN ZERO_DIVIDE THEN
      DBMS_OUTPUT.PUT_LINE('⚠️ Error: You cannot divide by zero!');
END;

B. User-Defined (Custom Rules)¶

You can create your own error names for business rules (e.g., "Insufficient Balance").

DECLARE
   ex_too_young EXCEPTION; -- 1. Declare
   v_age NUMBER := 15;
BEGIN
   IF v_age < 18 THEN
      RAISE ex_too_young; -- 2. Raise
   END IF;
EXCEPTION
   WHEN ex_too_young THEN -- 3. Handle
      DBMS_OUTPUT.PUT_LINE('⚠️ Error: Must be 18+ to register.');
END;

🏗️ Architect's Note: The "OTHERS" Trap 🛡️¶

Oracle provides a WHEN OTHERS handler that catches every possible error. - The Architect's Secret: While tempting, don't use it to "hide" errors. Always use SQLERRM to log the actual error message so you can fix it later! - Tip: Only use WHEN OTHERS at the very end of your handler as a final safety net.