Re: Returning from Exception Handler

Kevin J. Donovan wrote:
>
> Help diffculty returning to loop from exception handler
>
> Im having some error handling headaches here... not
> really headaches, more like Im pulling my hair out !
> Heres the problem, Within a stored procedure I have
> cursor that is populated via a select statement. I take
> the values of this cursor and then insert them into another
> table in a loop. My problem is that there may be records
> in the cursor that would be considered duplicate rows ie:
> (ORA-00001) in the table Im attempting to insert the
> records into. I have no problem trapping this error,
> my problem is after I have trapped the error my procedure
> ends! What I want to happen is when the exception is
> noted the procedure returns to the next item in the loop.
> Now as a work around I have put an IF construct in the
> middle of my loop that looks in the table Im trying
> to inset into for the record before I do it, I know there
> has got to be a better way ! HELP ! The exact syntax
> of my procedure is as follows:
>
> Thanks in advance
> Kevin Donovan kdonovan_at_edgewater.com
> Edgewater Technology (617) 246-3343 voice
> 20 Harvard Mill Square (617) 246-5903 fax
> Wakefield, MA 01880
>

<original code snipped>

To 'return' from an exception is simple: begin
  open c1; -- open cursor
  loop
    fetch c1 -- fetch
    into rec;
    exit when c1%notfound;
    begin -- start a new block (insert may raise an exception)

       insert into ......
    exception

        when dup_val_on_index then null;     end; -- end the insert block
   end loop;
end;

All you have to do is start a new block for the insert.

-- 
Gert Rijs
  gem_at_wirehub.net      (at home)
  gert.rijs_at_corp.ah.nl (at work)