rem -----------------------------------------------------------------------
rem Filename:   hex2dec.sql
rem Purpose:    Functions to convert Hex to Decimal and vice versa
rem -----------------------------------------------------------------------

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

CREATE OR REPLACE FUNCTION num2hex (N in number) RETURN varchar2 IS
  H  varchar2(64) :='';
  N2 integer      := N;
BEGIN
  loop
     select rawtohex(chr(N2))||H
     into   H
     from   dual;

     N2 := trunc(N2 / 256);
     exit when N2=0;
  end loop;
  return H;
END num2hex;
/
show errors

-- Examples:
select hex2dec('FF') from dual;

select num2hex(10) from dual;

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