Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721

    Unanswered: no data found error in UPDATE statement

    Hi. I have the following procedure which is givin me an error..

    Code:
    CREATE OR REPLACE PROCEDURE nixon (p_zonaini inmuebles.zon_codigo%TYPE,
                                       p_zonaFin inmuebles.zon_codigo%TYPE,
                                       p_periodo  facturas.pfc_codigo%TYPE,
                                       p_origen          datos.origen%TYPE
                                       ) IS
     BEGIN
      DECLARE
        v_agua           NUMBER := 0;
        v_alcant         NUMBER := 0;
        v_mora           NUMBER := 0;
        v_otros          NUMBER := 0;
        v_imppago        NUMBER := 0;
        i                PLS_INTEGER;
        consld           number(3);
        
        TYPE t_inmuebles  IS TABLE OF inmuebles.codigo%TYPE      INDEX BY BINARY_INTEGER;
        TYPE t_conceptos  IS TABLE OF datos.agua%TYPE            INDEX BY BINARY_INTEGER;
        TYPE t_pfc_codigo IS TABLE OF facturas.pfc_codigo%TYPE   INDEX BY BINARY_INTEGER;
        r_inm_codigo  t_inmuebles;
        r_pfc_codigo  t_pfc_codigo;
        r_importe     t_conceptos;
        r_totalagua   t_conceptos;
        r_totalalcant t_conceptos;
        r_totalmora   t_conceptos;
        r_totalotros  t_conceptos;
        
        CURSOR c_inmuebles IS
        SELECT /*+ INDEX (p PAG_FECHA) */
               p.inm_codigo,
               decode(nvl(a.fac_pfc_codigo,1),1,d.periodo,a.fac_pfc_codigo) pfc_codigo,
               sum(nvl(a.importe,0))+sum(nvl(d.importeapli,0)) importe
          FROM pagos p, aplpagos a, inmuebles i, saldos s, saldos_det d
         WHERE p.codigo            = a.pag_codigo(+)
           AND p.inm_codigo        = i.codigo
           AND s.inm_codigo(+)     = p.inm_codigo
           AND d.sld_inm_codigo(+) = s.inm_codigo
           AND d.sld_fecha(+)      = s.fecha
           AND s.pag_codigo(+)     = p.codigo
           AND a.estado(+)         = 'A'
           AND p.fecpago           >= to_date(p_periodo||'01','yyyymmdd')
           AND p.fecpago           <= last_day(to_date(p_periodo||'01','yyyymmdd'))
           AND p.hora_elm          IS NULL
           AND p.origen            NOT IN ('R','D')
           AND i.zon_codigo        BETWEEN NVL(p_zonaini,'000') AND NVL(p_zonafin,'ZZZ')
           GROUP BY p.inm_codigo,
                    decode(nvl(a.fac_pfc_codigo,1),1,d.periodo,a.fac_pfc_codigo);
                  
        CURSOR c_conceptos (p_inmueble IN inmuebles.codigo%TYPE,
                            p_fac_pfc_codigo IN facturas.pfc_codigo%TYPE) IS
        SELECT co.codigo, fa.valor, orden
          FROM conceptos co, faccon fa, facturas f
         WHERE f.factura     IS NOT NULL
           AND f.fact_sust   IS NULL
           AND co.codigo         = fa.con_codigo
           AND fa.con_codigo    != consld
           AND fa.fac_inm_codigo = f.inm_codigo
           AND fa.fac_Pfc_Codigo = f.Pfc_Codigo
           AND fa.conse          = f.conse
           AND f.inm_codigo      = p_inmueble
           AND f.pfc_codigo      = p_fac_pfc_codigo
         ORDER BY orden;
          
      BEGIN
        -- borro datos sesion anterior
        DELETE FROM datos WHERE origen = p_origen;
        COMMIT;
        
        -- codigo salgo a favor 
        select sld_con_codigo
        into consld
        from srvparametros;
        
        OPEN c_inmuebles;
        LOOP
        
          i := 1;
          
          FETCH c_inmuebles BULK COLLECT INTO r_inm_codigo, r_pfc_codigo, r_importe LIMIT 100;
                           
              v_imppago              := r_importe(i);
                  
              FOR c_c IN c_conceptos(r_inm_codigo(i),r_pfc_codigo(i)) LOOP
                  
                  IF v_imppago > 0 THEN
                  
                    IF c_c.codigo IN (1,3) THEN 
                      ----------------------------------------------
                      --   Concepto de Agua y Agua de Pozo         -
                      ----------------------------------------------
                       v_agua := Nvl(c_c.valor,0);
                       If v_agua <= v_ImpPago THEN
                          v_ImpPago := v_ImpPago - v_agua;
                       Else
                          v_agua := v_ImpPago;
                          v_ImpPago := 0;
                       End If;
                       r_totalagua(i) := r_totalagua(i) + v_agua;
                    ELSIF c_c.codigo = 2 THEN
                      ----------------------------------------------
                      --   Concepto de Alcantarillado              -
                      ----------------------------------------------
                       v_alcant := Nvl(c_c.valor,0);
                       If v_alcant <= v_ImpPago THEN
                          v_ImpPago := v_ImpPago - v_alcant;
                       Else
                          v_alcant := v_ImpPago;
                          v_ImpPago := 0;
                       End If;
                       r_totalalcant(i) := r_totalalcant(i) + v_alcant;
                    ELSIF c_c.codigo = 10 THEN
                      ----------------------------------------------
                      --   Concepto de Mora                        -
                      ----------------------------------------------
                       v_mora := Nvl(c_c.valor,0);
                       If v_mora <= v_ImpPago THEN
                          v_ImpPago := v_ImpPago - v_mora;
                       Else
                          v_mora := v_ImpPago;
                          v_ImpPago := 0;
                       End If;
                       r_totalmora(i) := r_totalmora(i) + v_mora;
                    ELSE
                      ----------------------------------------------
                      --   Demas Conceptos                         -
                      ----------------------------------------------
                       v_otros := Nvl(c_c.Valor,0);
                       If v_otros <= v_ImpPago Then
                          v_ImpPago := v_ImpPago - v_otros;
                       Else
                          v_otros := v_ImpPago;
                          v_ImpPago := 0;
                       End If;
                       r_totalotros(i) := r_totalotros(i) + v_otros;
                    END IF;
                  END IF;  
                  
              END LOOP; -- c_conceptos
              
              i := i + 1;          
              
              EXIT WHEN c_inmuebles%NOTFOUND;     
              
        END LOOP; -- c_inmuebles 
        
        CLOSE c_inmuebles;
        
        FOR i IN 1..r_inm_codigo.COUNT LOOP
             
             UPDATE datos
                SET agua           = agua + r_totalagua(i),
                    alcantarillado = alcantarillado + r_totalalcant(i),
                    mora           = mora + r_totalmora(i),
                    otros          = otros + r_totalotros(i)
              WHERE inmueble       = r_inm_codigo(i)
                AND origen         = p_origen;
    
             IF SQL%NOTFOUND THEN
             
               INSERT INTO datos (inmueble,
                                  agua,
                                  alcantarillado,
                                  mora,
                                  otros,
                                  origen)
                           VALUES (r_inm_codigo(i),
                                   r_totalagua(i),
                                   r_totalalcant(i),
                                   r_totalmora(i),
                                   r_totalotros(i),
                                   p_origen);
            
            END IF;
        
        END LOOP;
        
        COMMIT;
            
      END;
      
     END;
    The error is..
    Code:
    *
    ERROR at line 1:
    ORA-01403: no data found
    ORA-06512: at "ACEA.NIXON", line 149
    ORA-06512: at line 1
    Which line 149 points to the UPDATE statement..
    Code:
             UPDATE datos
                SET agua           = agua + r_totalagua(i),
                    alcantarillado = alcantarillado + r_totalalcant(i),
                    mora           = mora + r_totalmora(i),
                    otros          = otros + r_totalotros(i)
              WHERE inmueble       = r_inm_codigo(i)
                AND origen         = p_origen;
    In the for loop. The thing is, I am catching if they are not rows to update, to actually insert and not raise an error. How can I avoid this error ? Also, if I try to dbms_ouput.put_line all of the record variables, it works fine, it just raise this error at this update statement.

    Thanks!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    These lines are in the wrong order:
    Code:
      i := i + 1;          
      EXIT WHEN c_inmuebles%NOTFOUND;
    Should be:
    Code:
      EXIT WHEN c_inmuebles%NOTFOUND;     
      i := i + 1;

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    It is giving me the same exact error message nonetheless.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You need to do some debugging, but there do seem to be "holes" in your code. For example, array r_totalagua is only populated when "c_c.codigo IN (1,3)". You should populate it with something (NULL for example) for every i value - otherwise you get the NO_DATA_FOUND error later.

    Actually, I am confused by the way your code uses BULK COLLECT to get 100 rows at a time, but within a loop that increments i by 1 each time and looks at only the i'th record. In other words, your code does something like this:

    Fetch rows 1-100 into arrays
    Do something with 1st array element (i.e. cursor row 1)
    Fetch rows 101-200 into arrays
    Do something with 2nd array element (i.e. cursor row 102)
    Fetch rows 201-300 into arrays
    Do something with 3rd array element (i.e. cursor row 203)
    ...

    It seems your code used to be 1 row at a time, and has been semi-converted to bulk processing.

Posting Permissions

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