You probably don't give this a second thought, but sometimes the question rears its ugly and bewildering head in the code you are writing – precisely because Oracle itself provides multiple answers to the question, right from inside its own code base. Let's take a closer look.
When NO_DATA_FOUND is raised, the error message is:
ORA-1403: no data found
So...is that character between "ORA" and "1403" a dash (hyphen) or a negative sign?
Certainly the Oracle documentation identifies errors as negative. Yet the SQL%BULK_EXCEPTIONS pseudo-collection tells a different story. Consider this code:
DECLARE
bulk_errors EXCEPTION;
PRAGMAEXCEPTION_INIT(bulk_errors,-24381);
TYPEnamelist_tISTABLEOFVARCHAR2(1000);
enames_with_errors namelist_t
:=namelist_t('ABC'
,'DEF'
,NULL
,'LITTLE'
,RPAD('BIGBIGGERBIGGEST',250,'ABC')
,'SMITHIE'
);
BEGIN
FORALLindxINenames_with_errors.FIRST..
enames_with_errors.LAST
SAVEEXCEPTIONS
UPDATEemployee
SETlast_name=enames_with_errors(indx);
EXCEPTION
WHENbulk_errors
THEN
DBMS_OUTPUT.put_line('Updated ' || SQL%ROWCOUNT || ' rows.');
FORindxIN1..SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.put_line ('Error code = ' ||
SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
ENDLOOP;
END;
/
I attempt to update the last name of an employee with six different names, two of which are invalid (NULL, since the employee last name cannot be NULL, and the RPAD-ded value, since the maximum length of a last name is just 15 characters).
The output from this program is:
Updated 128 rows.
Error code = 1407
Error code = 12899
Notice that the error code stored by Oracle in the SQL%BULK_EXCEPTIONS structure is not negative.
If I would like to display the error message associated with these error codes, I can use SQLERRM. So you would think I could modify the exception section in the above code to this:
EXCEPTION
WHENbulk_errors
THEN
DBMS_OUTPUT.put_line('Updated ' || SQL%ROWCOUNT || ' rows.');
FORindxIN1..SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.put_line ('Error code = ' ||
SQLERRM (SQL%BULK_EXCEPTIONS(indx).ERROR_CODE));
ENDLOOP;
END;
Oddly enough, when I run this version of the code, I get the following output:
Updated 128 rows.
Error code = -1407: non-ORACLE exception
Error code = -12899: non-ORACLE exception
That is, SQLERRM puts a negative sign in front of the numbers, but then tells you they are not valid Oracle exceptions or error numbers. Fine. Change the call to SQLERRM as follows:
SQLERRM (-1 * SQL%BULK_EXCEPTIONS(indx).ERROR_CODE)
and then I get the desired results when the block is executed:
Updated 128 rows.
Error code = ORA-01407: cannot update () to NULL
Error code = ORA-12899: value too large for column (actual: , maximum: )
It would be awfully nice if Oracle would make up its own mind as to the sign of its error codes. But in the meantime, I will go with the established, generally-accepted wisdom (I certainly don't want to be a rebel): Oracle error codes, except for 1 (user-defined exception) and 100 (alternative for NO_DATA_FOUND), are all negative!