Description
This script creates a function to convert Hexadecimal value to decimal.
Example
SQL> select hextodec('BABA11') from dual; HEXTODEC('BABA11') ------------------ 12237329
Starting from Oracle 8i, there is a new format model(‘X’) for the to_number function which can be used to accomplish the same task. For the above example, you could issue the following:
SQL> select to_number('BABA11','XXXXXX') from dual; TO_NUMBER('BABA11','XXXXXX') ---------------------------- 12237329
Execution Environment:
SQL, SQL*Plus, iSQL*Plus
Access Privileges:
No special privileges are required to run this script.
Usage:
$ sqlplus [user]/[pw]@[SCRIPTFILE]
The script
Create or replace FUNCTION hextodec (hexnum in char) RETURN number IS x number; digits number; result number := 0; current_digit char(1); current_digit_dec number; BEGIN digits := length(hexnum); for x in 1..digits loop current_digit := upper(SUBSTR(hexnum, x, 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; /