Feuertip #16: 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.

https://www.ibm.com/docs/en/db2-for-zos/11?topic=codes-sql

As to why an unhandled exception displays ORA-4013….sorry, I have no idea and no insights from any friends at Oracle.