Post date: Oct 14, 2014 8:19:49 AM
Scientific formatted numbers are convenient and sometimes you need to deal with them anyway, convenient or not.
Given a perfectly fine number, given as a string, how can we convert that to a Number type?
Note that if the value is presented as a pure number, then Oracle will manage it perfectly:
select 0.0000000000000000001e+00 as a, 0.0000000000000000001e+00 + 10e20 as b from dual;-- A B------------ ------------,0000000000000000001 1.0E+21
But if you have the value as a string, in a column or as a parameter then Oracle will fail to convert it to a number, unless you instruct Oracle of how to interpret the string.
-- Given this scientific representation of a number, convert it to a Number data type.-- Straight forward cast will failselect cast('0.0000000000000000001e+00' as number) from dual;--Error starting at line : 3 in command ---select cast('0.0000000000000000001e+00' as number) from dual--Error report ---SQL Error: ORA-01722: invalid number--01722. 00000 - "invalid number"--*Cause: The specified number was invalid.--*Action: Specify a valid number.-- Jump through the hoops to get itselect to_number('0.0000000000000000001e+00', '99999999999999999999.99999999999999999999EEEE', 'NLS_NUMERIC_CHARACTERS = ''.,''') as afrom dual;
Result:
A----------,0000000000000000001