What are SQLCODE and SQLERRM?

SQLCODE and SQLERRM are Oracle’s built-in error reporting functions in PL/SQL.

When an error occurs in PL/SQL at runtime:

SQLCODE returns the number of the last encountered error.

SQLERRM returns the  message associated with its error-number argument. The error-number argument is optional: if omitted, SQLERRM returns the message associated with the current value of SQLCODE.

In this article, we’ll take a look at PL/SQL examples that demonstrate the use of SQLCODE and SQLERRM.

Scenario: SQLCODE and SQLERRM with Internally Defined Exception

Oracle’s internally defined exceptions are standard errors as described in Oracle Database Error Messages Reference. An internally defined exception gets raised implicitly; it always has an error code (of the form ORA-<number>) but does not have an error name unless one is assigned to it in PL/SQL.

When an internally defined exception occurs, SQLCODE returns the numeric value of the associated ORA error. e.g. when the error is ORA-00904, the corresponding SQLCODE is -904 and its SQLERRM is the text of the message associated with ORA-00904.

A typical use case for SQLCODE and SQLERRM is to place them in an OTHERS exception handler.

SQL> -- Scenario: Internally Defined Error
SQL> declare
  2     name emp.ename%type;
  3
  4  begin
  5    -- Incorrect column name
  6    execute immediate
  7    'select namewrong into name
  8    from emp
  9    where rownum = 1';
 10
 11   exception
 12      when others then
 13        dbms_output.put_line('SQLCODE: '|| SQLCODE);
 14        dbms_output.put_line('SQLERRM: '|| SQLERRM);
 15  end;
 16  /
SQLCODE: -904
SQLERRM: ORA-00904: "NAMEWRONG": invalid identifier

PL/SQL procedure successfully completed.

Alternatively, error names can be assigned to internally defined exceptions, and SQLCODE and SQLERRM can be used in exception handlers designed specifically for the error names.

SQL> -- Scenario: Internally Defined Error
SQL> -- with error name explicitly defined
SQL> declare
  2     name emp.ename%type;
  3
  4     -- Assign error name to internally defined error
  5     invalid_column  exception;
  6
  7     -- Associate error name with error code
  8     -- of the internally defined exception
  9     pragma exception_init(invalid_column, -904);
 10  begin
 11    -- Incorrect column name
 12    execute immediate
 13    'select namewrong into name
 14    from emp
 15    where rownum = 1';
 16
 17   exception
 18      -- Named exception handler for ORA-00904
 19      when invalid_column then
 20        dbms_output.put_line('SQLCODE: '|| SQLCODE);
 21        dbms_output.put_line('SQLERRM: '|| SQLERRM);
 22  end;
 23  /
SQLCODE: -904
SQLERRM: ORA-00904: "NAMEWRONG": invalid identifier

PL/SQL procedure successfully completed.

Scenario: SQLCODE and SQLERRM with Predefined Exception

Oracle’s predefined exceptions — such as CASE_NOT_FOUND or ZERO_DIVIDE — are internally defined exceptions with predefined error names.

With predefined exceptions, as with internally defined ones, SQLCODE returns the numeric value of the ORA error associated with the exception. e.g. the ORA error associated with ZERO_DIVIDE is ORA-01476; the corresponding SQLCODE is —1476 and its SQLERRM is the text of the message associated with ORA-01476.

SQLCODE and SQLERRM may be placed either  in a named exception handler for the predefined exception name, or in an OTHERS exception handler.

Here’s an example of SQLCODE and SQLERRM in a named exception handler:

SQL> -- Scenario: Predefined Error - ZERO_DIVIDE
SQL> declare
  2     saltransformed emp.sal%type;
  3
  4  begin
  5
  6     -- SQL that returns no data
  7     select sal/0 into saltransformed from emp
  8     where rownum = 1;
  9
 10   exception
 11      when zero_divide then
 12        dbms_output.put_line('SQLCODE: '|| SQLCODE);
 13        dbms_output.put_line('SQLERRM: '|| SQLERRM);
 14  end;
 15  /
SQLCODE: -1476
SQLERRM: ORA-01476: divisor is equal to zero

PL/SQL procedure successfully completed.

Note that the SQLCODE number is negative for all internally defined/predefined exceptions, except for NO_DATA_FOUND whose numeric code is +100.

SQL> -- Scenario: Predefined Error - NO_DATA_FOUND
SQL> declare
  2     name emp.ename%type;
  3
  4  begin
  5
  6     -- SQL that returns no data
  7     select ename into name from emp
  8     where empno = 100;
  9
 10   exception
 11      when no_data_found then
 12        dbms_output.put_line('SQLCODE: ' || SQLCODE);
 13        dbms_output.put_line('SQLERRM: ' || SQLERRM);
 14  end;
 15  /
SQLCODE: 100
SQLERRM: ORA-01403: no data found

PL/SQL procedure successfully completed.

Scenario: SQLCODE and SQLERRM with User-Defined Exception

Oracle’s user-defined exceptions are explicitly defined by the user in PL/SQL, to address the application’s error handling needs beyond internally defined exceptions. For example, while inserting into scott’s emp table, a user-defined exception INVALID_SALARY may be created to validate that emp.sal is more than 0.

A user-defined exception must be raised explicitly in code; it always has an error name, but does not have an  error code unless assigned.

For a user-defined exception, the default value of SQLCODE is +1 and of SQLERRM is "User-Defined Exception".

SQL> -- Scenario: User-Defined Error
SQL> -- with error code not defined
SQL> declare
  2     sal emp.sal%type;
  3
  4     -- Assign error name to user-defined error
  5     invalid_salary  exception;
  6
  7  begin
  8    -- SQL to select invalid value
  9    select 0 into sal
 10    from emp
 11    where rownum = 1;
 12
 13    -- Raise user-defined error
 14    if sal <= 0 then
 15      raise invalid_salary;
 16    end if;
 17
 18   exception
 19      -- Named exception handler for user-defined error
 20      when invalid_salary then
 21        dbms_output.put_line('SQLCODE: '|| SQLCODE);
 22        dbms_output.put_line('SQLERRM: '|| SQLERRM);
 23  end;
 24  /
SQLCODE: 1
SQLERRM: User-Defined Exception

PL/SQL procedure successfully completed.

The default values of SQLCODE and SQLERRM in case of user-defined exceptions are typically overriden to give them more specific/meaningful values.

• SQLCODE is overriden with EXCEPTION_INIT pragma. The error number should be a negative integer in the range -20000 to —20999.
• SQLERRM is overriden with RAISE_APPLICATION_ERROR. The error message should be a string up to 2048 bytes long.

SQL> -- Scenario: User-Defined Error
SQL> -- with error code/message explicitly defined
SQL> declare
  2     sal emp.sal%type;
  3
  4     -- Assign error name to user-defined error
  5     invalid_salary  exception;
  6
  7     -- Assign error code to user-defined error
  8     -- and associate it with the error name
  9     pragma exception_init(invalid_salary, -20000);
 10
 11  begin
 12    -- SQL to select invalid value
 13    select 0 into sal
 14    from emp
 15    where rownum = 1;
 16
 17    -- Raise user-defined error with custom message
 18    if sal <= 0 then
 19      raise_application_error(-20000
 20                  , 'Salary must be more than zero');
 21    end if;
 22
 23   exception
 24      -- Named exception handler for user-defined error
 25      when invalid_salary then
 26        dbms_output.put_line('SQLCODE: '|| SQLCODE);
 27        dbms_output.put_line('SQLERRM: '|| SQLERRM);
 28  end;
 29  /
SQLCODE: -20000
SQLERRM: ORA-20000: Salary must be more than zero

PL/SQL procedure successfully completed.

How do SQLCODE and SQLERRM behave outside an exception handler?

SQLCODE and SQLERRM are mainly useful in an exception handler.

Outside an exception handler:

• SQLCODE returns 0
• SQLERRM (without argument) returns the message normal, successful completion.

SQL> -- Scenario: No error
SQL> declare
  2     name emp.ename%type;
  3
  4  begin
  5
  6     -- Valid SQL
  7     select ename into name from emp
  8     where rownum = 1;
  9
 10     dbms_output.put_line('Name: '|| name);
 11
 12     -- SQLCODE and SQLERRM values when no error
 13     dbms_output.put_line('SQLCODE: '|| SQLCODE);
 14     dbms_output.put_line('SQLERRM: '|| SQLERRM);
 15  end;
 16  /
Name: KING
SQLCODE: 0
SQLERRM: ORA-0000: normal, successful completion

PL/SQL procedure successfully completed.

SQLERRM with error-number argument

The default value of SQLERRM – i.e. the message associated with the current value of SQLCODE – can be overridden by giving it an error number as argument. Let’s try that with the previous no-error example with a small tweak: change the last dbms_output.put_line to print SQLERRM with argument.

SQL> -- Scenario: No error, SQLERRM with argument
SQL> declare
  2     name emp.ename%type;
  3
  4  begin
  5
  6     -- Valid SQL
  7     select ename into name from emp
  8     where rownum = 1;
  9
 10     dbms_output.put_line('Name: '|| name);
 11
 12     -- SQLCODE and SQLERRM values when no error
 13     dbms_output.put_line('SQLCODE: '|| SQLCODE);
 14     dbms_output.put_line('SQLERRM(-42): '|| SQLERRM(-42));
 15  end;
 16  /
Name: KING
SQLCODE: 0
SQLERRM(-42): ORA-00042: Unknown Service name

PL/SQL procedure successfully completed.

…which shows that SQLERRM prints the message associated with its argument, instead of the message associated with SQLCODE.

What is the maximum length of SQLERRM?

We may need to know the maximum length of SQLERRM for various reasons e.g. while designing an EXCEPTIONS table to log application errors,  or while creating a PL/SQL string variable to hold its value.

Oracle documentation has this to say about SQLERRM:

This function returns a maximum of 512 bytes, which is the maximum length of an Oracle Database error message (including the error code, nested messages, and message inserts such as table and column names).

Summary

This article describes Oracle functions SQLCODE and SQLERRM and shows examples of their use with:

• internally defined exceptions
• predefined exceptions
• user-defined exceptions
• no exceptions