Validate the credit card (oracle 10 g plsql code) – working!
The Luhn algorithm or Luhn formula, also known as the “modulus 10″ or “mod 10″ algorithm, is a simple checksum formula used to validate a variety of identification numbers, such as credit card numbers and Canadian Social Insurance Numbers.
Recently, Oracle has stopped validating the credit card numbers as there is match for Authorization of credit card. However, this rudimentary mod-10 LUHN algorithm is still valid and valuable for us to check.
Hence, I started creating this mod-10 LUHN algorithm in plsql. This is tested with oracle 10 g database and working well. Special Thanks to ashish who has fixed the bug and tested the code.
Download the credit_card_validator.zip
– Copyright 2008 Author:Jiltin
–=============================================================================
– DESCRIPTION
– I have been searching for a plsql package to validate the credit card.
– Eventhough I created a Visual C# program to validate credit card,
– I was lazy enough to create a plsql. Finally, here you go with LUHN!
–=============================================================================
–
– table indicates card type, prefix, length and also for known cards
—————-
– CARD TYPE………………..Prefix………Length..Check digit algorithm
– MASTERCARD……………….51-55……….16……mod 10
– VISA…………………….4…………..13,16…mod 10
– AMEX…………………….34,37……….15……mod 10
– Diners Club………………300-305,36,38..14……mod 10
– Discover…………………6011………..16……mod 10
– enRoute………………….2014,2149……15……any
– JCB……………………..3…………..16……mod 10
– JCB……………………..2131,1800……15……mod 10
–===========================================================================
–
–VALIDATECREDITCARD(’371536720471006′)
–
CREATE OR REPLACE FUNCTION validatecreditcard (p_creditcardnumber IN VARCHAR2)
RETURN VARCHAR2
IS
creditcardnumber VARCHAR2 (32);
– := nosymbols (p_CreditCardNumber, LENGTH (p_CreditCardNumber));
creditcardlength NUMBER := LENGTH (p_creditcardnumber);
subtotal NUMBER := 0;
t_value NUMBER := 0;
c1 NUMBER;
c2 NUMBER;
c3 NUMBER;
c4 NUMBER;
cardtype VARCHAR2 (160) := ‘CARD’;
calculationmethod VARCHAR2 (160) := ‘UNKNOWN’;
RESULT VARCHAR2 (160);
BEGIN
creditcardnumber := LTRIM(RTRIM(p_creditcardnumber));
creditcardnumber := REPLACE(creditcardnumber, ‘-’, ”);
creditcardnumber := REPLACE(creditcardnumber, ‘.’, ”);
–IF isnumber (CreditCardNumber) = 0 THEN
c1 := TO_NUMBER (SUBSTR (creditcardnumber, 1, 1));
c2 := TO_NUMBER (SUBSTR (creditcardnumber, 1, 2));
c3 := TO_NUMBER (SUBSTR (creditcardnumber, 1, 3));
c4 := TO_NUMBER (SUBSTR (creditcardnumber, 1, 4));
IF creditcardlength = 13
THEN
IF c1 IN (4)
THEN
cardtype := ‘VISA’;
calculationmethod := ‘MOD10′;
END IF;
ELSIF creditcardlength = 14
THEN
IF c2 IN (36, 38)
THEN
cardtype := ‘DINERS CLUB’;
calculationmethod := ‘MOD10′;
ELSIF c3 IN (300, 301, 302, 303, 304, 305)
THEN
cardtype := ‘DINERS CLUB’;
calculationmethod := ‘MOD10′;
END IF;
ELSIF creditcardlength = 15
THEN
IF c2 IN (34, 37)
THEN
cardtype := ‘AMEX’;
calculationmethod := ‘MOD10′;
ELSIF c4 IN (2014, 2149)
THEN
cardtype := ‘enROUTE’;
calculationmethod := ‘ANY’;
ELSIF c4 IN (2131, 1800)
THEN
cardtype := ‘JBC’;
calculationmethod := ‘MOD10′;
END IF;
ELSIF creditcardlength = 16
THEN
IF c1 IN (4)
THEN
cardtype := ‘VISA’;
calculationmethod := ‘MOD10′;
ELSIF c1 IN (3)
THEN
cardtype := ‘JBC’;
calculationmethod := ‘MOD10′;
ELSIF c2 IN (51, 52, 53, 54, 55)
THEN
cardtype := ‘MASTERCARD’;
calculationmethod := ‘MOD10′;
ELSIF c4 IN (6011)
THEN
cardtype := ‘DISCOVER’;
calculationmethod := ‘MOD10′;
END IF;
END IF;
IF calculationmethod = ‘MOD10′
THEN
FOR i IN REVERSE 1 .. LENGTH (creditcardnumber)
LOOP
IF cardtype = ‘AMEX’
THEN
IF (TO_NUMBER (SUBSTR (TO_CHAR (i), LENGTH (i), 1)) NOT IN (1, 3, 5, 7, 9))
THEN
t_value := SUBSTR (creditcardnumber, i, 1) * 2;
subtotal := subtotal + SUBSTR (t_value, 1, 1);
subtotal := subtotal + NVL (SUBSTR (t_value, 2, 1), 0);
ELSE
subtotal := subtotal + SUBSTR (creditcardnumber, i, 1);
END IF;
ELSE
IF (TO_NUMBER (SUBSTR (TO_CHAR (i), LENGTH (i), 1)) IN (1, 3, 5, 7, 9))
THEN
t_value := SUBSTR (creditcardnumber, i, 1) * 2;
subtotal := subtotal + SUBSTR (t_value, 1, 1);
subtotal := subtotal + NVL (SUBSTR (t_value, 2, 1), 0);
ELSE
subtotal := subtotal + SUBSTR (creditcardnumber, i, 1);
END IF;
END IF;
END LOOP;
IF MOD (subtotal, 10) = 0
THEN
RESULT := ‘VALID’;
ELSE
RESULT := ‘INVALID’;
END IF;
ELSIF calculationmethod = ‘ANY’
THEN
RESULT := ‘VALID’;
ELSE
RESULT := ‘UNKNOWN’;
END IF;
RESULT := RESULT ” ‘ ‘ ” cardtype;
RETURN (RESULT);
END;
/
Thanks for the article, it was a good read indeed
Can you provide more information on this?