Re: select into NULL in PL/SQL
Date: Sat, 21 Jul 2001 21:17:15 GMT
Message-ID: <9hdkdb$lg1$1_at_spiney.sierra.com>
pl/sql fragment using EXCEPTION handling:
my_var number;
begin
select A_VALUE into MY_VAR from MY_TABLE where A_Condition_Is_True;
do some processing
:
:
exception
when NO_DATA_FOUND then
begin
deal with the fact that A_VALUE was null.
end;
end;
Using this method, be particularly careful when using SELECT COUNT(*) INTO var
my_var number;
begin
select count(*) into MY_VAR from MY_TABLE where A_Condition_Is_True;
if MY_VAR is not null then
do some processing
end if;
:
:
exception /* this exception will never occur when no data exists */
when NO_DATA_FOUND then
begin
deal with the fact that A_VALUE was null.
end;
end;
"Jeff Wilson" <jwilson2000_at_home.com> wrote in message
news:CX9_6.253872$p33.5088230_at_news1.sttls1.wa.home.com...
> Have looked everywhere for an example or explanation of this without
> success.
>
> Have a table with a column that can contain NULL. Am using a cursor to
loop
> over every record and load this column value into a variable using SELECT
> INTO.
>
> When I get to a NULL value, I get an ORA-04013 or similar that says "data
> not found". I actually want to treat NULL values in a particular way in
the
> logic, not ignore them. Otherwise, I could just bind the cursor to a
query
> that leaves out the NULLs.
>
> I guess it makes sense to not want uninitialized variables floating around
> your PL/SQL code, but Oracle handles NULL values pretty gracefully
> elsewhere.
>
> Can someone provide either an explanation of why this must work this way
or
> a possible workaround.
>
> Many thanks.
>
> --
> Jeff Wilson
> jwilson2000_at_home.com
> Eugene, Oregon
>
> (541) 684-8590
>
>
Received on Sat Jul 21 2001 - 23:17:15 CEST