http://asktom.oracle.com/tkyte/hexdec

Base Conversion Routines

This set of routines is useful to convert between various 'bases' in Oracle. Once you install these functions, you will be able to perform operations such as:

SQL> select to_bin( 123 ) bin, to_hex( 123 ) hex, to_oct( 123 ) oct from dual
2  /

BIN             HEX             OCT
--------------- --------------- ---------------
1111011         7B              173

SQL>
SQL> select to_dec( '1111011', 2 ) base2, to_dec( '7B' ) base16,
2         to_dec('173',8) base8
3    from dual
4  /

BASE2     BASE16      BASE8
---------- ---------- ----------
  123        123        123

Of course, with current releases - you would never user TO_HEX since TO_CHAR already does this (faster). Also TO_NUMBER would be used to convert HEX to decimal these days as well

ops$tkyte%ORA10GR2> select to_char( '1234', 'XXXX' ) from dual;

TO_CH
-----
4D2

Here is the original code:

create or replace function to_base( p_dec in number, p_base in number )
return varchar2
is
 l_str varchar2(255) default NULL;
 l_num number default p_dec;
 l_hex varchar2(16) default '0123456789ABCDEF';
begin
 if ( p_dec is null or p_base is null )
 then
  return null;
 end if;
 if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then
  raise PROGRAM_ERROR;
 end if;
 loop
  l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
  l_num := trunc( l_num/p_base );
  exit when ( l_num = 0 );
 end loop;
 return l_str;
end to_base;
/


create or replace function to_dec
( p_str in varchar2,
 p_from_base in number default 16 ) return number
is
 l_num   number default 0;
 l_hex   varchar2(16) default '0123456789ABCDEF';
begin
 if ( p_str is null or p_from_base is null )
 then
  return null;
 end if;
 for i in 1 .. length(p_str) loop
  l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1;
 end loop;
 return l_num;
end to_dec;
/
show errors

create or replace function to_hex( p_dec in number ) return varchar2
is
begin
 return to_base( p_dec, 16 );
end to_hex;
/
create or replace function to_bin( p_dec in number ) return varchar2
is
begin
 return to_base( p_dec, 2 );
end to_bin;
/
create or replace function to_oct( p_dec in number ) return varchar2
is
begin
 return to_base( p_dec, 8 );
end to_oct;
/