First of all, sqlcode is a PL/SQL function (and documented here), not a SQL function. So what is it good for? Returning the error code of the exception currently being handled.
There’s a good chance you never call this function. Instead, you call a generic error logging procedure, such as logger.log_error, and it calls sqlcode on your behalf.
See the Feuertips episode on sqlcode “secrets”!
But if you are curious about all things PL/SQL, as I am, you might be interested to learn more about some of the “secrets” (aka, nuances and trivial factoids) behind this function.
So here goes!
First of all, and it’s kind of a surprise given the name, but you cannot invoke sqlcode (nor its “sister” function, sqlerrm) from inside a SQL statement. No, no, no.
As to why this is the case, perhaps Oracle gives us a clue in the documentation?
“If a function invokes SQLCODE, and you use the RESTRICT_REFERENCES pragma to assert the purity of the function, then you cannot specify the constraints WNPS and RNPS.”
Also, the specification in the standard package shows that sqlcode returns a pls_integer, a PL/SQL type.
function SQLCODE return PLS_INTEGER; pragma BUILTIN('SQLCODE',45, 10, 0);
Of course, the workaround is straightforward enough. Assign sqlcode to a variable, then insert it into your log table.
declare l_code integer := sqlcode; begin insert into log_error_table value (l_code); END;
What if there is no error?
Whenever you call sqlcode outside of an exception handler, it returns 0 (no error). Note that “outside of” refers not to compile time but to runtime. In other words, the following call to sqlcode occurs inside the handler at runtime, even though it is defined and compiled outside any exception section.
create or replace procedure show_sqlcode is begin dbms_output.put_line(sqlcode); end; / begin raise no_data_found; exception when others then show_sqlcode; end; /
OK, but what about those error codes?
Yes, sqlcode is simple enough. It returns the database error code. Ah, and then things get interesting.
For example, the documentation says:
“For an internally defined exception, the numeric code is the number of the associated Oracle Database error. This number is negative except for the error “no data found,” whose numeric code is +100.”
Oh really? The number is negative? I certainly always thought they were negative (I’ll come back to that odd 100 later). As a result, this little tidbit related to sql%bulk_exceptions makes me sad:
For some strange reason, the database error code is not always stored as a negative number. When the sql%bulk_exceptions pseudo-collection is populated (by including the save exceptions clause), the error code is stored without a sign.
The same thing is true with log errors. If, for example, you raise a dup_val_on_index error, then “1” and not “-1” is reported. It is then up to you to make it a negative number. This is necessary if you want to call sqlerrm to retrieve the generic error message. Or simply avoid confusion in your error logging table.
An error with two error codes?
And then there is “no data found.” Oddly enough, in the world of Oracle Database, two different error codes are associated with this error.
As noted in the documentation, sqlcode returns 100 (a positive number!) when a select-into is executed and no rows are found:
declare l_dummy dual.dummy%type; begin select dummy into l_dummy from dual where 1 = 2; exception when others then dbms_output.put_line('sqlcode = ' || sqlcode); end; / sqlcode = 100
This is true even for non-SQL related “no data found” scenarios, as with collections:
declare l_list dbms_sql.varchar2_table; begin dbms_output.put_line('l_list(1) = ' || l_list(1)); exception when others then dbms_output.put_line('sqlcode = ' || sqlcode); end; / sqlcode = 100
Yet, oddly enough, when I do not handle the exception, I do not see 100, I see -1403!
declare l_list dbms_sql.varchar2_table; begin dbms_output.put_line('l_list(1) = ' || l_list(1)); end; / ORA-01403: no data found ORA-06512: at line 4
And if I call both sqlcode and sqlerrm inside the exception handler?
declare l_list dbms_sql.varchar2_table; begin dbms_output.put_line('l_list(1) = ' || l_list(1)); exception when others then dbms_output.put_line('sqlcode = ' || sqlcode); dbms_output.put_line('sqlerrm = ' || sqlerrm); end; / sqlcode = 100 sqlerrm = ORA-01403: no data found
Well, OK then.
So far as I can tell, 100 is the ANSI-standard error code for this situation, set long, long ago. You can see it in the IBM Db2 documentation as well.
As to why an unhandled exception displays ORA-4013….sorry, I have no idea and no insights from any friends at Oracle.