Hallo!
anyone can help me?
Why the following stored procedure, using a FOREACH statement don't loop through all records (500.000), only work with the first record.
How can i to catch an exception?

create procedure vus_SprivSample()
define varCodigoServicio char(20);
define varCircuito char(20);
define varAlready char(1);

foreach sprivCursor for
select serv_nm_servic, circ_nm_cto
into varCodigoServicio, varCircuito
from sprmccto
-- Check if the current record already in the tarjet table

select '1' into varAlready
from vista_unificada
where codigo_servicio = varCodigoServicio
and circuito = varCircuito;

if varAlready is null then
-- Insert the record in the tarjet table
begin work;
insert into vista_unificada (codigo_servicio, circuito)
values (varCodigoServicio, varCircuito);
commit work;
end if
let varOk = '0';
end foreach

end procedure;

Hi,

The most probable reason could be the transaction that start and ends within the foreach iteration loop. Transaction closure implicitly closes any open cursors without any notice. To solve this problem:
1) start the transaction before the foreach loop and close it after the loop gets over OR
2) declare the deciding cursor as a “withhold” cursor so that it is not closed even when a transaction being closed.

Regards,
Shriyan