Re: Watch out for this one :o)

Norman Dunbar wrote:

> I've just been bitten by the following, and I can't find it in the docs,
> so maybe it is an 'undocumented feature', but it is most certainly on my
> list of gotchas now !
>
> CREATE TABLE TEST(A NUMBER, B NUMBER);
> INSERT INTO TEST VALUES (1,1);
> COMMIT;
>
> CREATE OR REPLACE FUNCTION tester(in_a IN NUMBER) RETURN NUMBER
> AS
> BEGIN
> total_amt NUMBER := 0;
>
> select sum(b) into total_amt
> from test
> where a = in_a);
>
> return (total_amt);
>
> EXCEPTION
> WHEN no_data_found THEN
> return (-1);
> END;
> /
>
> select tester(1) from dual; returns 1 as expected.
> select tester(4) from dual; returns NULL and not -1 as expected.
>
> It appears that aggregate functions don't fire off the EXCEPTION when
> nothing is returned from the table.
> Same happens in 8174 and 7134 which is all I've tested.
>
> If I remove the SUM() part, and just return the 'b' column, then the
> exception is fired for the second test.
>
> Nothing on Metalink, Google or in the DOcs - as far as I can see.
>
> Cheers,
> Norman.
>
> -------------------------------------
> Norman Dunbar
> Database/Unix administrator
> Lynx Financial Systems Ltd.
> mailto:Norman.Dunbar_at_LFS.co.uk
> Tel: 0113 289 6265
> Fax: 0113 289 3146
> URL: http://www.Lynx-FS.com
> -------------------------------------

This is a normal behavior. You asked it to sum something and it did so there is no error. And this is not related to it being an aggregate function as you will find this does not happen with SELECT COUNT(*): It is just the way SUM works. And is a good reason to always wrap NVL() around a SUM.

Daniel Morgan Received on Mon Jul 22 2002 - 10:55:20 CDT