This set of routines is useful to convert between various 'bases' in Oracle:

create or replace function to_base( p_dec in number, p_base in number ) return varchar2is 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 numberis 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 varchar2isbegin return to_base( p_dec, 16 );end to_hex;/create or replace function to_bin( p_dec in number ) return varchar2isbegin return to_base( p_dec, 2 );end to_bin;/create or replace function to_oct( p_dec in number ) return varchar2isbegin return to_base( p_dec, 8 );end to_oct;/

how to use them:

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

however , if you can use to_number function to convert between hex and dec

select to_number('AA','XXXX') from dual;

select to_char(10,'XXXX') from dual; /*10 is A in this case */