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,

Share


5 comentários

  1. #
    Marcio Dias
    junho 13th, 2012 at 13:20

    Roberto,

    Muito obrigado pela sua explicação sobre ORA-01722: invalid number.

    Resolveu meu problema.

    Obrigado

    Responda a esse comentário
  2. #
    Paulo dos Santos
    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.

    Responda a esse comentário
  3. #
    Silas
    janeiro 21st, 2014 at 18:29

    Resolveu o meu problema também! Muito obrigado

    Responda a esse comentário
  4. #
    Natan Pedroso
    fevereiro 12th, 2015 at 15:06

    Este comando é infalível:

    ALTER SESSION SET NLS_NUMERIC_CHARACTERS=’,.’;

    no meu caso só funcionou assim.

    Responda a esse comentário
  5. #
    Patricia Louzada
    dezembro 13th, 2017 at 13:03

    Obrigada resolveu para mim 🙂

    Responda a esse comentário

Deixe um comentário