How to Use SQLCODE in PL/SQL Exception Handlers

When an exception handler catches an error, it usually needs to know which error it was: to log it, to decide whether to retry, or to report it. SQLCODE returns the error number and SQLERRM the error message, and together with Oracle's predefined exception names they make handlers precise.

Code for This Guide

The main examples are in the examples/exceptions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

exception
  when exception_name [or exception_name ...] then
    ... sqlcode ... sqlerrm ...
  [when others then ...]

sqlerrm(error_number)      -- the message of any error number

SQLCODE is negative for Oracle errors, such as -1476 for ORA-01476, 1 for a user-defined exception, and 0 outside a handler.

Common Predefined Exceptions

Catch a Predefined Exception

Example:

declare
  v_ratio number;
begin
  v_ratio := 1 / 0;
exception
  when zero_divide then
    dbms_output.put_line('Division by zero: ' || sqlcode || ' ' || sqlerrm);
end;
/

Output:

Division by zero: -1476 ORA-01476: divisor is equal to zero

PL/SQL procedure successfully completed.

The handler names ZERO_DIVIDE and reports SQLCODE -1476 and the full message.

Outside a Handler, and Any Error Number

Example:

declare
  v_city airports.city%type;
begin
  dbms_output.put_line('outside a handler: SQLCODE ' || sqlcode || ', ' || sqlerrm);
  dbms_output.put_line('message of -1422: ' || sqlerrm(-1422));
  select city into v_city from airports where country_code = 'IN';
exception
  when others then
    dbms_output.put_line('caught ' || sqlcode || ': ' || sqlerrm);
end;
/

Output:

outside a handler: SQLCODE 0, ORA-0000: normal, successful completion
message of -1422: ORA-01422: exact fetch returned more than the requested number of rows
caught -1422: ORA-01422: exact fetch returned more than the requested number of rows

PL/SQL procedure successfully completed.

Outside a handler, SQLCODE is 0 and SQLERRM reports normal, successful completion. SQLERRM with an argument returns the message of any error number. The SELECT INTO finds three Indian airports and raises TOO_MANY_ROWS, which WHEN OTHERS catches.

User-Defined Exceptions

Example:

declare
  e_overbooked exception;
  v_seats_left number := 0;
begin
  if v_seats_left <= 0 then
    raise e_overbooked;
  end if;
exception
  when e_overbooked then
    dbms_output.put_line('Flight is full (' || sqlcode || ': ' || sqlerrm || ')');
end;
/

Output:

Flight is full (1: User-Defined Exception)

PL/SQL procedure successfully completed.

For a user-defined exception, SQLCODE is 1 and SQLERRM says User-Defined Exception.

Things to Know

• SQLCODE and SQLERRM cannot be used directly in SQL statements; assign them to variables first, for example to insert them into a log table.
• SQLERRM may truncate long error stacks; DBMS_UTILITY.FORMAT_ERROR_STACK returns the full stack.
• A WHEN OTHERS handler should log and re-raise, not silently swallow errors.

Related Guides

• Error Handling with Dbms_utility.format_error_backtrace

Conclusion

In an exception handler, SQLCODE returns the error number and SQLERRM the message, and predefined names such as NO_DATA_FOUND and ZERO_DIVIDE let handlers target specific errors. Copy the values into variables to log them, and re-raise errors you cannot handle.