oracle-databaseplsqloracle-apexora-01722

PL/SQL Error ORA-01722 when looping through cursor


For a project I need to modify some attributes in a table based off of the value of that same attribute. I decided to go with cursors, and wrote the following:

DECLARE
totale_rente rekening.saldo%TYPE;
cursor c_boven1000 is
select r.reknummer,
       r.saldo,
       rt.rentepercentage,
       ABS(ROUND(r.saldo * (rt.rentepercentage/100), 2 )) as teBetalen
       FROM rekening r
       join rekeningtype rt on rt.naam = r.rekeningtype
       Where r.saldo < 0 and saldo >= -1000;
 cursor c_onder1000 is 
 select r.reknummer,
        r.saldo,
        rt.rentepercentage,
        ABS(ROUND(r.saldo * ((rt.rentepercentage*2)/100), 2 )) as teBetalen
        From rekening r
        join rekeningtype rt on rt.naam = r.rekeningtype
        Where r.saldo < -1000;

 TYPE rek_saldo IS TABLE OF rekening.saldo%TYPE;
 TYPE rek_nummer IS TABLE OF rekening.reknummer%TYPE;
 TYPE type_percentage IS TABLE OF rekeningtype.rentepercentage%TYPE;
 TYPE rek_tebetalen IS TABLE OF rekening.saldo%TYPE;

 rek_saldos rek_saldo;
 rek_nummers rek_nummer;
 type_percentages type_percentage;
 rek_tebetalens rek_tebetalen;

BEGIN
OPEN c_boven1000;
FETCH c_boven1000 BULK COLLECT INTO rek_saldos, rek_nummers, type_percentages, rek_tebetalens;
CLOSE c_boven1000;
FOR x IN rek_nummers.first..rek_nummers.last LOOP
    UPDATE rekening r
    SET r.saldo = r.saldo - rek_tebetalens(x)
    WHERE r.reknummer = rek_nummers(x);
    totale_rente := totale_rente + rek_tebetalens(x);
END LOOP;
OPEN c_onder1000;
FETCH c_onder1000 BULK COLLECT INTO rek_saldos, rek_nummers, type_percentages, rek_tebetalens;
CLOSE c_onder1000;
FOR x IN rek_nummers.first..rek_nummers.last LOOP
    UPDATE rekening r
    SET r.saldo = r.saldo - rek_tebetalens(x)
    WHERE r.reknummer = rek_nummers(x);
    totale_rente := totale_rente + rek_tebetalens(x);
END LOOP;    

    UPDATE rekening r
    SET saldo = saldo + totale_rente
    WHERE r.reknummer = '2250';
END;

In this case, reknummer is a Varchar, saldo is a number(10,2), rentepercentage is a number (3,2).

When executing, i got the following error:

ORA-01722: Invalid number for execute PL/SQL code.

Not sure if it is important, but this code block is inside a dynamic action on clicking a button. I have tried to find my error, but have been unable to. Could anyone assist?


Solution

  • The order in which you select your columns and the order of the variables is not the same.

     select r.reknummer
           ,r.saldo
           ,rt.rentepercentage
    
     into rek_saldos
              ,rek_nummers
              ,type_percentages
    

    You can use a case statement in your cursor as such:

    select r.reknummer
          ,r.saldo
          ,rt.rentepercentage
          ,case
              when saldo >= -1000 then
               abs(round(r.saldo * (rt.rentepercentage / 100), 2))
              else
               abs(round(r.saldo * ((rt.rentepercentage * 2) / 100), 2))
           end as tebetalen
      from rekening r
      join rekeningtype rt
        on rt.naam = r.rekeningtype
     where r.saldo < 0