Olá,

Se você usa o to_number() para converter varchar2 em number provavelmente já teve esse problema ou um dia terá.
O problema aparenta ser simples quando tem uma variável varchar2 com caracteres estranhos no meio, mas e quando você tem certeza que o número esta certo. E quando to_number() funciona corretamente só quando o número é inteiro .
Essa dor de cabeça aparece quando tem o separador de decimal e/ou de milhar, que é diferente conforme a localização usada. Geralmente usamos o ponto, que é o normal para programadores. Mas o oracle pode te pegar de calças curtas.

Vamos usar a simples query abaixo para demonstrar o problema

select to_number('3.1415', '9D9999'), 1/2 from dual;

Rode a query abaixo para saber as configurações da sessão atual.

 select *
   from v$nls_parameters
  where parameter in
		('NLS_LANGUAGE', 'NLS_CHARACTERSET');

Se retornar isso:

   	PARAMETER	        VALUE
1	NLS_LANGUAGE	    BRAZILIAN PORTUGUESE
2	NLS_CHARACTERSET	WE8ISO8859P1

A query de exemplo vai retornar erro: ORA-01722: invalid number

SQL> select  to_number('3.1415', '9D9999'), 1/2 from dual;
select  to_number('3.1415', '9D9999'), 1/2 from dual
                  *
ERROR at line 1:
ORA-01722: invalid number

Se trocarmos o ponto pela vírgula vai funcionar perfeitamente

SQL>  select  to_number('3,1415', '9D9999'), 1/2 from dual;
TO_NUMBER('3,1415','9D9999')        1/2
---------------------------- ----------
                      3,1415         ,5

Para funcionar com ponto, o NLS_LANG deve ser o AMERICAN_AMERICA.WE8ISO8859P1, mostrado abaixo:

   	PARAMETER	        VALUE
1	NLS_LANGUAGE	    AMERICAN
2	NLS_CHARACTERSET	WE8ISO8859P1

Desse forma se usarmos o ponto vai funcionar.

SQL> select  to_number('3.1415', '9D9999'), 1/2 from dual
  2  ;
TO_NUMBER('3.1415','9D9999')        1/2
---------------------------- ----------
                      3.1415         .5

Com esse post demonstrei que o cliente é que manda, não sei tem como o DBA travar isso, mas pelo menos nos bancos que eu acesso tem essa característica.

Para alterar esse comportamento no linux basta usar o export para criar a variável NLS_LANG

export NLS_LANG="BRAZILIAN PORTUGUESE_BRAZIL.WE8ISO8859P1"
export NLS_LANG="AMERICAN_AMERICA.WE8ISO8859P1"

No windows vá nas propriedades do sistema, aba avançado e clique no botão variáveis de ambiente. Utilize o botão novo para criar uma nova variável, coloque no nome NLS_LANG e o valor que desejar.

Até mais,