Skip to content

Exception Propagation 🫧

Mentor's Note: Imagine you are in a tall office building. If a fire starts on the 1st floor and there is no firefighter there, the smoke travels up to the 2nd floor. In PL/SQL, if an inner block doesn't handle an error, it "bubbles up" to the outer block. This is called Propagation. 💡

🌟 The Scenario: The Message Relay 📞

  • Inner Block: You find an error. You don't know what to do.
  • The Bubble: You pass the error up to your manager (The Outer Block).
  • The Outer Block: If the manager knows what to do, the program continues. If not, the program crashes! ✅

💻 1. The Propagation Flow

If an error occurs in a nested block: 1. Oracle looks for a handler in the Current Block. 2. If not found, it stops the inner block and looks in the Outer Block. 3. It keeps going until a handler is found or it hits the App level.

DECLARE
   -- 📦 Outer Block
BEGIN
   DECLARE
      -- 📦 Inner Block
   BEGIN
      RAISE_APPLICATION_ERROR(-20001, 'Inner Error');
      -- ❌ Inner block has no handler!
   END;
EXCEPTION
   WHEN OTHERS THEN
      -- ✅ Outer block catches the "Bubbled Up" error
      DBMS_OUTPUT.PUT_LINE('Caught in Outer: ' || SQLERRM);
END;

🎨 Visual Logic: Bubbling Up

graph TD
    A[Inner Block Error! 💥] --> B{Handled Locally?}
    B -- No --> C[Stop Inner Block 🛑]
    C --> D[Outer Block Exception Handler 🪂]
    D -- "Not Handled?" --> E[Crash App 🧨]
    D -- "Handled!" --> F[Continue Program ✅]

🏗️ Architect's Note: Re-Raising 🛡️

Sometimes you want to log an error in the inner block but still let the outer block know it happened. You use the RAISE; command with no name.

EXCEPTION
   WHEN OTHERS THEN
      Log_Error_To_File(SQLERRM);
      RAISE; -- 🚀 Send the error to the next level

📈 Learning Path