Re: select into NULL in PL/SQL

From: Steve McDaniels <steve.mcdaniels_at_sierra.com>
Date: Sat, 21 Jul 2001 21:17:15 GMT
Message-ID: <9hdkdb$lg1$1@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 - 16:17:15 CDT