Skip to content

RAISE & RAISE_APPLICATION_ERROR 🧨

Mentor's Note: Oracle handles system errors (like divide by zero). But Oracle doesn't know your business rules. "An order cannot be for 0 items" isn't a database error—it's a Business Error. You use RAISE to tell Oracle: "Stop! This is not allowed." 💡

🌟 The Scenario: The Credit Card Check 💳

Imagine a customer tries to buy a ₹50,000 laptop. - The SQL is perfect. The database is fine. - The Rule: If "Balance < Price", the purchase must fail. - The Action: You manually "Pull the Alarm" using a custom exception. ✅

💻 1. The RAISE Statement

Used to trigger a pre-defined or user-defined exception.

DECLARE
   ex_bad_inventory EXCEPTION;
   v_stock NUMBER := 0;
BEGIN
   IF v_stock < 1 THEN
      RAISE ex_bad_inventory; -- 🧨 Alarm pulled!
   END IF;
EXCEPTION
   WHEN ex_bad_inventory THEN
      DBMS_OUTPUT.PUT_LINE('⚠️ Error: Out of stock!');
END;

💻 2. RAISE_APPLICATION_ERROR 🛠️

This is the professional way to communicate with front-end apps (Java, Python, C#). It returns an error code and a message.

  • Range: You must use codes between -20000 and -20999.
DECLARE
   v_age NUMBER := 10;
BEGIN
   IF v_age < 18 THEN
      -- 🚀 Syntax: (Error_Code, Message)
      RAISE_APPLICATION_ERROR(-20001, 'Student must be 18+ to enroll.');
   END IF;
END;

🛡️ 3. Why use RAISE_APPLICATION_ERROR? (Architect's Note)

  1. Consistency: Your Java app will receive a standard SQLException with your specific code and message.
  2. Termination: Unlike a simple DBMS_OUTPUT, this Stops the code execution immediately.
  3. Rollback: It automatically triggers a ROLLBACK of any uncommitted changes in the current transaction. 🔒

📊 Comparison

Command Best For Result
RAISE Internal PL/SQL logic Jumps to EXCEPTION block
RAISE_APP_ERR Communication with Apps Jumps to App and shows Error 💣

📈 Learning Path