Re: SQL Function call does not fail on no_data_found ?

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Fri, 26 Mar 2010 08:45:20 -0700 (PDT)
Message-ID: <87c2b7e4-3762-4e37-b6b1-26ec12e7f8cc_at_d27g2000yqf.googlegroups.com>

On Mar 25, 8:52 pm, John Hurley <hurleyjo..._at_yahoo.com> wrote:
> On Mar 25, 3:54 am, "Vladimir M. Zakharychev"
>
> <vladimir.zakharyc..._at_gmail.com> wrote:
>
> snip
>
> > The above code more or less covers what single SELECT INTO does. Is it
> > safer? Shorter? More readable? Name a single reason why we should
> > prefer explicit cursors to single-fetch implicit cursors please. :)
>
> An easy reason is something changes somewhere and where you used to
> get 1 row ( or zero rows ) now you get more than 1 row.
>
> Anyone ever hear a Developer ever say "But we were supposed to only
> get back 1 row here?" ...
>
> Take a look at the code posted by the OP.

If this was an expected change then you already know it will happen and you need to change the code to a cursor loop/bulk fetch. If it's an unexpected change, TOO_MANY_ROWS thrown on single-row fetch will immediately alert you of a bug/data corruption and you'll be right on track fixing it. So no, this is not a valid reason.

As of the OP's code - don't see anything wrong with it. Presumably, he knows the data model. In particular, he knows that empno is the primary key so the query can not return more than one row for any empno. Zero rows can be returned though and NO_DATA_FOUND will be raised in this case. If the function is called from PL/SQL, this exception is not handled automatically and bubbles up. If called from SQL however, this exception is expected by the client and means "no more data." Now, since the function didn't actually return a value, NULL (what else?) is assumed as the result.

How would you code this any different with explicit cursor? If you want an exception to be raised and not ignored by the SQL engine, your only options are to trap NO_DATA_FOUND in the function itself and raise an exception that has no special meaning to the SQL engine or expect NULL returns for "wrong" arguments and treat them accordingly. But this has nothing to do with implicit vs. explicit cursors.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Fri Mar 26 2010 - 10:45:20 CDT