NO_DATA_FOUND when using aggregate functions like count, min, max, avg…

A common misconception when writing PL/SQL select …. into … statements is that if it is an aggregation query (a query that is using an aggregation like count, max, min, avg, etc.) then they always return a result – possibly null – so a NO_DATA_FOUND exception will never occur.

During a post on the Oracle Community Forums, a regular and respected contributor made a statement to this effect, before the classic error was pointed out to him.

Let’s take a look at when, how and why NO_DATA_FOUND exceptions occur and show that they can indeed occur when using aggregated functions.

create table T_TEST as                           -- Create 100 rows
 select trunc(dbms_random.value(0, 10)) as VAL1, -- integers between 0-9
        dbms_random.value(0, 100) as VAL2        -- decimals between 0-100
 from dual
 connect by level <= 100;

commit;

declare
  vValue integer;
begin
  select VAL2
  into vValue
  from T_TEST
  where VAL1 = 10; -- There are no values for 10!
end;

Error at line 9
ORA-01403: no data found
ORA-06512: at line 4

Now lets look and aggregated situation, a simple count.

declare
  vCount integer;
begin
  select count(*)
  into vCount
  from T_TEST
  where VAL1 = 10; -- There are no values greater than 10!
  DBMS_OUTPUT.PUT_LINE ( 'OK! vCount = ' || vCount );
end;

OK! vCount = 0

As expected, the anonymous block outputs a count of 0 with no exception, as there are no rows where VAL1 = 10, but that can be counted, so results in a row of just the count being returned.

Now what about if we add a GROUP BY and select a value as well as the count?

declare
  vValue1 integer;
  vCount integer;
begin
  select VAL1, count(*)
  into vValue1, vCount
  from T_TEST
  where VAL1 = 10
  group by VAL1; 
  DBMS_OUTPUT.PUT_LINE ('OK! vValue1 = ' || vValue1 ||', vCount = ' || vCount);
end;

Error at line 1
ORA-01403: no data found
ORA-06512: at line 5

NO_DATA_FOUND exception occurred!  But how?  Aren’t aggregated functions always supposed to return a value?  Well, no. You are doing a GROUP BY on an empty set, for all combinations of the grouping column(s), in this case VAL1.  Well there is no data to fetch into VAL1, so there is no count on it.  In other words no rows to return = NO_DATA_FOUND.

The same happens if you are filtering out all data using a HAVING clause.

declare
  vValue1 integer;
  vCount integer;
begin
  select count(*)
  into vCount
  from T_TEST
  group by VAL1
  having max(VAL1) > 9; 
  DBMS_OUTPUT.PUT_LINE('vCount = ' || vCount);
end;

Error at line 1
ORA-01403: no data found
ORA-06512: at line 5

So remember, aggregated queries in PL/SQL CAN raise a NO_DATA_FOUND exception if your aggregated function is using a GROUP BY or a HAVING clause.

Here’s a demo of all of the scenarios that anyone can run without creating tables…

declare
  vDummy number;
  vCount integer;
begin
  begin
    select count(*)
    into vCount
    from (
      select 1 as dummy, 2 as dummy2
      from dual
      where 1 = 0
    );
    DBMS_OUTPUT.PUT_LINE ('OK - count(*)' );
  exception
    when NO_DATA_FOUND then
      DBMS_OUTPUT.PUT_LINE ('No Data Found - count(*)' );
  end;
----
  begin
    select dummy, count(*)
    into vDummy, vCount
    from (
      select 1 as dummy, 2 as dummy2
      from dual
      where 1 = 0
    )
    group by dummy;
    DBMS_OUTPUT.PUT_LINE ('OK - count(*) with Group By' );
  exception
    when NO_DATA_FOUND then
      DBMS_OUTPUT.PUT_LINE ('No Data Found - count(*) with Group By ' );
  end;
----
  begin
    select count(*)
    into vCount
    from (
      select 1 as dummy, 2 as dummy2
      from dual
    )
    having max(dummy) > 2;
    DBMS_OUTPUT.PUT_LINE ('OK - count(*) with Having' );
  exception
    when NO_DATA_FOUND then
      DBMS_OUTPUT.PUT_LINE ('No Data Found - count(*) with Having' );
  end;
----
  begin
    select dummy, count(*)
    into vDummy, vCount
    from (
      select 1 as dummy, 2 as dummy2
      from dual
    )
    group by dummy
    having max(dummy2) > 2;
    DBMS_OUTPUT.PUT_LINE ('OK - count(*), Group By With Having' );
  exception
    when NO_DATA_FOUND then
      DBMS_OUTPUT.PUT_LINE ('No Data Found - count(*), Group By With Having' );
  end;          
end;

OK - count(*)
No Data Found - count(*) with Group By
No Data Found - count(*) with Having
No Data Found - count(*), Group By With Having

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s