Re: SELECT INTO when there is no data.

Moshe_t_at_yahoo.com (moshe) wrote in message news:<616be6f6.0403220131.12cba5a0_at_posting.google.com>...
> Still, I can't use it in FOR LOOP (cursor).
> I have FOR LOOP, and I would like that the loop will continue even
> there's NO_DATA_FOUND or TOO_MANY_ROWS or other exceptions.
> I don't like to stop the procedure. I want to handle with that
> exceptions and keep moving to the next record.
> I read in the doc's many pages. I understood that the EXCEPTION comes
> after the END. Meaning that the EXCEPTION will stop my procedure/loop.
> and I can't go back in order to continue the procedure with the next
> record.
>
> Am I right ? What would you do in that case ?
>
> With lot of thanks,
> Moshe.
>

Can't really see what your problem is. You can nest BEGIN -> EXCEPTION -> END within your PL/SQL. The exception doesn't have to take you out of the loop. Like this (cursor for loop):

DECLARE
   the_variable NUMBER;
BEGIN
   FOR r IN (SELECT column1 FROM table1) LOOP

       BEGIN
          SELECT column3
            INTO the_variable
            FROM table2
           WHERE column2 = r.column1;
           <do your processing>
       EXCEPTION
          WHEN NO_DATA_FOUND THEN
             <handle your exception>;
       END;

CE Received on Mon Mar 22 2004 - 18:34:40 CET