Re: min(); never no_data_found

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 27 Dec 2009 15:50:14 -0700
Message-ID: <4b37f2b6$1_at_news.victoria.tc.ca>

Mark D Powell (Mark.Powell2_at_hp.com) wrote:
: On Dec 23, 10:10=A0am, Jeremy <jeremy0..._at_gmail.com> wrote:
: > In article <79bb2ee5-f28a-43b9-af1e-4597012495f0
: > _at_a32g2000yqm.googlegroups.com>, Mark.Powe..._at_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

: 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.

: HTH -- Mark D Powell --

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 Received on Sun Dec 27 2009 - 16:50:14 CST