Explain SQLCODE and SQLERRM in PL/SQL exception handlers

How it works

Inside an exception handler — most often a generic WHEN OTHERS — SQLCODE returns the numeric error code of the exception currently being handled (0 if none, 1 for a user-defined exception raised without a code), and SQLERRM returns the associated error message text. Together they're what you log or re-raise with when a generic OTHERS handler needs to report specifically what went wrong instead of silently swallowing it.

BEGIN
  UPDATE employees SET salary = salary / 0 WHERE employee_id = 100;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error code: ' || SQLCODE);
    DBMS_OUTPUT.PUT_LINE('Error message: ' || SQLERRM);
    RAISE; -- re-raise after logging, don't swallow it
END;
/

Related Questions