Monday, September 17, 2012

ERROR: ORA-01426: single-row subquery returns no rows

I went on a SQL*Plus course in 1990 but did not start using Oracle until 1997. I recently read through the notes from this course and found a reference to the error message in the title of this post. I do not recall ever seeing this error so I tried to reproduce it on Oracle I ran it in a database which does not have a user called NODDY so I thought this might work but it didn't:

SQL> select username from dba_users
  2  where trunc(created) =
  3  (select trunc(created)
  4   from dba_users
  5   where username = 'NODDY');

no rows selected


I found out why when I read the following explanation, which is taken from the book advertised above. As usual, click on the picture to enlarge it and bring it into focus if necessary:

So the moral of this story is: Don't read old course notes, buy yourself an up to date book instead.

After I wrote this post, Laurent commented that ORA-01426 is now used for a different exception, as you can see in the following SQL, which was tested on Oracle on Windows XP:

SQL> l
  1* select power(70,70) from dual
SQL> /
select power(70,70) from dual
ERROR at line 1:
ORA-01426: numeric overflow



  1. In the 11.2 docs it says :
    Scalar Subquery Expressions

    If the subquery returns 0 rows, then the value of the scalar subquery expression is NULL

    But in 11.2, 1426 is not related to subquery

    SQL> select 1e100*1e100 from dual;
    select 1e100*1e100 from dual
    ERROR at line 1:
    ORA-01426: numeric overflow

  2. So, assuming my old notes were correct when they were printed, it looks as if Oracle has redefined the error code!