Watch out for this one :o)

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Mon, 22 Jul 2002 16:12:48 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA702670ADA@lnewton.leeds.lfs.co.uk>

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


-------------------------------------

Received on Mon Jul 22 2002 - 10:12:48 CDT