ORA-01722: invalid number
13/março/2011 - Oracle
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,
junho 13th, 2012 at 13:20
Roberto,
Muito obrigado pela sua explicação sobre ORA-01722: invalid number.
Resolveu meu problema.
Obrigado
maio 6th, 2013 at 23:30
Valeu, apareceu este erro depois de eu instalar o cliente ORACLE 11G no windows 2003 server, seguí a dica e criei a variável NLS_LANG nas propriedades do sistema, aba avançado e nas variáveis de ambiente. coloquei o valor “BRAZILIAN PORTUGUESE_BRAZIL.WE8ISO8859P1” resolveu. Obrigado.
janeiro 21st, 2014 at 18:29
Resolveu o meu problema também! Muito obrigado
fevereiro 12th, 2015 at 15:06
Este comando é infalível:
ALTER SESSION SET NLS_NUMERIC_CHARACTERS=’,.’;
no meu caso só funcionou assim.
dezembro 13th, 2017 at 13:03
Obrigada resolveu para mim 🙂