use Perl

We had some data that was, for reasons I dare not ask, in hex instead of decimal. Surprisingly there is no Oracle SQL function to convert numbers of one base to another (that I saw - if there is, please let me know). The convert() function in Sybase can do this, but the Oracle version works differently.

Anyway I came across this:

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;
/

Works like a charm.