Prev: oracle 10g disaster recoveries
Next: best tutorial for installing oracle 10 for Solaris 10 x86
From: John Hurley on 25 Mar 2010 13:52
On Mar 25, 3:54 am, "Vladimir M. Zakharychev"
<vladimir.zakharyc...(a)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.
From: Mladen Gogala on 25 Mar 2010 14:06
On Thu, 25 Mar 2010 10:47:25 -0700, John Hurley wrote:

> AFAIK the only way to do SELECT INTO in oracle is in PLSQL.

You can do it any 3GL language interface.



--
http://mgogala.freehostia.com
From: Vladimir M. Zakharychev on 26 Mar 2010 11:45
On Mar 25, 8:52 pm, John Hurley <hurleyjo...(a)yahoo.com> wrote:
> On Mar 25, 3:54 am, "Vladimir M. Zakharychev"
>
> <vladimir.zakharyc...(a)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
First  |  Prev  | 
Pages: 1 2 3 4
Prev: oracle 10g disaster recoveries
Next: best tutorial for installing oracle 10 for Solaris 10 x86