Home » SQL & PL/SQL » SQL & PL/SQL » WHEN NO DATA FOUND EXCEPTION - COUNT - MAX Functions (Windows XP - Oracle 9i)
WHEN NO DATA FOUND EXCEPTION - COUNT - MAX Functions [message #319731] Mon, 12 May 2008 19:56 Go to next message
alexaoracle
Messages: 36
Registered: October 2007
Member
Hi everyone,

When you use COUNT or MAX functions inside of a sql query, and you have no data on the table, you get the No data found exception.
My question is, if you have more than one sql query and each one uses this functions, in the EXCEPTION block how could you know which sql query, triggered the exception?
Is there a way to control this?



Re: WHEN NO DATA FOUND EXCEPTION - COUNT - MAX Functions [message #319733 is a reply to message #319731] Mon, 12 May 2008 20:30 Go to previous messageGo to next message
alexaoracle
Messages: 36
Registered: October 2007
Member
ok,

begin
select max(col1) from table1 where col2='time' and col3='seconds';
select count(col2) from table2 where colA = '0009393' and colB = 'cs23213'

exception
when no data found then

--how do I say here: if is for table1 do this
--..
--for table2 do this other
--..
end
Re: WHEN NO DATA FOUND EXCEPTION - COUNT - MAX Functions [message #319734 is a reply to message #319731] Mon, 12 May 2008 20:37 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
begin
begin
select max(col1) from table1 where col2='time' and col3='seconds';
exception
when no data found then
--how do I say here: if is for table1 do this
end;
begin
select count(col2) from table2 where colA = '0009393' and colB = 'cs23213'
exception
when no data found then
--..
--for table2 do this other
--..
end;
end;

[Updated on: Mon, 12 May 2008 20:38] by Moderator

Report message to a moderator

Re: WHEN NO DATA FOUND EXCEPTION - COUNT - MAX Functions [message #319769 is a reply to message #319734] Tue, 13 May 2008 00:22 Go to previous messageGo to next message
alexaoracle
Messages: 36
Registered: October 2007
Member
Shocked hi, this is so weird I mean, I cant explain it.
You know, I have too many columns on some tables, triggers and a dblink, so for this time in order to make my question clear I made a simple example on this forum to explain what I am needing.
the funny thing is that I finished it, I executed on isqlplus and I don´t get the no data found message

create table green(
id number, 
intensity number);

insert into green values(1,100);
insert into green values(2,182);
insert into green values(3,140);
insert into green values(4,110);

create table blue(
freq number,
radix number);

insert into blue values(1, 900);
insert into blue values(2, 905);
insert into blue values(3, 950);
insert into blue values(4, 920);

declare
n number;
m number;
begin
select max(intensity) into n from green where id>5;
select count(radix) into m from blue where freq >5;
exception
when no_data_found then
dbms_output.put_line('Message NFD-: '||sqlerrm);
end;
/


so well I have nothing else to say, only that
mmmmm what? and check my code again Confused

[Updated on: Tue, 13 May 2008 00:24]

Report message to a moderator

Re: WHEN NO DATA FOUND EXCEPTION - COUNT - MAX Functions [message #319771 is a reply to message #319769] Tue, 13 May 2008 00:33 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
It is because of this
SQL> set feedback 1
SQL> select max(sno) from test_tab where sno > 5;

  MAX(SNO)
----------


1 row selected.

SQL> select sno from test_tab where sno > 5;

no rows selected

SQL> select count(sno) from test_tab where sno > 5;

COUNT(SNO)
----------
         0

1 row selected.

Regards

Raj
Re: WHEN NO DATA FOUND EXCEPTION - COUNT - MAX Functions [message #319773 is a reply to message #319731] Tue, 13 May 2008 00:37 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
When you use COUNT or MAX functions inside of a sql query, and you have no data on the table, you get the No data found exception.

This is incorrect
SQL> ed
Wrote file afiedt.buf

  1  declare
  2  x number;
  3  begin
  4  select max(employee_id) into x
  5  from hr.employees
  6  where employee_id = 10;
  7  dbms_output.put_line('val: '||x);
  8* end;
SQL> /
val:

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2  x number;
  3  begin
  4  select count(employee_id) into x
  5  from hr.employees
  6  where employee_id = 10;
  7  dbms_output.put_line('val: '||x);
  8* end;
SQL> /
val: 0

Count will NOT raise no_data_found it will return 0 (zero)
max will not raise no_data_found it will return a null.
Re: WHEN NO DATA FOUND EXCEPTION - COUNT - MAX Functions [message #319783 is a reply to message #319773] Tue, 13 May 2008 00:53 Go to previous messageGo to next message
alexaoracle
Messages: 36
Registered: October 2007
Member
Yes, right didnt see that,
count will always return 0 if there is no data
and max will return a null value
Thanks!

Again I am going back to see my code because there is something else not working well there..
Re: WHEN NO DATA FOUND EXCEPTION - COUNT - MAX Functions [message #319809 is a reply to message #319783] Tue, 13 May 2008 01:55 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
select max(col1) from table1 where col2='time' and col3='seconds';
select count(col2) from table2 where colA = '0009393' and colB = 'cs23213'

This is invalid syntax in PL/SQL. Look up the syntax for SELECT INTO
My apologies, just noticed that you were using it in your code (in a later post).

[Updated on: Tue, 13 May 2008 01:57]

Report message to a moderator

Re: WHEN NO DATA FOUND EXCEPTION - COUNT - MAX Functions [message #320065 is a reply to message #319809] Tue, 13 May 2008 23:59 Go to previous message
alexaoracle
Messages: 36
Registered: October 2007
Member
no problema
Previous Topic: Constraints
Next Topic: UNION
Goto Forum:
  


Current Time: Wed Dec 07 06:50:01 CST 2016

Total time taken to generate the page: 0.16185 seconds