Thomas T
2003-09-24 16:11:46 UTC
Just a quick question with PL/SQL and Oracle 8i (under Win2k o/s)!
I wrote an implicit cursor that would look up one value, and used the
%NOTFOUND property to check to see if the cursor returned a value or not. I
tested the code, and my user-defined exception wasn't raised, the "when
others" loop caught it! I removed the "when others" clause, re-ran the
procedure, and got a no_data_found exception that pointed to my implicit
cursor. In reading up, I found out that SQL%NOTFOUND is only valid for
group functions! Too bad. I think the following code snippet is the only
way to tell if the implicit cursor threw the no_data_found, or if it came
from elsewhere. Any thoughts? Is there anything slightly more
sophisticated then using null as a flag? It brings back flashbacks of
using -999 to determine "end of data" in text streams...
I'm not using an explicit cursor and a for loop because the value I need
isn't needed often; it's only needed if a value in the main "for" loop
changes. An explicit cursor seemed like overkill to me, because I'd be
opening and closing it every time I needed a value. (I'd need to make a
parameterized explicit cursor.) Or should I just go explicit? I usually
use explicit cursors, unless, like this situation, the value is only
retrieved once or very few times.
set serveroutput on
declare
mynum number;
begin
mynum:= 3; --old value from prior operation
mynum:=null; --now need to get new value, so reset value before query
select 5
into mynum
from dual
where 0=5; --yes this was done on purpose
if SQL%NOTFOUND then --doesn't execute b/c no_data_found is raised
dbms_output.put_line('wasn''t found');
end if;
dbms_output.put_line('mynum is ' || mynum);
exception
when no_data_found then
dbms_output.put_line('error handler');
if mynum is null then
dbms_output.put_line('mynum is null so I know where error was');
else
dbms_output.put_line('Unexpected ''no data found'' error');
end if;
end;
/
As you can tell, I just typed this up right in SQL*Plus as a test. Thanks!
-Thomas
I wrote an implicit cursor that would look up one value, and used the
%NOTFOUND property to check to see if the cursor returned a value or not. I
tested the code, and my user-defined exception wasn't raised, the "when
others" loop caught it! I removed the "when others" clause, re-ran the
procedure, and got a no_data_found exception that pointed to my implicit
cursor. In reading up, I found out that SQL%NOTFOUND is only valid for
group functions! Too bad. I think the following code snippet is the only
way to tell if the implicit cursor threw the no_data_found, or if it came
from elsewhere. Any thoughts? Is there anything slightly more
sophisticated then using null as a flag? It brings back flashbacks of
using -999 to determine "end of data" in text streams...
I'm not using an explicit cursor and a for loop because the value I need
isn't needed often; it's only needed if a value in the main "for" loop
changes. An explicit cursor seemed like overkill to me, because I'd be
opening and closing it every time I needed a value. (I'd need to make a
parameterized explicit cursor.) Or should I just go explicit? I usually
use explicit cursors, unless, like this situation, the value is only
retrieved once or very few times.
set serveroutput on
declare
mynum number;
begin
mynum:= 3; --old value from prior operation
mynum:=null; --now need to get new value, so reset value before query
select 5
into mynum
from dual
where 0=5; --yes this was done on purpose
if SQL%NOTFOUND then --doesn't execute b/c no_data_found is raised
dbms_output.put_line('wasn''t found');
end if;
dbms_output.put_line('mynum is ' || mynum);
exception
when no_data_found then
dbms_output.put_line('error handler');
if mynum is null then
dbms_output.put_line('mynum is null so I know where error was');
else
dbms_output.put_line('Unexpected ''no data found'' error');
end if;
end;
/
As you can tell, I just typed this up right in SQL*Plus as a test. Thanks!
-Thomas