create or replace package body search_CC as G_DEBUG constant boolean := false; G_CC_CARD_FOUND constant varchar2 (20) := 'CC'; g_charreturn constant varchar2 (2) := ' '; function getCCfromList (p_list OWA_UTIL.ident_arr ,p_start pls_integer default 1) return varchar2 is v_cc varchar2 (32000); v_return varchar2 (32000); v_result varchar2 (32000); begin if p_start < p_list.COUNT then --- Count of tockens for i in p_start .. p_list.COUNT loop DBMS_OUTPUT.put_line ( 'GPI Tools : getCCfromList => get tocken (' || TO_CHAR (i) || ') => ' || p_list (i)); v_cc := v_cc || p_list (i); if LENGTH (v_cc) > 13 then DBMS_OUTPUT.put_line ( 'GPI Tools : analyseCCText =>check String for CC ' || v_cc); if G_DEBUG then v_return := showCreditCardType (v_cc) || ' for ' || v_cc || ' '; else v_result := showCreditCardType (v_cc); if v_result = G_CC_CARD_FOUND then v_return := v_result || ' for ' || v_cc || ' '; end if; end if; exit; end if; end loop; v_return := v_return || '' || getCCfromList (p_list => p_list, p_start => p_start + 1); else return ''; end if; return v_return; end getCCfromList; /* Read textstring and search for valid creditcards */ function analyseCCText (p_text varchar2) return varchar2 is v_textstring varchar2 (32000) := p_text; v_token OWA_UTIL.ident_arr; v_tokennum OWA_UTIL.num_arr; v_tokenlen integer; v_count pls_integer := 1; v_return varchar2 (32000) := 'NOT ANALYSED'; v_length integer := 0; begin -- Remove text from the string v_textstring := REPLACE (v_textstring, g_charreturn, ' '); v_textstring := TRANSLATE (UPPER (v_textstring) ,'ABCDEFGHIJKLMNOPQRSTUVWÄÜÖXYZÀÌ' ,'X'); v_textstring := REPLACE (v_textstring, 'X', ''); DBMS_OUTPUT.put_line ( 'GPI Tools : analyseCCText => normalized string 1 ' || v_textstring); -- remove spezieal signs v_textstring := TRANSLATE (v_textstring ,'²³{[]}\~|!"§$%&/()=?`*''#+´ß@€^°<>„“…¿' ,'X'); v_textstring := REPLACE (v_textstring, 'X', ''); DBMS_OUTPUT.put_line ( 'GPI Tools : analyseCCText => normalized string 2 ' || v_textstring); -- use this to seperate numbers strings v_textstring := REPLACE (v_textstring, '.', ','); v_textstring := REPLACE (v_textstring, '-', ','); v_textstring := REPLACE (v_textstring, ':', ','); v_textstring := REPLACE (v_textstring, '_', ','); v_textstring := REPLACE (v_textstring, ';', ','); DBMS_OUTPUT.put_line ( 'GPI Tools : analyseCCText => normalized string 3 ' || v_textstring); v_textstring := RTRIM (LTRIM (v_textstring)); -- three times space v_textstring := REPLACE (v_textstring, ' ', ' '); -- two times space v_textstring := REPLACE (v_textstring, ' ', ' '); -- use , as sperator v_textstring := REPLACE (v_textstring, ' ', ','); v_textstring := REPLACE (v_textstring, ',,,,,', ','); v_textstring := REPLACE (v_textstring, ',,,', ','); v_textstring := REPLACE (v_textstring, ',,', ','); v_textstring := RTRIM (LTRIM (v_textstring, ','), ','); -- if only on number exists v_textstring := v_textstring || ','; DBMS_OUTPUT.put_line ( 'GPI Tools : analyseCCText => normalized string 4 ' || v_textstring); -- tokeniser all elements or the text, Sperator ' ' OWA_UTIL.comma_to_ident_arr (v_textstring ,v_token ,v_tokennum ,v_tokenlen); -- generate a moving window to collect the strings together v_return := getCCfromList (p_list => v_token, p_start => 1); return v_return; exception when others then return v_textstring; end analyseCCText; /* identify a number as a creditcard number Returns the type of a creditcard number check if the number is a valid (Luhn Number check) Returns: The type of Creditcard or NONE if not valid */ function showCreditCardType (p_number varchar2) return varchar2 is v_cardnumber number; v_cardstring varchar2 (200); v_cardtype varchar2 (200) := 'UNKOWN'; v_return varchar2 (200) := 'UNKOWN'; v_lenght pls_integer; v_d1 pls_integer; v_d2 pls_integer; v_d3 pls_integer; v_d4 pls_integer; begin -- normalize number v_cardstring := RTRIM (LTRIM (p_number)); v_cardstring := TRANSLATE ( UPPER (v_cardstring) ,'.-,;:_#*+@ABCDEFGHIJKLMNOPQRSTUVW!"§$%&/()=²³{[]}?ßÄÜÖ' ,'X'); v_cardstring := REPLACE (v_cardstring, 'X', ''); -- check on valid number begin v_cardnumber := TO_NUMBER (v_cardstring); exception when VALUE_ERROR then return 'VALUE ERROR'; end; --check length v_lenght := LENGTH (v_cardstring); if v_lenght between 13 and 16 then v_d1 := TO_NUMBER (SUBSTR (v_cardstring, 1, 1)); v_d2 := TO_NUMBER (SUBSTR (v_cardstring, 1, 2)); v_d3 := TO_NUMBER (SUBSTR (v_cardstring, 1, 3)); v_d4 := TO_NUMBER (SUBSTR (v_cardstring, 1, 4)); --- 13 if v_lenght = 13 then if v_d1 in (4) then v_cardtype := 'VISA'; end if; end if; --14 if v_lenght = 14 then if v_d2 in (36, 38) then v_cardtype := 'DINERS CLUB'; elsif v_d3 in (300, 301, 302, 303, 304, 305) then v_cardtype := 'DINERS CLUB'; end if; end if; --15 if v_lenght = 15 then if v_d2 in (34, 37) then v_cardtype := 'AMEX'; elsif v_d4 in (2014, 2149) then v_cardtype := 'enROUTE'; elsif v_d4 in (2131, 1800) then v_cardtype := 'JBC'; end if; end if; --16 if v_lenght = 16 then if v_d1 in (4) then v_cardtype := 'VISA'; elsif v_d1 in (3) then v_cardtype := 'JBC'; elsif v_d2 in (51, 52, 53, 54, 55) then v_cardtype := 'MASTERCARD'; elsif v_d4 in (6011) then v_cardtype := 'DISCOVER'; end if; end if; -- Check on Lun CC DBMS_OUTPUT.put_line ( 'GPI Tools : showCreditCardType => test num ' || TO_CHAR (v_cardnumber) || ' and type ' || v_cardtype); if checkLuhnMod10 (v_cardnumber) = 0 then if G_DEBUG then v_return := 'VALID CC NUM TYPE ' || v_cardtype; else if v_cardtype != 'UNKOWN' then v_return := G_CC_CARD_FOUND; else v_return := 'NO CC'; end if; end if; else if G_DEBUG then v_return := 'CC NUM Error TYPE ' || v_cardtype; else v_return := '-'; end if; end if; else v_cardtype := 'NO CC NUM'; end if; return v_return; end showCreditCardType; /* create arry from a number */ function getNumArray (p_number number) return num_arry is v_numstring varchar2 (32) := TO_CHAR (p_number); v_nums num_arry; v_pos pls_integer; begin for i in 1 .. LENGTH (v_numstring) loop v_nums (i) := TO_NUMBER (SUBSTR (v_numstring, i, 1)); DBMS_OUTPUT.put_line ( 'GPI Tools : getNumArray => get num ' || TO_CHAR (v_nums (i))); end loop; return v_nums; end; /* check if the number string can be check with the LuhnMod10 alogrithmus 1 Sucess - is a Luh10 Number -1 Invalid */ function checkLuhnMod10 (p_number number) return integer is v_nums num_arry; v_pos pls_integer := 1; v_return integer := 0; v_total num_arry; begin -- create number array from a number v_nums := getNumArray (p_number); for i in reverse 1 .. v_nums.COUNT loop if MOD (v_pos, 2) = 0 then v_total (i) := (v_nums (i) * 2); else v_total (i) := (v_nums (i)); end if; v_pos := v_pos + 1; end loop; for i in 1 .. v_total.COUNT loop if v_total (i) > 9 then v_nums := getNumArray (v_total (i)); for x in 1 .. v_nums.COUNT loop v_return := v_return + v_nums (x); end loop; else v_return := v_return + v_total (i); end if; end loop; return MOD (v_return, 10); end; end; /