Sqlerrm() vs Sqlcode()

Question

as Sqlerrm() returns message and code which is provided by orecle.

exception 1: like for in implicit cursor when data not found error ouccers n we are writing exception handling like dbms_ou..(sqlerrm());

output: ORA-01403: no data found and for Dbms_output..(sqlcode());--when same exception no data found

output: 100

exception 2: when too_many_rows exception occuers then for it returns

output : when sqlerrm(): ORA-01422:exact fetch returns more than requested number of rows..

output : when sqlcode(): -1422

so,in first situation sqlcode() returns 100 and in too_many_rows it returns -1422 (which is code that provided by oracle).??

so this is my question what is the correct ans. what sqlcode() function returns a number of code which is provided by oracle or any anonymous number??

No data (sqlcode = 100) is not considered to be an error. Only errors have negative sqlcode values. — jarlh, Feb 04 '22 at 08:33

score 2 · Answer 1 · answered Feb 04 '22 at 06:15

There's nothing unusual in what you're saying, as it is described in Documentation.

For internal exceptions, SQLCODE returns the number of the associated Oracle error. The number that SQLCODE returns is negative unless the Oracle error is no data found, in which case SQLCODE returns +100.

score 0 · Answer 2 · answered Feb 04 '22 at 06:49

I don't know why Oracle once decided to replace -01403 by +100 in the SQL code. This is the only exception that exists, though. We use the following function for all our exception handling:

FUNCTION get_ora_code(vi_sqlcode INTEGER) RETURN INTEGER IS
BEGIN
 if vi_sqlcode = 100 then
   return -1403;
 else
   return vi_sqlcode;
 end if;
END get_ora_code;

Then our exception handlers may look like this:

EXCEPTION WHEN OTHERS THEN
  vo_msg := regexp_replace(sqlerrm || ' ' || dbms_utility.format_error_backtrace, '[[:space:]]+', ' ');
  v_ora_code := get_ora_code(sqlcode);
  RETURN v_ora_code;
END my_package_function;

Thus our functions always return a negative value in case of an error (and report the complete error message and stack in the out variable vo_msg). We use the return code 0 for success and positive values for success with a warning (which we also comment in the vo_msg). This is a nice code convention, I think.

Sqlerrm() vs Sqlcode()

2 Answers2