NLS_NUMERIC_CHARACTERS 十进制设置

2021-12-05 00:00:00 oracle11g oracle oracle-sqldeveloper

我在测试机器上设置了一个数据库，在生产机器上设置了第二个.当我跑步时:

I have one db setup in a test machine and second in production machine. When I run:

select to_number('100,12') from dual

然后它在测试机器中给出错误.然而，这个语句在生产机器上工作得很好.

Then it gives error in test machine. However, this statement works quite fine in production machine.

现在，当我检查 NLS_NUMERIC_CHARACTERS 时，我会在两台机器上看到,"(逗号).还有其他地方我应该寻找小数设置吗?

Now, when I check for NLS_NUMERIC_CHARACTERS then I see ',' (comma) in both machine. Is there anywhere else I should be looking for the decimal setting?

干杯！

推荐答案

您可以通过查询nls_session_parameters来查看您当前的会话设置:

You can see your current session settings by querying nls_session_parameters:

select value
from nls_session_parameters
where parameter = 'NLS_NUMERIC_CHARACTERS';

VALUE                                  
----------------------------------------
.,

这可能与数据库默认值不同，您可以在 nls_database_parameters 中看到.

That may differ from the database defaults, which you can see in nls_database_parameters.

在此会话中您的查询错误:

In this session your query errors:

select to_number('100,12') from dual;

Error report -
SQL Error: ORA-01722: invalid number
01722. 00000 -  "invalid number"

我可以直接使用 alter session 更改我的会话，或者通过确保我的客户端以导致设置字符串需要的方式进行配置(它可能是从操作系统或 Java 继承的)语言环境，例如):

I could alter my session, either directly with alter session or by ensuring my client is configured in a way that leads to the setting the string needs (it may be inherited from a operating system or Java locale, for example):

alter session set NLS_NUMERIC_CHARACTERS = ',.';
select to_number('100,12') from dual;

TO_NUMBER('100,12')
-------------------
             100,12

在 SQL Developer 中，您可以在 Tool->Preferences->Database->NLS 中设置您的首选值.

In SQL Developer you can set your preferred value in Tool->Preferences->Database->NLS.

但我也可以覆盖该会话设置作为查询的一部分，将可选的第三个 nlsparam 参数设置为 to_number();虽然这使得可选的第二个 fmt 参数也是必要的，因此您需要能够选择合适的格式:

But I can also override that session setting as part of the query, with the optional third nlsparam parameter to to_number(); though that makes the optional second fmt parameter necessary as well, so you'd need to be able pick a suitable format:

alter session set NLS_NUMERIC_CHARACTERS = '.,';
select to_number('100,12', '99999D99', 'NLS_NUMERIC_CHARACTERS='',.''')
from dual;

TO_NUMBER('100,12','99999D99','NLS_NUMERIC_CHARACTERS='',.''')
--------------------------------------------------------------
                                                        100.12

默认情况下，结果仍然与我的会话设置一起显示，所以小数点分隔符仍然是一个句点.

By default the result is still displayed with my session settings, so the decimal separator is still a period.

相关文章