Hexadecimal para Decimal

CREATE OR REPLACE FUNCTION hex2dec (hexval in char) RETURN number IS
  i                 number;
  digits            number;
  result            number := 0;
  current_digit     char(1);
  current_digit_dec number;
BEGIN
  digits := length(hexval);
  for i in 1..digits loop
     current_digit := SUBSTR(hexval, 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;
     result := (result * 16) + current_digit_dec;
  end loop;
  return result;
END hex2dec;
/

SQL> SELECT hex2dec('AFB2') FROM dual;

HEX2DEC('AFB2')
---------------
          44978