Prev: oracle 10g disaster recoveries
Next: best tutorial for installing oracle 10 for Solaris 10 x86
From: Galen Boyer on 24 Mar 2010 21:52
ddf <oratune(a)msn.com> writes:

> On Mar 23, 12:45�pm, webtourist <webtour...(a)gmail.com> wrote:
>> 10gR2:
>>
>> given a function (in package "test") like this:
>>
>> � FUNCTION get_ename (p_empno IN NUMBER) RETURN VARCHAR2 IS
>> � �l_ename VARCHAR2(90);
>> � BEGIN
>> � � SELECT ename INTO l_ename FROM emp WHERE empno = p_empno ;
>> � � RETURN l_ename ;
>> � END ;
>>
>> And a SQL function calls:
>>
>> > select test.get_ename(7499) from dual;
>>
>> TEST.GET_ENAME(7499)
>> ------------------------
>> ALLEN
>>
>> > select test.get_ename(74992) from dual;
>>
>> TEST.GET_ENAME(74992)
>> -----------------------
>>
>> I just realized the the above query wouldn't fail with "no_data_found"
>> - has this always been like this behavior ?
>
> Where did you handle that exception? Possibly this is how you should
> have coded your function:
>
> FUNCTION get_ename (p_empno IN NUMBER) RETURN VARCHAR2 IS
> l_ename VARCHAR2(90);
> BEGIN
> SELECT ename INTO l_ename FROM emp WHERE empno = p_empno ;
> RETURN l_ename ;
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> DBMS_OUTPUT.PUT_LINE('No employee with empno '||p_empno);
> WHEN TOO_MANY_ROWS THEN
> DBMS_OUTPUT.PUT_LINE('Data integrity error for empno '||
> p_empno||': More than one employee assigned to this empno');
> END ;
>
>
> Then it would fail on NO_DATA_FOUND.

How would the above fail? You just swallowed the exception with the
above code.

> Oracle did raise the exception but there was nothing coded to properly
> handle it.

The idea was that without handling the exception, Oracle should have
thrown it.

--
Galen Boyer

--- news://freenews.netfront.net/ - complaints: news(a)netfront.net ---
From: Galen Boyer on 24 Mar 2010 21:53
John Hurley <hurleyjohnb(a)yahoo.com> writes:

> I would add that the SELECT INTO is a dangerous construct ... using a
> cursor is a better approach in PLSQL.

How is that true? PLSQL over SQL? When is that ever better?

--
Galen Boyer

--- news://freenews.netfront.net/ - complaints: news(a)netfront.net ---
From: Vladimir M. Zakharychev on 25 Mar 2010 03:54
On Mar 24, 5:40 pm, John Hurley <hurleyjo...(a)yahoo.com> wrote:
> On Mar 23, 12:45 pm, webtourist <webtour...(a)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
From: ddf on 25 Mar 2010 09:54
On Mar 24, 9:52 pm, Galen Boyer <galen_bo...(a)yahoo.com> wrote:
> ddf <orat...(a)msn.com> writes:
> > On Mar 23, 12:45 pm, webtourist <webtour...(a)gmail.com> wrote:
> >> 10gR2:
>
> >> given a function (in package "test") like this:
>
> >>   FUNCTION get_ename (p_empno IN NUMBER) RETURN VARCHAR2 IS
> >>    l_ename VARCHAR2(90);
> >>   BEGIN
> >>     SELECT ename INTO l_ename FROM emp WHERE empno = p_empno ;
> >>     RETURN l_ename ;
> >>   END ;
>
> >> And a SQL function calls:
>
> >> > select test.get_ename(7499) from dual;
>
> >> TEST.GET_ENAME(7499)
> >> ------------------------
> >> ALLEN
>
> >> > select test.get_ename(74992) from dual;
>
> >> TEST.GET_ENAME(74992)
> >> -----------------------
>
> >> I just realized the the above query wouldn't fail with "no_data_found"
> >> - has this always been like this behavior ?
>
> > Where did you handle that exception?  Possibly this is how you should
> > have coded your function:
>
> >   FUNCTION get_ename (p_empno IN NUMBER) RETURN VARCHAR2 IS
> >    l_ename VARCHAR2(90);
> >   BEGIN
> >     SELECT ename INTO l_ename FROM emp WHERE empno = p_empno ;
> >     RETURN l_ename ;
> >   EXCEPTION
> >     WHEN NO_DATA_FOUND THEN
> >         DBMS_OUTPUT.PUT_LINE('No employee with empno '||p_empno);
> >     WHEN TOO_MANY_ROWS THEN
> >         DBMS_OUTPUT.PUT_LINE('Data integrity error for empno '||
> > p_empno||': More than one employee assigned to this empno');
> >   END ;
>
> > Then it would fail on NO_DATA_FOUND.  
>
> How would the above fail?  You just swallowed the exception with the
> above code.
>
> > Oracle did raise the exception but there was nothing coded to properly
> > handle it.
>
> The idea was that without handling the exception, Oracle should have
> thrown it.
>
> --
> Galen Boyer
>
> --- news://freenews.netfront.net/ - complaints: n...(a)netfront.net ---- Hide quoted text -
>
> - Show quoted text -

You're behind on the posts to this thread, Galen. :) I've already
admitted my original code still wouldn't fail even with the exception
handler as it's a function. I'll post the revised code again:

FUNCTION get_ename (p_empno IN NUMBER) RETURN VARCHAR2 IS
l_ename VARCHAR2(90);
v_emp_ct number:=0;
BEGIN
select count(*) into v_emp_ct from emp where empno = p_empno;


if v_emp_ct = 0 then
raise_application_error(-20111, 'No employee found with
empno
'||p_empno);
else
SELECT ename INTO l_ename FROM emp WHERE empno = p_empno ;
end if;
RETURN l_ename ;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Data integrity error for empno '||
p_empno||': More than one employee assigned to this empno');
END ;


There is no NO_DATA_FOUND exception to trap because, as Jonathan
Lewis noted, an ORA-01403 is thrown at the end of a fetch when no more
rows are available:

"...queries (that run to completion) end with an ignored 'no data
found' exception; that's how Oracle reports 'no more data' to the
front-end. Try running a simple 'select user from dual' from SQL*Plus
with event 10079 set to level 2 and you'll see text like the
following
in the trace file:


328E320 00000000 00000000 00000000 00000000 [................]
328E330 00000000 00000000 00000000 524F1900 [..............OR]
328E340 31302D41 3A333034 206F6E20 61746164 [A-01403: no data]
328E350 756F6620 000A646E [ found..]


Your example still looks like an anomaly, though, as you could expect
a silent return of NO rows from 'select bad_func(1) from dual',
rather
than a silent return of a null value. I would guess that this is a
side effect of the requirement for scalar subqueries to result in a
NULL return when they actually find no data. "

Look at my first example again and you will find an exception handler
for NO_DATA_FOUND but it won't be handled as expected in a function
due to Jonathan's explanation. The exception wasn't 'swallowed' by my
code, but it won't trap what cannot be trapped and, in a function,
that's NO_DATA_FOUND.


David Fitzjarrell
From: John Hurley on 25 Mar 2010 13:47
On Mar 24, 9:53 pm, Galen Boyer <galen_bo...(a)yahoo.com> wrote:

snip

> How is that true?  PLSQL over SQL?  When is that ever better?

It was PLSQL all along ...

BEGIN
SELECT ename INTO l_ename FROM emp WHERE empno = p_empno ;
RETURN l_ename ;
END ;

AFAIK the only way to do SELECT INTO in oracle is in PLSQL.
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: oracle 10g disaster recoveries
Next: best tutorial for installing oracle 10 for Solaris 10 x86