CREATE OR REPLACE FUNCTION hex2dec (hexnum IN VARCHAR(30))
RETURN DECIMAL(36,0)
IS
i INTEGER;
digits INTEGER;
res DECIMAL(36,0);
current_digit CHAR(1);
current_digit_dec DECIMAL(2,0);
BEGIN
res := 0;
digits := LENGTH(hexnum);
FOR i IN 1..digits LOOP
current_digit := lower(SUBSTR(hexnum, i, 1));
IF current_digit IN ('a','b','c','d','e','f') THEN
current_digit_dec := ASCII(current_digit) - ASCII('a') + 10;
ELSE
current_digit_dec := TO_NUMBER(current_digit);
END IF;
res := (res * 16) + current_digit_dec;
END LOOP;
RETURN res;
END
/