If you doubt that this might be possible or of interest, then answer these two questions:
Can an error in Oracle have more than one error code?
Are error codes positive or negative?
If you answered "yes" for the first and "yes and no" for the second, you probably don't need to read this post.
Oracle errors with more than one error code?Well that wouldn't be very normalized, would it? :-)
But it is true that there at least one error that has two different error codes associated with it, and it's one of the most common "errors" you'll encounter in your code:
The NO_DATA_FOUND exception
When I execute a SELECT-INTO statement, Oracle will raise NO_DATA_FOUND if no row is found for the query. It will raise TOO_MANY_ROWS if more than one row is found.
So what error code is associated with NO_DATA_FOUND?
The following code demonstrates this curiosity. I create a table with no data. My SELECT-INTO therefore finds no rows and the error message displayed (there was no exception handler) shows that the error code is -1403 (or is it 1403? I explore that curiosity later).
Now I will handle the exception, first with WHEN OTHERS then with WHEN NO_DATA_FOUND, and display the value returned by SQLCODE.
CREATE TABLE t (n NUMBER) / DECLARE l_n NUMBER; BEGIN SELECT n INTO l_n FROM t; END; / ORA-01403: no data found
100, not -1403!
DECLARE l_n NUMBER; BEGIN SELECT n INTO l_n FROM t; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ('Error code = ' || SQLCODE); END; / Error code = 100 DECLARE l_n NUMBER; BEGIN SELECT n INTO l_n FROM t; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line ('Error code = ' || SQLCODE); END; / Error code = 100
That's certainly very odd. How can this be? Well, you what they say: go to the source for the best answer, and the source in this case is the code of the STANDARD package, which defines many datatypes and exceptions in the PL/SQL language. In STANDARD, you will find the following:
That pragma is used to associate an error code with a named exception (explore this pragma on LiveSQL here). We can do that in our code as well (which I show you below, thereby introducing another oddity for NO_DATA_FOUND). In this case, the STANDARD package pre-defines a number of named exceptions that are commonly used. And as you can see, the association is made to 100 and not -1403.
NO_DATA_FOUND exception; pragma EXCEPTION_INIT(NO_DATA_FOUND, 100); .... TOO_MANY_ROWS exception; pragma EXCEPTION_INIT(TOO_MANY_ROWS, '-1422');
I must confess I have not tracked down any official documentation on this, but it is pretty clear that 100 is the ANSI-standard error code for "no rows found". In fact, Intersystems elaborates a bit further as follow:
SQLCODE=100 indicates that the SQL operation was successful, but found no data to act upon. This can occur for a number of reasons. For a SELECT these include: the specified table contains no data; the table contains no data that satisfies the query criteria; or row retrieval has reached the final row of the table. For an UPDATE or DELETE these include: the specified table contains no data; or the table contains no row of data that satisfies the WHERE clause criteria. In these cases %ROWCOUNT=0.Notice that it states that 100 indicates the "SQL operation was successful." I like this because it helps explain why the error code is not, say, -100. It also reinforces the point that just because a query does not return any rows does not mean that there is an error. It's just a data condition.
More NO_DATA_FOUND OdditiesRemember I said you could use the EXCEPTION_INIT pragma to associate a code with your own named exception? Here's an example:
I can even use this pragma to assign an error code already assigned a pre-defined exception name, like TOO_MANY_ROWS:
DECLARE e_bad_date_format EXCEPTION; PRAGMA EXCEPTION_INIT (e_bad_date_format, -1830); BEGIN DBMS_OUTPUT.put_line (TO_DATE ('2010 10 10 44:55:66', 'YYYSS')); EXCEPTION WHEN e_bad_date_format THEN DBMS_OUTPUT.put_line ('Bad date format'); END;
And what about -1403? A big, fat no way!
DECLARE my_exception EXCEPTION; PRAGMA EXCEPTION_INIT (my_exception, -1422); BEGIN RAISE my_exception; END; / ORA-01422: exact fetch returns more than requested number of rows
But 100 works just fine.
DECLARE my_exception EXCEPTION; PRAGMA EXCEPTION_INIT (my_exception, -1403); BEGIN RAISE my_exception; END; / PLS-00701: illegal ORACLE error number -1403 for PRAGMA EXCEPTION_INIT DECLARE my_exception EXCEPTION; PRAGMA EXCEPTION_INIT (my_exception, -1403); BEGIN RAISE my_exception; END; / PLS-00701: illegal ORACLE error number -1403 for PRAGMA EXCEPTION_INIT
Notice also that the inability to use EXCEPTION_INIT with -1403 manifests as a compile-time error ("PLS") not a runtime Oracle error ("ORA").
DECLARE my_exception EXCEPTION; PRAGMA EXCEPTION_INIT (my_exception, 100); BEGIN RAISE my_exception; END; / ORA-01403: no data found
Error Codes Negative or Positive?I bet that most of you believe that error codes are (mostly) negative. That view would certainly be reinforced with code like this:
Sure looks negative to me. And if I try to use that pragma with a positive number that is not 100 or 1 (the error code associated with a user-defined exception that has not been essociated with an error code by EXCEPTION_INIT), I get an error:
DECLARE my_exception EXCEPTION; PRAGMA EXCEPTION_INIT (my_exception, -1422); BEGIN RAISE my_exception; EXCEPTION WHEN OTHERS THEN IF SQLCODE = -1422 THEN DBMS_OUTPUT.PUT_LINE ('Negative!'); END IF; END; / Negative!
So it's OK, so error codes are generally negative. Everyone agreed on that? Well, maybe everyone but a few people who work(ed) at Oracle and maybe everything but a few features in Oracle Database.
DECLARE my_exception EXCEPTION; PRAGMA EXCEPTION_INIT (my_exception, 1422); BEGIN RAISE my_exception; END; / PLS-00701: illegal ORACLE error number 1422 for PRAGMA EXCEPTION_INIT
It turns out that sometimes error codes are stored without that pesky "-". Which is understandable, because there are clearly two ways to interpret the hyphen in this text:
1. A negative sign
2. A hyphen
Let's first consider the SQLERRM function. Most people use it to obtain the error message of the current error (though you would be better off using DBMS_UTILITY.FORMAT_ERROR_STACK or the UTL_CALL_STACK API).
Relatively few developers know that you can also pass an error code to SQLERRM and it will return the generic message associated with that code. Here's an example:
That's nice. But what if I leave off the "-"?
BEGIN DBMS_OUTPUT.put_line (SQLERRM (-1422)); END; ORA-01422: exact fetch returns more than requested number of rows
Gee, that's telling it like it is.
BEGIN DBMS_OUTPUT.put_line (SQLERRM (1422)); END; -1422: non-ORACLE exception
But why didn't SQLERRM talk to SQL%BULK_EXCEPTIONS and get their story straight?
SQL%BULK_EXCEPTIONS is a pseudo-collection or records that is populated with any errors in the execution of FORALL statements. Check out my LiveSQL tutorial on bulk processing for lots more details.
Each record contains the index into the collection for each statement that failed along with the error code of the failure. Guess what? The error code is recorded without what that person clearly thought was a hyphen.
Notice in the code below (an excerpt from my LiveSQL script on SAVE EXCEPTIONS) I must multiply the error code value by -1 so that I can retrieve the error message.
No big deal, once you aware of it. But....kind of odd, eh?
EXCEPTION WHEN std_errs.failure_in_forall THEN FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP DBMS_OUTPUT.put_line ( 'Oracle error is ' || SQLERRM ( -1 * SQL%BULK_EXCEPTIONS (indx).ERROR_CODE)); END LOOP; ROLLBACK; END;
Well, there is some consistency in our inconsistency. If you use the LOG ERRORS feature for non-query DML (which allows you to suppress errors at the row level), then Oracle will automatically record the error code, message and more in an error logging table. And in this table, error codes are stored as unsigned integers, as you can see below in the output from this LiveSQL script on LOG ERRORS:
So those are my little discoveries on the nuances of error codes in Oracle Database.
Do you have your own story about Oracle error codes you'd like to share?