Hex To Decimal Conversation

Using this function you can convert hex data into decimal format.

Create or Replace Function hex_to_decimal(stream_in in varchar2) return number as
stream varchar2(2000) := upper(stream_in);
begin
if not regexp_like(stream,'^[0-9A-F]*$') then
raise_application_error(450,'Not a hexadecimal expression');
end if;
while stream like '0%' and length(stream) > 1
loop
stream := substr(stream,2);
end loop;
if length(stream) > 44 then
raise_application_error(-5500,'Number exceeds the limit (length > 44).');
end if;
declare
val number := 0;
len number(2);
multiplier number := 1;
num number;
chr varchar2(1);
begin
len := length(stream);
while len >= 1
loop
num := case substr(stream,len,1)
when 'A' then 10
when 'B' then 11
when 'C' then 12
when 'D' then 13
when 'E' then 14
when 'F' then 15
else to_number(substr(stream,len,1))
end;
val := val + multiplier * num;
multiplier := multiplier * 16;
len := len - 1;
end loop;
if len = 44 and regexp_like(stream,'^[89A-F].*$')
then
val := val - power(2,22*8) + 1;
end if;
return val;
end;
end;