Oracle sql 错误 ora-01722 invalid number ora-02063 前一行来自
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33226193/
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
Oracle sql error ora-01722 invalid number ora-02063 preceding line from
提问by sayan
I ran a sql query in oracle sql developer and it works just fine but when I use a wrapper, in this case adodb in excel vba, to execute the same query I get an error. I suspect adodb is using a different version of oracle and the optimization is done differently.
我在 oracle sql developer 中运行了一个 sql 查询，它工作得很好，但是当我使用包装器时，在这种情况下，excel vba 中的 adodb，执行相同的查询时出现错误。我怀疑 adodb 使用的是不同版本的 oracle，并且优化的方式不同。
The query I ran is:
select (LATITUDE) from db where sin(LATITUDE) <= 45.0;
我运行的查询是：
select (LATITUDE) from db where sin(LATITUDE) <= 45.0;
I get the error:
我收到错误：
ORA-01722: invalid number
ORA-01722: 无效号码
ORA-02063: preceding line from db
ORA-02063: 来自 db 的前一行
If I change sin(LATITUDE) <= 45.0in query to:
如果我将sin(LATITUDE) <= 45.0查询更改为：
LATITUDE <= 45.0(it doesn't work)
LATITUDE <= 45.0（它不起作用）
LATITUDE <= '45.0'(it works)
LATITUDE <= '45.0'（有用）
sin(LATITUDE) <= '45.0'(it doesn't work)
sin(LATITUDE) <= '45.0'（它不起作用）
to_number(sin(LATITUDE)) <= 45.0(it doesn't work)
to_number(sin(LATITUDE)) <= 45.0（它不起作用）
sin(to_number(LATITUDE)) <= '45.0'(it doesn't work)
sin(to_number(LATITUDE)) <= '45.0'（它不起作用）
I also tried casting LATITUDE to a float and that doesn't work either. The only instance where it runs is without the SIN and with the single quotes around the floating value on the right of the condition.
我还尝试将 LATITUDE 转换为浮点数，但这也不起作用。它运行的唯一实例是没有 SIN 并且在条件右侧的浮动值周围有单引号。
I am at a loss as to why I can't get the query to run without any errors even though it runs just fine in Oracle SQL Developer. Any help would be appreciated.
我不知道为什么我不能让查询在没有任何错误的情况下运行，即使它在 Oracle SQL Developer 中运行得很好。任何帮助，将不胜感激。
回答by Gordon Linoff
So, it appears that LATITUDEis stored as a number and some of the values are not numeric.
因此，它似乎LATITUDE存储为数字，并且某些值不是数字。
Perhaps a query like this will help you figure out the problem:
也许这样的查询会帮助您找出问题所在：
select latitude
from db
where translate(latitude, '-0123456789.a', 'a') is not null;
This is not a perfect test, but it often works.
这不是一个完美的测试，但它通常有效。
EDIT:
编辑：
A better method uses regular expressions:
更好的方法是使用正则表达式：
select latitude
from db
where not regexp_like(latitutde, '^-?[[:digit:]]*[.]?[[:digit:]]*$')
回答by Alex Poole
Following investigation in comments, this seems to be a difference in the NLS set-up between the direct SQL client and the ADODB connection. Because you are storing a number as a string (which is always a bad idea) and the number has a decimal separator, the different NLS_NUMERIC_CHARACTERS settings are causing an error in one place but not the other.
根据评论中的调查，这似乎是直接 SQL 客户端和 ADODB 连接之间 NLS 设置的差异。因为您将数字存储为字符串（这总是一个坏主意）并且该数字有一个小数分隔符，所以不同的 NLS_NUMERIC_CHARACTERS 设置会在一个地方而不是另一个地方导致错误。
Oracle uses that NLS setting when you an implicit conversion from a string to a number, or if you have an explicit to_number()call without a format mask. So in any environment one of these statements will work and the other will get an ORA-01722 error:
当您进行从字符串到数字的隐式转换，或者您有一个to_number()没有格式掩码的显式调用时，Oracle 会使用该 NLS 设置。因此，在任何环境中，这些语句之一将起作用，而另一个将收到 ORA-01722 错误：
select to_number('0,1') from dual;
select to_number('0.1') from dual;
Which one fails depends on the NLS setting. You can use an explicit format mask to avoid this, and the third parameter to to_number()to say how to interpret it:
哪个失败取决于 NLS 设置。您可以使用显式格式掩码来避免这种情况，并使用第三个参数to_number()来说明如何解释它：
select to_number('0,1', '99D99999', 'NLS_NUMERIC_CHARACTERS='',.''') from dual;
select to_number('0.1', '99D99999', 'NLS_NUMERIC_CHARACTERS=''.,''') from dual;
This is using the Ddecimal separator marker, which corresponds to the first character in the NLS override - notice that the period and comma switch between those two statements - and that NLS override has to be set to match your actual data. You also need to use an appropriate number digits before and after the decimal of course.
这是使用D小数点分隔符标记，它对应于 NLS 覆盖中的第一个字符 - 请注意这两个语句之间的句点和逗号切换 - 并且必须设置 NLS 覆盖以匹配您的实际数据。当然，您还需要在小数点前后使用适当的数字。
On the assumption you're dealing with decimal degreesyou should be able to use:
假设您正在处理十进制度数，您应该能够使用：
select to_number(latitude, '999D99999999', 'NLS_NUMERIC_CHARACTERS=''.,''')
from irtt_meter_mv@cmomprd;
You only really need to two nines before the decimal for latitude, but you'll need three for longitude, so you might as well be consistent.
你只需要小数点前两个 9 表示纬度，但你需要三个表示经度，所以你最好保持一致。
But you also need to do that explicit conversion everywhere you reference it, so for example:
但是您还需要在引用它的任何地方进行显式转换，例如：
select to_number(latitude, '999D99999999', 'NLS_NUMERIC_CHARACTERS=''.,''')
from irtt_meter_mv@cmomprd;
where abs(to_number(latitude, '999D99999999', 'NLS_NUMERIC_CHARACTERS=''.,''')) < 45;
This would be avoided if you were using the correct datatype in the first place. If you can't change the column type you could consider adding a virtual column that does that same conversion for you, or if that isn't allowed a view that changes the type.
如果您首先使用正确的数据类型，则可以避免这种情况。如果您无法更改列类型，您可以考虑添加一个为您执行相同转换的虚拟列，或者如果不允许更改类型的视图。
An earlier answer gave a variation on Gordon Linoff's, but turns out to be a red herring.
较早的回答给出了戈登·林诺夫的一个变体，但结果证明是一个红鲱鱼。
