Re: SQL Function call does not fail on no_data_found ?

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Thu, 25 Mar 2010 00:54:34 -0700 (PDT)
Message-ID: <3deec59f-af54-4dd2-8a7f-b8048f8b8d2d_at_15g2000yqi.googlegroups.com>

On Mar 24, 5:40 pm, John Hurley <hurleyjo..._at_yahoo.com> wrote:
> On Mar 23, 12:45 pm, webtourist <webtour..._at_gmail.com> wrote:
>

>
> I would add that the SELECT INTO is a dangerous construct ... using a
> cursor is a better approach in PLSQL.
>
> You can then test for %FOUND and/or %NOTFOUND and do logical checking
> on how many rows or none ... etc.

SELECT INTO is not dangerous. It's a single row fetch with all checks (at least one row, at most one row) embedded and corresponding exceptions thrown automatically. Saves you a lot of keystrokes. Equivalent code with explicit cursor would be like this:

DECLARE
  CURSOR C1 IS SELECT ... FROM ... WHERE ...;   X C1%ROWTYPE;
BEGIN
  OPEN C1;
  FETCH C1 INTO X;

• at least one row IF C1%NOTFOUND THEN RAISE NO_DATA_FOUND; END IF;
• at most one row FETCH C1 INTO X; IF C1%FOUND THEN RAISE TOO_MANY_ROWS; END IF;
• If the second fetch doesn't find anything, X will still hold
• the row retrieved by the first fetch, so we can safely proceed
• with processing it.

  <process data here>

• we won't get here if any of the above conditions are met and
• exceptions are raised, so we also need to explicitly close the
• cursor in the exception handler to prevent cursor leak. CLOSE C1; EXCEPTION WHEN OTHERS THEN
  • we might get an exception on OPEN, so we need to check if
  • the cursor is actually opened before attempting to close it,
  • otherwise we'll get INVALID_CURSOR exception inside the
  • exception handler itself. IF C1%ISOPEN THEN CLOSE C1; END IF; RAISE; -- bubble the original exception up END;

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

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Thu Mar 25 2010 - 02:54:34 CDT