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
