SQL function to validate credit
card number
If you give the digits of the credit it verifies that
no body is putting junk number as soon as you leave the field in form.
You can use this to validate the number. This logic can be used in JAVASCRIPT
also. IF the number qualifies then you can dial up and check the credit
and also get the transaction number back from the cc company. It provides
a higher level of validation. The other validation can be done later also
when you have a substantial batch.
function check_digit (cc_type VARCHAR2, cc_number
VARCHAR2)
return number IS
valid
NUMBER := 0;
invalid
NUMBER := -1;
AMEX
VARCHAR2(1) := 'A';
VISA
VARCHAR2(1) := 'V';
DINERS_CLUB
VARCHAR2(1) := 'I';
MASTER_CARD
VARCHAR2(2) := 'MC';
CARTE_BLANCHE
VARCHAR2(1) := 'C';
DISCOVER
VARCHAR2(1) := 'D';
/* The following defines set up the valid low/high ranges
for the first 5 digits of the
credit card number */
VISA_LOW
NUMBER := 40000;
VISA_HIGH
NUMBER := 49999;
MC_LOW
NUMBER := 50000;
MC_HIGH
NUMBER := 59999;
DISCOVER_LOW
NUMBER := 60000;
DISCOVER_HIGH
NUMBER := 69999;
AMEX_LOW1
NUMBER := 34000;
AMEX_HIGH1
NUMBER := 34999;
AMEX_LOW2
NUMBER := 37000;
AMEX_HIGH2
NUMBER := 37999;
DC_LOW1
NUMBER := 30000;
DC_HIGH1
NUMBER := 30999;
DC_LOW2
NUMBER := 36000;
DC_HIGH2
NUMBER := 36999;
DC_LOW3
NUMBER := 38000;
DC_HIGH3
NUMBER := 38999;
CB_LOW1
NUMBER := 38900;
CB_HIGH1
NUMBER := 38999;
CB_LOW2
NUMBER := 39300;
CB_HIGH2
NUMBER := 39499;
CC
NUMBER;
first_five
VARCHAR2(5);
work_string
VARCHAR2(1);
num
NUMBER;
sum1
NUMBER;
odd
boolean;
product
NUMBER;
check_digit
NUMBER;
range_OK
boolean;
INDEX1
NUMBER;
work_rem
number;
work_quot
number;
BEGIN
first_five
:= substr(cc_number,1,5);
CC
:= to_number(first_five);
IF cc_type = VISA THEN
IF (CC between VISA_LOW AND VISA_HIGH) THEN
range_OK := TRUE;
ELSE
range_OK := FALSE;
END IF;
ELSIF cc_type = MASTER_CARD THEN
IF (CC between MC_LOW AND MC_HIGH) THEN
range_OK := TRUE;
ELSE
range_OK := FALSE;
END IF;
ELSIF cc_type = DISCOVER THEN
IF (CC between DISCOVER_LOW AND DISCOVER_HIGH) THEN
range_OK := TRUE;
ELSE
range_OK := FALSE;
END IF;
ELSIF cc_type = AMEX THEN
IF (CC between AMEX_LOW1 AND AMEX_HIGH1) or
(CC between AMEX_LOW2 AND AMEX_HIGH2) THEN
range_OK := TRUE;
ELSE
range_OK := FALSE;
END IF;
ELSIF cc_type = DINERS_CLUB THEN
IF (CC between DC_LOW1 AND DC_HIGH1) or
(CC between DC_LOW2 AND DC_HIGH2) or
(CC between DC_LOW3 AND DC_HIGH3) THEN
range_OK := TRUE;
ELSE
range_OK := FALSE;
END IF;
ELSIF cc_type = CARTE_BLANCHE THEN
IF (CC between CB_LOW1 AND CB_HIGH1) or
(CC between CB_LOW2 AND CB_HIGH2) THEN
range_OK := TRUE;
ELSE
range_OK := FALSE;
END IF;
ELSE
range_OK := TRUE;
END IF;
/* Since we know the range of the first five digts is
correct for the card,we can do the
MOD10 check digit calculation */
sum1
:=0;
odd
:= TRUE;
index1
:= length(cc_number) -1;
/* Convert the ASCII values in the cc_number array to
integer data type so we can do
calculations on them */
BEGIN
WHILE (index1 >= 1) loop
work_string
:= substr(cc_number,index1,1);
num
:= to_number(work_string);
IF odd THEN
product
:= num * 2;
IF (product
> 9) THEN
work_rem := mod(product,10);
work_quot := trunc(product/10);
sum1 := sum1 + work_quot + work_rem;
else
sum1 := sum1 + product;
END
IF;
odd
:= FALSE;
ELSE
sum1
:= sum1 + num;
odd
:= TRUE;
END IF;
INDEX1
:= INDEX1 - 1;
END loop;
/* Do the modulus calculation. If the last digit of the
sum1 is not a 0, then we need to
subtract it from the next highest multiple
of 10, else the check digit is 0 */
IF mod(sum1,10) != 0 THEN
work_quot := trunc(sum1/10);
check_digit := ((work_quot
+ 1) * 10) - sum1;
ELSE
check_digit :=
0;
end if;
work_string := substr(to_char(check_digit),1,1);
IF ( work_string = substr(cc_number,length(cc_number),1)
) AND range_OK then
RETURN valid;
ELSE
RETURN invalid;
END IF;
END;
END;
Have a Oracle Question
Do
you have an Oracle Question?
Oracle Books
Oracle
Certification, Database Administration, SQL, Application, Programming Reference
Books
Oracle Application
Oracle
Application Hints and Tips
Oracle Home
Oracle
Database, SQL, Application, Programming Tips
All the site contents are Copyright © www.erpgreat.com
and the content authors. All rights reserved.
All product names are trademarks of their respective
companies.
The site www.erpgreat.com is not affiliated with or endorsed
by any company listed at this site.
Every effort is made to ensure the content integrity.
Information used on this site is at your own risk.
The content on this site may not be reproduced
or redistributed without the express written permission of
www.erpgreat.com or the content authors.
|