Skip to content

Unhandled Exceptions (WHEN OTHERS) 🕸️

Mentor's Note: You can plan for a "Wrong ID" or "Zero Balance". But you can't plan for a server crash or a cable being cut. WHEN OTHERS is the "Global Catch-All" for everything you didn't expect. 💡

🌟 The Scenario: The Safety Net 🕸️

Imagine an acrobat performing in a circus. - He has a plan for his jumps (Specific exceptions). - But if he slips in a way he never practiced, he needs a Safety Net below him to catch him no matter how he falls.

💻 1. The OTHERS Clause

This must always be the last handler in your exception block.

DECLARE
   v_data NUMBER;
BEGIN
   -- Some complex logic
   NULL;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('We know how to fix this!');
   WHEN OTHERS THEN
      -- 🕸️ The Safety Net catches everything else
      DBMS_OUTPUT.PUT_LINE('Something unexpected happened!');
END;

💻 2. SQLCODE & SQLERRM 🛠️

Inside the WHEN OTHERS block, you need to know what actually happened. Oracle provides two magic variables: - SQLCODE: The numeric error number (e.g., -1476). - SQLERRM: The text error message (e.g., "ORA-01476: divisor is equal to zero").

EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error Code: ' || SQLCODE);
      DBMS_OUTPUT.PUT_LINE('Error Message: ' || SQLERRM);
END;

🛡️ 3. The Architect's Warning: Don't Swallow Errors! 🛡️

Many beginners use WHEN OTHERS THEN NULL;. - The Architect's Secret: This is extremely dangerous! It "Swallows" the error, making it look like the program worked when it actually failed. - Rule: Always Log the error to a table or Re-raise it after logging. Never let an error disappear in silence! 🤫🚫

📈 Learning Path