Calculating modulus for large numbers in PL/SQL

Question

I'm trying to calculate big modulus in PL/SQL. I implemented a method that I found on this location: http://en.wikipedia.org/wiki/International_Bank_Account_Number under Modulo operation on IBAN. But I get wrong results when I use this number: 221021290110000123452229211210282900128222984.

This is how the method needs to be done:

Piece-wise calculation D mod 97 can be done in many ways. One such way is as follows:

Starting from the leftmost digit of D, construct a number using the first 9 digits and call it N.
Calculate N mod 97. If the result is less than 10, prefix the result with a 0, giving a result in the range 00 to 96.
Construct a new 9-digit N by concatenating above result (step 2) with the next 7 digits of D. If there are fewer than 7 digits remaining in D but at least one, then construct a new N, which will have less than 9 digits, from the above result (step 2) followed by the remaining digits of D
Repeat steps 2–3 until all the digits of D have been processed

The result of the final calculation in step 2 will be D mod 97 = N mod 97.

I did some logging, these are the results of all the modulo steps:

221021290110000123452229211210282900128222984 (221021290 % 97 = 0)
00110000123452229211210282900128000000 (001100001 % 97 = 21)
2123452229211210282900128000000 (212345222 & 97 = 0)
009211210282900128000000 (009211210 % 97 = 90)
90282900128000000 (902829001 % 97 = 46)
4628000000 (462800000 % 97 = 2)
020 = 20

The result needs to be 1. I checked it with a modulo calculator and it needs to be 1. With most numbers the calcutor is correct.

This is the code I made, how is it possible that this number isn't correct:

create or replace function doubleDiget (iban number)
return varchar2
as
begin
  if(iban <= 9) then
    return concat('0',iban);
  else
    return iban;
  end if;
end doubleDiget;

create or replace FUNCTION modbig (iban number)   
    RETURN varchar2                     
      AS  
      lengthIban number(38);
      modUitkomts number(38);
      modUitkomtsc varchar(38);
      restIban varchar(38);
      modlength number(38);
  BEGIN

  modUitkomts := SUBSTR(iban,0,9) mod 97;
  modUitkomtsc := doubleDiget(modUitkomts);
  restIban := concat(modUitkomtsc,SUBSTR(iban,10)); 
  dbms_output.put_line(restIban);

  loop
    if( length(restIban) >= 9) then
        modUitkomts := SUBSTR(restIban,0,9) mod 97;
        modUitkomtsc := doubleDiget(modUitkomts);
        restIban := concat(modUitkomtsc,SUBSTR(restIban,10)); 
        dbms_output.put_line(restIban);
    else
      exit;
    end if;
  end loop;

  modUitkomts := restIban mod 97;
  return modUitkomts;


END modbig;

begin
DBMS_OUTPUT.PUT_LINE(modbig(221021290110000123452229211210282900128222984));  
end;

Vincent Malgrat · Accepted Answer · 2014-01-03T16:41:54.750

Use VARCHAR2 instead of NUMBER:

SQL> DECLARE
  2     FUNCTION modbig(iban VARCHAR2) RETURN VARCHAR2 IS -- won't be rounded
  3        lengthIban   NUMBER(38);
  4        modUitkomts  NUMBER(38);
  5        modUitkomtsc VARCHAR(38);
  6        restIban     VARCHAR(50);
  7        modlength    NUMBER(38);
  8     BEGIN
  9        restIban     := iban;
 10        modUitkomts  := SUBSTR(restIban, 0, 9) MOD 97;
 11        modUitkomtsc := LPAD(modUitkomts, 2, '0');
 12        restIban := concat(modUitkomtsc, SUBSTR(iban, 10));
 13  
 14        LOOP
 15           IF (length(restIban) >= 9) THEN
 16              modUitkomts  := SUBSTR(restIban, 0, 9) MOD 97;
 17              modUitkomtsc := LPAD(modUitkomts, 2, '0');
 18              restIban := concat(modUitkomtsc, SUBSTR(restIban, 10));
 19           ELSE
 20              EXIT;
 21           END IF;
 22        END LOOP;
 23        modUitkomts := restIban MOD 97;
 24        RETURN modUitkomts;
 25     END modbig;
 26  
 27  BEGIN
 28     DBMS_OUTPUT.PUT_LINE(
 29        modbig('221021290110000123452229211210282900128222984')); -- varchar2
 30  END;
 31  /
1
PL/SQL procedure successfully completed

Explanation: the NUMBER dataype has a precision of about 38 digits, so this will be rounded for very large number, hence your output ending in 0000 instead of the digits you did input.

Calculating modulus for large numbers in PL/SQL

1 Answers1