Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP

[Christophe Pettus]

> On Oct 18, 2022, at 14:29, Ravi Krishna <s_ravikris...@aol.com> wrote:
> 
> > You can commit in a loop, but not in BEGIN / END block that has an 
> > exception handler:
> > that creates a subtransaction for the duration of the BEGIN / END.
> 
> The reason I have to deal with error exception is that I want to ignore 
> failure on a table and move on to next table. 

Rather than have a loop inside the BEGIN / END, you could put the BEGIN 
EXCEPTION END inside the loop, catch the error, store the important parts of 
the exception in a variable, and then do the COMMIT after the END statement but 
before the next iteration of the loop.  A bit messier, but it gets the job done.