min(); never no_data_found
cate
select min(datex) from tablex where x = 1;
With min(), I never get a no_data_found exception.
cate
The check is useless because the IF condition is tested only when
%NOTFOUND is false. When PL/SQL raises NO_DATA_FOUND, normal execution
stops and control transfers to the exception-handling part of the
block.
However, a SELECT INTO statement that calls a SQL aggregate function
never raises NO_DATA_FOUND because aggregate functions always return a
value or a null. In such cases, %NOTFOUND yields FALSE, as the
following example shows:
BEGIN
...
SELECT MAX(sal) INTO my_sal FROM emp WHERE deptno = my_deptno;
-- never raises NO_DATA_FOUND
IF SQL%NOTFOUND THEN -- always tested but never true
... -- this action is never taken
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN ... -- never invoked
Maxim Demenko
It is unclear for me, what a business goal are you behind? If you know,
that aggregate functions always return value or null, why don't you
write your code in such a manner, that this circumstance is accounted?
But if you need by any means raise a no_data_found by aggregate
functions, maybe that helps...
SQL> declare
2 my_sal number;
3 my_deptno number := -1;
4 begin
5 select max(sal) into my_sal from emp where deptno = my_deptno
group by 1;
6 exception
7 when no_data_found then
8 if sql%notfound then
9 dbms_output.put_line('tests should be done thoroughfull');
10 end if;
11 dbms_output.put_line('never say never');
12 end;
13 /
tests should be done thoroughfull
never say never
PL/SQL procedure successfully completed.
Best regards
Maxim
Mark D Powell
>
> - Show quoted text -
Cate, why not test for a NULL value being returned and if so the RAISE
NO_DATA_FOUND?
> @t20
> set echo on
> set serveroutput on
1> declare
2 v_variable varchar2(10);
3 begin
4 select 'X' into v_variable from dual;
5 if v_variable is null
6 then RAISE NO_DATA_FOUND;
7 end if;
8 dbms_output.put_line('Found Data');
9 exception
10 when NO_DATA_FOUND then dbms_output.put_line('NO DATA');
11 end;
12 /
Found Data
PL/SQL procedure successfully completed.
1 > declare
2 v_variable varchar2(10);
3 begin
4 select NULL into v_variable from dual;
5 if v_variable is null
6 then RAISE NO_DATA_FOUND;
7 end if;
8 dbms_output.put_line('Found Data');
9 exception
10 when NO_DATA_FOUND then dbms_output.put_line('NO DATA');
11 end;
12 /
NO DATA
PL/SQL procedure successfully completed.
HTH -- Mark D Powell --
Jeremy
@a32g2000yqm.googlegroups.com>, Mark.P...@hp.com says...
>
> Cate, why not test for a NULL value being returned and if so the RAISE
> NO_DATA_FOUND?
>
What's the opinion of this ng's contributors on the advisability of
raising "standard" error conditions when the underlying reasons for
raising that error are different?
To me it seems like a practice not to be recommended - potential for
misleading people unfamiliar with the code later on in maintenance mode.
--
jeremy
joel garry
> In article <79bb2ee5-f28a-43b9-af1e-4597012495f0
>
>
>
> > Cate, why not test for a NULL value being returned and if so the RAISE
> > NO_DATA_FOUND?
>
> What's the opinion of this ng's contributors on the advisability of
> raising "standard" error conditions when the underlying reasons for
> raising that error are different?
>
> To me it seems like a practice not to be recommended - potential for
> misleading people unfamiliar with the code later on in maintenance mode.
>
> --
> jeremy
http://lmgtfy.com/?q=then+null+site%3Atkyte.blogspot.com
jg
--
@home.com is bogus.
http://www.guardian.co.uk/technology/2009/dec/22/russian-hackers-citigroup-cyber-security
Jeremy
@d4g2000pra.googlegroups.com>, joel-...@home.com says...>
> > In article <79bb2ee5-f28a-43b9-af1e-4597012495f0
> > @a32g2000yqm.googlegroups.com>, Mark.Powe...@hp.com says...
> >
> >
> >
> > > Cate, why not test for a NULL value being returned and if so the RAISE
> > > NO_DATA_FOUND?
> >
> > What's the opinion of this ng's contributors on the advisability of
> > raising "standard" error conditions when the underlying reasons for
> > raising that error are different?
> >
> > To me it seems like a practice not to be recommended - potential for
> > misleading people unfamiliar with the code later on in maintenance mode.
> >
>
> http://lmgtfy.com/?q=then+null+site%3Atkyte.blogspot.com
>
Not sure why you posted that - my question is simply is it bad practice
to raise an exception that means something specific when the underlying
condition found is something diffferent? As in the example here where
someone is finding that a value is NULL but wishes to treat that as
no_data_found.
--
jeremy
joel garry
> In article <799a158c-7229-4232-82a0-264a4a6303c0
> @d4g2000pra.googlegroups.com>, joel-ga...@home.com says...>
>
> - Show quoted text -
Not so much bad practice as bad execution. The idea of trapping for
an error means you are handling an error. If you are handling the
error wrong that would be a bug. The example of handling null as
no_data_found... I would suggest googling on 3 and 4 value null
debates that have been going on since forever. The answer, both for
your question and example, is undefined.
jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2009/dec/24/doomed-china-restaurant-hires-live-in-protester/
Mark D Powell
> In article <79bb2ee5-f28a-43b9-af1e-4597012495f0
>
>
>
> > Cate, why not test for a NULL value being returned and if so the RAISE
> > NO_DATA_FOUND?
>
> What's the opinion of this ng's contributors on the advisability of
> raising "standard" error conditions when the underlying reasons for
> raising that error are different?
>
> To me it seems like a practice not to be recommended - potential for
> misleading people unfamiliar with the code later on in maintenance mode.
>
> --
> jeremy
You have a point. I normally use the 20xxx error codes Oracle
reserved by Oracle for customer application error codes but my post
just adresses one way to do what the OP asked.
Malcolm Dew-Jones
I can't check this at the moment, (pl/sql of course)
select min(datex) into the_min
from tablex where x = 1
and min(datex) is not null;
If that is not allowed then use "group by x" and "having" to do the same
thing. The point of course is to raise no_data_found when that is
desired, and avoid discussions (hence issues) with opinions about what
errors you're allowed to raise.
I think that you should sometimes raise standard Oracle errors, especially
because the caller understands them - but being careful to ensure that the
logic makes sense. Also consider, from the perspective of the caller
there may be no reason to expect a value must be derived as opposed to
being simply looked up - so why would the statement not raise
no_data_found if there is no value?
$0.10