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.