Convert Oracle Number to Hex and Vice Verse

To convert a hexadecimal number (base 16) to a decimal number, we use to_number function and specify the format as 'XXXXX' as shown below.

SQL> select to_number('30003','XXXXXX') from dual;

TO_NUMBER('30003','XXXXXX')
---------------------------
       196611

We can verify the conversion by using the following query.The fifth position from the right represents 16 to the 4th power.

SQL> select power(16,4)*3+3 from dual;

POWER(16,4)*3+3
---------------
         196611

To convert a decimal number to a hex number, we use to_char() function.

SQL> select to_char(196611, 'XXXXXX') from dual;

TO_CHAR
-------
  30003