and take care, that your settings are correct in regard to your decimal/thousand delimiter.
select value from nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS'
Edited by: Maxim Demenko on Jan 20, 2011 8:56 AM
alter session set nls_numeric_characters='.,';
So the conversion does not fail with the data and row you think - it is failing somewhere else. And as you are showing a SQL select, quite likely that failure is with another row where that substr() does not return a string that can be converted to numeric.
SQL> select to_number('00000000.1') as N from dual; N ---------- .1 SQL>