Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2004
    Posts
    5

    Post Unanswered: FOREACH Don't work

    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;

  2. #2
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •