Explain RAISE_APPLICATION_ERROR in PL/SQL

How it works

RAISE_APPLICATION_ERROR(error_number, message) raises a runtime error with a custom message, using an error number chosen from the reserved range -20000 to -20999. It's the standard way to surface a business-rule failure back to the caller — a Java or REST client, SQL*Plus, another PL/SQL block — as a clean, specific error instead of letting an unhandled generic exception propagate.

CREATE OR REPLACE PROCEDURE withdraw(p_account_id NUMBER, p_amount NUMBER) IS
  v_balance NUMBER;
BEGIN
  SELECT balance INTO v_balance FROM accounts WHERE account_id = p_account_id;
  IF p_amount > v_balance THEN
    RAISE_APPLICATION_ERROR(-20001, 'Withdrawal amount exceeds available balance.');
  END IF;
  UPDATE accounts SET balance = balance - p_amount WHERE account_id = p_account_id;
END;
/

Related Questions