Skip to main content

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?

Comments

Popular posts from this blog

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.

How to Get a Mutating Table Error

I need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

Table Functions, Part 1: Introduction and Exploration

Please do feel encouraged to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done!


Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs.

So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into.

Of course, I am not the first to do so. I encourage to check out the documentation, as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall. Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latter part of this seri…

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post.

PL/SQL is a strongly-typed language. Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type).

PL/SQL offers a wide array of pre-defined data types, both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package).

Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types.

You can't really declare your own "user-defined" scalars, though you can define subtypes from those scalars, which can be very helpful from the perspective…