DISQUS

Jared · 2 years ago

Here's one that has probably bitten a few people.

The count() function does not raise a no_data_found error when no rows are returned, even when the table is empty.

SQL>create table js ( x integer);

Table created.

SQL>
SQL>declare
2 v_x integer;
3 begin
4 select count(*) into v_x from js;
5 dbms_output.put_line(v_x);
6 end;
7 /
0

PL/SQL procedure successfully completed.

Eddie Awad · 2 years ago

Thanks Jared. I have been bitten with this "gotcha" when I first started with PL/SQL. Then, I learned that a SELECT INTO statement that calls a SQL aggregate function, such as COUNT(*) or AVG(), always returns a value or a null. Aggregate functions are guaranteed to return a single value, even if no "data is found".

Ethan · 2 years ago

I often do this sort of thing...

select nvl(max(col), 'DEFAULT_VALUE') into l_foo from table_name where x='BLAH';

In this case when there is no value I set a default and don't get the error. In the event that there is more than one value I only get one (I usually only implement this when that is very unlikely).

<ul>
<li>Ethan</li>
</ul>

Jared · 2 years ago

Using nvl() ensures that your query will retrieve a non-null value.

It does not prevent any error condition though, as there would not be on.

select max(x) from js
where js is an empty table will not raise an error.

Simon · 2 years ago

Actualy there is a way to cause a COUNT() query to raise the NO_DATA_FOUND exception :
if you add a group by clause to the query, e.g.,
SQL> DECLARE
2 l_count number;
3 BEGIN
4 select count()
5 into l_count
6 from all_objects
7 where 1 =0
8 group by 1;
9 DBMS_OUTPUT.put_line ('RESULT = '||l_count);
10 EXCEPTION
11 when NO_DATA_FOUND then
12 DBMS_OUTPUT.put_line ('Query returned no data!');
13 END;
14 /
Query returned no data!

PL/SQL procedure successfully completed

Simon · 2 years ago

Follow Up my own comment :
I'd like to note that in case of adding a group by clause to such a query, you should also deal with a possibility of too_many_rows - if the query actualy succeeds....

Karl · 2 years ago

Raising NO_DATA_FOUND on subscription error. It seems the developers were not busy enough to create an own out_of_bound exception for it

Karl

Michael Moore · 2 years ago

Simon,
what do you mean by " too_many_rows - if the query actualy succeeds…."

I took off the "1 =0" and it ran with no problem.