Why Does Group By Cause ORA-01403: no data found

From: Gene Hubert <ghubert_at_netpath.net>
Date: Thu, 12 Nov 1998 02:39:53 GMT
Message-ID: <5C666F29101FCBC5.374DE020E7A7DAD9.99F3F4FB5267F119@library-proxy.airnews.net>

I was converting a SQL Server procedure to Oracle and hit a problem with ORA-01403: no data found when I selected count(*) into a variable. Why does Oracle handle this differently when there is a group by in the select. The following examples illustrate.

1st case - without a group by

SQL> create or replace procedure junk as   2 i pls_integer;
  3 begin
  4 select count(*) into i from dual where dummy is null;   5 dbms_output.put_line(to_char(i));
  6 end;
  7 /

Procedure created.

SQL> execute junk
0
(this is just what i would expect)

2nd case - with a group by

SQL> create or replace procedure junk as   2 i pls_integer;
  3 begin
  4 select count(*) into i from dual where dummy is null   5 group by dummy;
  6 dbms_output.put_line(to_char(i));
  7 end;
  8 /

Procedure created.

SQL> execute junk
begin junk; end;

*
ERROR at line 1:
ORA-01403: no data found

---

I'm using Oracle 8.0.3 on NT 4.0.

Gene Hubert
Qualex Inc.
Durham, NC Received on Wed Nov 11 1998 - 20:39:53 CST