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.

---

📈 Learning Path

• ← Back: REF CURSOR
• Next: Exception Propagation →