Re: select into NULL in PL/SQL

Thanks for the responses. My question was misleading because I was clueless.

NULL can indeed be selected into a variable via cursor. However, the query must return one and exactly one result, whether NULL or something else. Mine wasn't, hence the message.

There's a nice explanation in "Oracle Web Applications" by Odewahn about the limitations of SELECT INTO and why it's much safer to use explicit cursors.

Thanks again.

"john sprague" <bearpig_at_attglobal.net> wrote in message news:3B393D46.959E1E1B_at_attglobal.net...
>
>
> Jeff Wilson wrote:
>
> > 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".
>
> Parsed above ...
>
> Try a fetch from a seed cursor to get the data . The fecth will put it
 into a
> variable that you can then conditionally handle and drive via a loop
 structure
> if needed into the sub querry.
>
> I avoid select into but only because my own impact analysis search vs
 plsql code
> will identify things specifically labled as 'cursor' so my c-code search
 program
> knows to return all contiguous code. It is a formulaic method of crude
 impact
> analysis in lieu of a real change control / impact analysis code - but
 most of
> my plsql is ad hoc - not that I don't occasionally want to know where I
 used
> 'johnny' so I know if table structures change ( I have a few developers in
 the
> shop ) that I need my code to change ...
>
> Nevertheless... what follows helps:
>
> so ...
>
> open getseed;
>
> <<L_primaryloop>>
> loop
>
> fetch getseed into v_cancontainnull;
>
> exit when getseed%notfound;
>
> if v_cancontainnull is null then .....
> //special null handling mentioned ///
>
> end if;
>
> if v_cancontainnull is not null then .....
> open getsecondquerry;
>
> fecth getsecondquerry into v_whatever1......;
>
> /// process action based on v_whatever one -- initiate loop if
 multiple
> returns possible ///
>
> close getsecondquerry;
>
> end if;
>
> end loop L_primaryloop;
>
> close getseed;
>
Received on Tue Jul 03 2001 - 00:02:30 CDT