CODEWITHSUNDEEP
oracle-databaseplsqloracle-apexora-01722
Maarten B
Maarten B

Reputation: 165

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?

Upvotes: 0

Views: 1826

Answers (1)

Rene
Rene

Reputation: 10541

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

Upvotes: 3

Related Questions