oracle ORA-01722: 无效号码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1432163/
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
ORA-01722: invalid number
提问by Jimmy Stenke
When i execute the below SQL command using single quotes to enter a number, i got an error if remove the single quotes,it is successfully updated. knowing that the type of the field HEIGHT is NUMBER.
当我使用单引号执行以下 SQL 命令以输入数字时，如果删除单引号，则会出现错误，它已成功更新。知道字段 HEIGHT 的类型是 NUMBER。
The strange thing is that i tried to use the same sql statement with single quotes on different machines, some machines execute it successfully, others do not.(same oracle version,same table structure...)
奇怪的是，我试图在不同的机器上使用相同的带有单引号的sql语句，有些机器执行成功，有些则没有。（相同的oracle版本，相同的表结构......）
Any explanation please
请任何解释
SQL> UPDATE TBL_DEVICE_INFO SET HEIGHT='14.5' WHERE ID='6ujbfI';
UPDATE TBL_DEVICE_INFO SET HEIGHT='14.5' WHERE ID='6ujbfI'
*
ERREUR à la ligne 1 :
ORA-01722: invalid number
SQL> UPDATE TBL_DEVICE_INFO SET HEIGHT=14.5 WHERE ID='6ujbfI';
1 row updated.
回答by Jimmy Stenke
This is most likely a locale problem.
这很可能是语言环境问题。
That is, some machines have the decimal symbol "." (period), and some have "," (comma).
也就是说，有些机器有十进制符号“.”。（句号），有些带有“,”（逗号）。
You can test it by putting it like this:
你可以这样测试：
UPDATE TBL_DEVICE_INFO
SET HEIGHT = to_number('14.5', '99D9','NLS_NUMERIC_CHARACTERS = ''. ''')
WHERE ID='6ujbfI'
When the number is in single qoutes, oracle will do an implicit conversion to number using the characters set in the database.
当number为single qoutes时，oracle会使用数据库中设置的字符隐式转换为number。
You can change the default by setting the NLS_NUMERIC_CHARACTERS parameter:
您可以通过设置 NLS_NUMERIC_CHARACTERS 参数来更改默认值：
alter session set NLS_NUMERIC_CHARACTERS = '. ';
but that will also reflect to data returned by the system so make sure that it doesn't break anything in your application if you change that.
但这也将反映到系统返回的数据中，因此如果您更改它，请确保它不会破坏您的应用程序中的任何内容。
回答by Glen
Strings should be quoted using single quotes, numbers shouldn't be.
字符串应该用单引号引起来，数字不应该。
Maybe you're using a different client on the machines where the invalid syntax works?
也许您在使用无效语法的机器上使用不同的客户端？
