ORA-01722: invalid number

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
[sourcecode language=”sql”]select to_number(‘3.1415’, ‘9D9999’), 1/2 from dual;[/sourcecode]

Rode a query abaixo para saber as configurações da sessão atual.
[sourcecode language=”sql”]
select *
from v$nls_parameters
where parameter in
(‘NLS_LANGUAGE’, ‘NLS_CHARACTERSET’);
[/sourcecode]

Se retornar isso:
[sourcecode language=”text”]
PARAMETER VALUE
1 NLS_LANGUAGE BRAZILIAN PORTUGUESE
2 NLS_CHARACTERSET WE8ISO8859P1
[/sourcecode]

A query de exemplo vai retornar erro: ORA-01722: invalid number
[sourcecode language=”text”]
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
[/sourcecode]

Se trocarmos o ponto pela vírgula vai funcionar perfeitamente
[sourcecode language=”text”]
SQL> select to_number(‘3,1415’, ‘9D9999’), 1/2 from dual;

TO_NUMBER(‘3,1415′,’9D9999’) 1/2
—————————- ———-
3,1415 ,5
[/sourcecode]

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

[sourcecode language=”text”]
PARAMETER VALUE
1 NLS_LANGUAGE AMERICAN
2 NLS_CHARACTERSET WE8ISO8859P1
[/sourcecode]

Desse forma se usarmos o ponto vai funcionar.
[sourcecode language=”text”]
SQL> select to_number(‘3.1415’, ‘9D9999’), 1/2 from dual
2 ;

TO_NUMBER(‘3.1415′,’9D9999’) 1/2
—————————- ———-
3.1415 .5
[/sourcecode]

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
[sourcecode language=”text”]
export NLS_LANG=”BRAZILIAN PORTUGUESE_BRAZIL.WE8ISO8859P1″
export NLS_LANG=”AMERICAN_AMERICA.WE8ISO8859P1″
[/sourcecode]
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,