PL/SQL Exceptions

An exception is a runtime error that stops normal execution. PL/SQL does not make you check a return code after every statement: an error raises an exception, control jumps straight to the exception section of the enclosing block, and you decide there whether to recover, log, or re-raise it.

Predefined Exceptions

Oracle raises certain exceptions automatically and gives the most common ones a name, so you can catch them by name instead of by error number.

Exception	Raised when
`NO_DATA_FOUND`	A `SELECT INTO` returns no rows.
`TOO_MANY_ROWS`	A `SELECT INTO` returns more than one row.
`ZERO_DIVIDE`	Division by zero.
`VALUE_ERROR`	A conversion, truncation, or numeric/value error.
`DUP_VAL_ON_INDEX`	An `INSERT`/`UPDATE` would violate a unique constraint.
`INVALID_CURSOR`	An illegal cursor operation, such as fetching a closed cursor.

Example:

declare
  n_result number;
begin
  n_result := 10 / 0;
exception
  when zero_divide then
    dbms_output.put_line('cannot divide by zero');
end;
/

User-Defined Exceptions

Declare your own exception like a variable, then RAISE it wherever a business rule is broken. This turns a domain error into the same control-flow mechanism as a system error, instead of a magic return code the caller has to remember to check.

Example:

declare
  e_negative_price exception;
  n_price number := -5;
begin
  if n_price < 0 then
    raise e_negative_price;
  end if;
exception
  when e_negative_price then
    dbms_output.put_line('price cannot be negative: ' || n_price);
end;
/

PRAGMA EXCEPTION_INIT

Some Oracle errors have a number but no predefined name. PRAGMA EXCEPTION_INIT binds a name of your choice to that error number at compile time, so you can catch it the same way you catch a predefined exception.

Example:

declare
  e_resource_busy exception;
  pragma exception_init(e_resource_busy, -54); -- ORA-00054: resource busy
begin
  -- ... a statement that might raise ORA-00054 ...
  null;
exception
  when e_resource_busy then
    dbms_output.put_line('resource was busy, try again later');
end;
/

WHEN OTHERS and SQLERRM

WHEN OTHERS is the catch-all handler: it matches any exception not already matched above it, so it must always come last. Inside it, SQLCODE and SQLERRM tell you what actually happened.

Advice: Never write an empty WHEN OTHERS THEN NULL; — it silently swallows every error, including ones you never anticipated, and makes production bugs nearly impossible to trace. At minimum, log SQLERRM before deciding whether to continue or re-raise.

Example:

begin
  null; -- risky work goes here
exception
  when others then
    dbms_output.put_line('unexpected error [' || sqlcode || ']: ' || sqlerrm);
    raise; -- re-raise so the caller still sees the failure
end;
/

RAISE_APPLICATION_ERROR

Inside a stored procedure, function, or trigger, use RAISE_APPLICATION_ERROR to reject bad input with your own error number (in the reserved -20000 to -20999 range) and a clear message that reaches the caller as a normal Oracle error.

if :new.price < 0 then
  raise_application_error(-20001, 'price cannot be negative: ' || :new.price);
end if;

See it in a full demo: Demo 08 — Exceptions.