oracle NLS_NUMERIC_CHARACTERS 十进制设置
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24571355/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
NLS_NUMERIC_CHARACTERS setting for decimal
提问by Jaanna
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.
然后它在测试机器中出错。然而，这个语句在生产机器上工作得很好。
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_NUMERIC_CHARACTERS 时，我会在两台机器上看到“,”（逗号）。还有其他地方我应该寻找小数设置吗？
Cheers!
干杯!
回答by Alex Poole
You can see your current session settings by querying nls_session_parameters:
您可以通过查询来查看当前的会话设置nls_session_parameters：
select value
from nls_session_parameters
where parameter = 'NLS_NUMERIC_CHARACTERS';
VALUE
----------------------------------------
.,
That may differ from the database defaults, which you can see in nls_database_parameters.
这可能与数据库默认值不同，您可以在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"
I could alter my session, either directly with alter sessionor 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或者通过确保我的客户端以导致设置字符串需要的方式进行配置（例如，它可能是从操作系统或 Java 语言环境继承的）：
alter session set NLS_NUMERIC_CHARACTERS = ',.';
select to_number('100,12') from dual;
TO_NUMBER('100,12')
-------------------
100,12
In SQL Developer you can set your preferred value in Tool->Preferences->Database->NLS.
在 SQL Developer 中，您可以在 Tool->Preferences->Database->NLS 中设置您的首选值。
But I can also override that session setting as part of the query, with the optional third nlsparamparameter to to_number(); though that makes the optional second fmtparameter necessary as well, so you'd need to be able pick a suitable format:
但我也可以覆盖该会话设置作为查询的一部分，使用可选的第三个nlsparam参数to_number()；尽管这也使得可选的第二个fmt参数成为必要，因此您需要能够选择合适的格式：
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.
默认情况下，结果仍然与我的会话设置一起显示，所以小数点分隔符仍然是一个句点。
回答by Jonas
Jaanna, the sessionparameters in Oracle SQL Developerare dependent on your client computer, while the NLSparameters on PL/SQLis from server.
Jaanna，Oracle SQL Developer中的会话参数取决于您的客户端计算机，而PL/SQL上的NLS参数来自服务器。
For example the NLS_NUMERIC_CHARACTERSon client computer can be ',.' while it's '.,' on server.
例如NLS_NUMERIC_CHARACTERS，客户端计算机上的可以是 ' ,. ' 而它是 ' .,' 在服务器上。
So when you run script from PL/SQLand Oracle SQL Developerthe decimal separator can be completely different for the same script, unless you alter session with your expected NLS_NUMERIC_CHARACTERSin the script.
因此，当您从PL/SQL和Oracle SQL Developer运行脚本时，同一脚本的小数点分隔符可能完全不同，除非您NLS_NUMERIC_CHARACTERS在脚本中使用预期的会话更改会话。
One way to easily test your session parameter is to do:
轻松测试会话参数的一种方法是：
select to_number(5/2) from dual;
回答by schlebe
To know SESSION decimal separator, you can use following SQL command:
要知道 SESSION 小数点分隔符，您可以使用以下 SQL 命令：
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ', ';
select SUBSTR(value,1,1) as "SEPARATOR"
,'using NLS-PARAMETER' as "Explanation"
from nls_session_parameters
where parameter = 'NLS_NUMERIC_CHARACTERS'
UNION ALL
select SUBSTR(0.5,1,1) as "SEPARATOR"
,'using NUMBER IMPLICIT CASTING' as "Explanation"
from DUAL;
The first SELECT command find NLS Parameter defined in NLS_SESSION_PARAMETERStable. The decimal separator is the first character of the returned value.
第一个 SELECT 命令查找NLS_SESSION_PARAMETERS表中定义的 NLS 参数。小数点分隔符是返回值的第一个字符。
The second SELECT command convert IMPLICITELY the 0.5rational number into a String using (by default) NLS_NUMERIC_CHARACTERS defined at session level.
第二个 SELECT 命令0.5使用（默认情况下）在会话级别定义的 NLS_NUMERIC_CHARACTERS 将有理数隐式转换为字符串。
The both command return same value.
这两个命令返回相同的值。
I have already tested the same SQL command in PL/SQL script and this is always the same value COMMA or POINT that is displayed. Decimal Separator displayed in PL/SQL script is equal to what is displayed in SQL.
我已经在 PL/SQL 脚本中测试了相同的 SQL 命令，这始终是显示的相同值 COMMA 或 POINT。PL/SQL 脚本中显示的十进制分隔符与 SQL 中显示的相同。
To test what I say, I have used following SQL commands:
为了测试我所说的，我使用了以下 SQL 命令：
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ', ';
select 'DECIMAL-SEPARATOR on CLIENT: (' || TO_CHAR(.5,) || ')' from dual;
DECLARE
S VARCHAR2(10) := '?';
BEGIN
select .5 INTO S from dual;
DBMS_OUTPUT.PUT_LINE('DECIMAL-SEPARATOR in PL/SQL: (' || S || ')');
END;
/
The shorter command to know decimal separator is:
知道小数点分隔符的较短命令是：
SELECT .5 FROM DUAL;
That return 0,5if decimal separator is a COMMA and 0.5if decimal separator is a POINT.
这回0,5如果小数点分隔符是一个逗号和0.5如果小数点分隔符是一个点。
回答by Pramod
Best way is,
最好的办法是，
SELECT to_number(replace(:Str,',','')/100) --into num2
FROM dual;
