ORA-01722: invalid number Error

Question

I am facing titled error while execution of below query.

select to_number('FROM1', 'xxxxxxx') from dual;

Error ORA-01722: invalid number

FROM1 values can be:

F1F65
F20B5
F204D

Please suggest solution. I need to convert these values to a number. For example, F1F65 will be converted to 991077 using below query.

select to_number('F1F65', 'xxxxxxx') from dual;

Answer 1

You should use

select to_number(FROM1, 'xxxxxxx') from dual;

otherwise Oracle interprets FROM1 as a string, and not as a variable.

Answer 2

select to_number(hex_value,'xxxxxxx')  
  from hex;  

SQL Fiddle Demo