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

SQL>

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 11.1.0.6.0 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

SQL>

2 comments:

Laurent Schneider said...

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


Andrew Stuart Reid said...

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