Tuesday, October 6th, 2009...7:27 am

Luhn Digit Generation Oracle PL/SQL Implementation

Jump to Comments

Here's how to generate a Luhn Mod-10 Check Digit. It's implemented as a function in oracle PL/SQL. I spent 2 hours looking for this online, then decided to implement it myself since it's not that hard. Anyway, I thought I'd post it here for the next person trolling google for it. This is useful for generating credit card numbers or any other place you need a check digit.

This algorithm works by doing the same thing as the validation algorithm, but shifted over one character (the equivalent of adding a zero to a number, generating the sum & subtracting 10).

*EDIT - there was a bug in this code, i just fixed it on 12/4/2009, the old code returned 10 instead of zero. It also was only working on even numbers, sorry about that!

CREATE OR REPLACE FUNCTION generate_luhn (togen IN varchar)
RETURN number
IS
    curval NUMBER := 0;
    total NUMBER := 0;
    everyother NUMBER := 1;

BEGIN
  IF(
   togen IS NULL
  ) then
    RETURN 0;
  end IF;

 
  FOR i IN reverse 1 .. LENGTH(togen) loop
    curval:=SUBSTR(togen, i, 1);
    IF everyother = 1 then
       everyother := 0;
      curval := curval * 2;
      IF(curval> 9) then
        curval:=curval-9;
      end IF;
     else
       everyother := 1;
    end IF;

   
    total := total + curval;
  end loop;
  IF MOD(total, 10) = 0 THEN
    RETURN 0;
    ELSE
    RETURN 10 - MOD(total, 10);
    END IF;
END generate_luhn;
/

6 Comments

Filed under oracle, sql, tip, Uncategorized

6 Comments

  • Malcolm Speedie
    December 2nd, 2009 at 1:28 am

    Thanks for the sample, it saved me some time.

    Since you are processing a string you might want to check for a value_error on the implicted numeric conversion or do a translate to only get digits.

  • Bilal
    January 31st, 2010 at 8:24 pm

    Thanks. It worked and saved me time.

  • Ferg
    February 25th, 2010 at 8:06 am

    Thanks for the code, but I used this on a DB of ICCIDs (GSM SIM Card identifiers) and it found some inconsistencies (70 out of 3million)
    e.g 8941020551329001200 should have checksum 3, but this PL/SQL returned 4?

  • Johnson
    March 8th, 2010 at 6:15 am

    Thanks for your sharing, it's great.

  • andrej
    November 18th, 2010 at 12:54 pm

    Strange, but this function said that luhn is not valid for some card numbers....

  • andrej
    November 19th, 2010 at 5:23 am

    I am sorry...it is working fine. I just forgot to cut last digit, when i checked number of card.

Leave a Reply