Re: EXception no_DATA_FOUND

Sach,
count (*) will always return a single row!

Here is a cut-down version of your query running in sqlplus.

SQL> set feedback 1
SQL> r
  1* select count(*) from all_tables where owner='SOTT'

  COUNT(*)

         0

1 row selected.

even a nonsense query like select count (*) from dual where 1=2 will return a row!

This is true of any of the group-by functions (count/sum/avg...).

Your query will return NO DATA FOUND if you change "count(*)" to a constant, such as 1,2,3 etc.

Perhaps your 'if' statement should test if tablenum = 0 instead?

Regards,

Paul

sachh wrote:
>
> I am not getting the exception
> no_data_found while using the following procedure
> eventhough the user sott dowsnt exists;
> where can be the problem??
>
> declare
> tablenum integer;
> status integer;
> begin
> select count(*) into tablenum from all_tables where owner='SOTT';
> if sql%rowcount=0 then dbms_output.put_line('no tables');
> end if;
> exception
> when no_data_found then
> status:=sqlcode;
> dbms_output.put_line('following '|| sqlerrm(status));
> end;
> /
Received on Fri Nov 26 1999 - 00:07:31 CET