Some Curiosities of Oracle Error Codes

A blog post about Oracle error codes? Curiosities about them, even?

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).

CREATE TABLE t (n NUMBER)
/

DECLARE
   l_n   NUMBER;
BEGIN
   SELECT n INTO l_n FROM t;
END;
/

ORA-01403: no data found 

Now I will handle the exception, first with WHEN OTHERS then with WHEN NO_DATA_FOUND, and display the value returned by SQLCODE.

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

100, not -1403!

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:

  NO_DATA_FOUND exception;
    pragma EXCEPTION_INIT(NO_DATA_FOUND, 100);

    ....

  TOO_MANY_ROWS exception;
    pragma EXCEPTION_INIT(TOO_MANY_ROWS, '-1422');

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.

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 Oddities

Remember I said you could use the EXCEPTION_INIT pragma to associate a code with your own named exception? Here's an example:

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; 

I can even use this pragma to assign an error code already assigned a pre-defined exception name, like TOO_MANY_ROWS:

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 

And what about -1403? A big, fat no way!

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 

But 100 works just fine.

DECLARE  
   my_exception   EXCEPTION;  
   PRAGMA EXCEPTION_INIT (my_exception, 100);  
BEGIN  
   RAISE my_exception;  
END;
/

ORA-01403: no data found 

Notice also that the inability to use EXCEPTION_INIT with -1403 manifests as a compile-time error ("PLS") not a runtime Oracle error ("ORA").

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:

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!

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;  
END; 
/

PLS-00701: illegal ORACLE error number 1422 for PRAGMA EXCEPTION_INIT

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.

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:

ORA-01422

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:

BEGIN 
   DBMS_OUTPUT.put_line (SQLERRM (-1422)); 
END; 

ORA-01422: exact fetch returns more than requested number of rows

That's nice. But what if I leave off the "-"?

BEGIN 
   DBMS_OUTPUT.put_line (SQLERRM (1422)); 
END; 

-1422: non-ORACLE exception 

Gee, that's telling it like it is.

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.

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; 

No big deal, once you aware of it. But....kind of odd, eh?

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?