Oracle:ORA-01722:无效的号码 [英] Oracle: ORA-01722: invalid number
问题描述
我有一个查询，在sqlplus中运行时效果很好:
I have a query which works nice when I run it inside sqlplus:
SQL> SELECT T_0.ID AS ATTR_1_, T_0_0.ID AS ATTR_2_,
CASE WHEN ( T_0.ID=1 AND ( T_0_0.ID=3 OR T_0_1.ID='val_1') )
THEN 'val_1' ELSE 'val_2' END AS TXT, T_0_1.ID,
CASE WHEN T_0.ID='boo' THEN 'boo' END AS EXTRA_FIELD
FROM TEST_TABLE T_0
INNER JOIN TEST_TABLE_2 T_0_0 ON ( T_0_0.ATTR=T_0.ID )
INNER JOIN TEST_TABLE_3 T_0_1 ON ( T_0_1.ID = T_0_0.ID )
WHERE ( ( T_0.ID=1 AND T_0_0.ID=3 )
OR T_0_1.ID=2 OR T_0_0.TXT='val_2');
no rows selected
尽管它什么也不返回，它仍然可以工作并且不会导致错误.但是，当我在Python中使用绑定执行相同的操作时，会收到以下错误消息:
Although, it returns nothing, it still works and does not result in error. However, when I do the same thing in Python, using bindings, I get this error message:
cx_Oracle.DatabaseError: ORA-01722: invalid number
在执行cursor.execute之前，这是我的查询在Python中的外观:
This is how my query looks in Python, before I do cursor.execute:
SELECT T_0.ID AS ATTR_1_, T_0_0.ID AS ATTR_2_,
CASE WHEN ( T_0.ID=:TXT_ AND ( T_0_0.ID=:TXT__ OR T_0_1.ID=:TXT___ ) )
THEN :TXT___ ELSE :TXT____ END AS TXT, T_0_1.ID,
CASE WHEN T_0.ID=:EXTRA_FIELD THEN :EXTRA_FIELD END AS EXTRA_FIELD
FROM TEST_TABLE T_0
INNER JOIN TEST_TABLE_2 T_0_0 ON ( T_0_0.ATTR=T_0.ID )
INNER JOIN TEST_TABLE_3 T_0_1 ON ( T_0_1.ID = T_0_0.ID )
WHERE ( ( T_0.ID=:ID AND T_0_0.ID=:ID_ )
OR T_0_1.ID=:ID__ OR T_0_0.TXT=:TXT )
查询只是一个双引号"SELECT ..."的字符串.这就是带有绑定变量的字典的样子:
The query is just a string double-quoted "SELECT ..." . And this is how the dictionary with binding variables looks like:
OrderedDict([('TXT_', 1), ('TXT__', 3), ('TXT___', 'val_1'),
('TXT____', 'val_2'), ('EXTRA_FIELD', 'boo'), ('ID', 1),
('ID_', 3), ('ID__', 2), ('TXT', 'val_2')])
因此，如您所见，我有一本完美的字典-数字值只是不带引号的数字，字符串值只是带单引号的字符串.我知道，您将询问有关表的架构.因此，这里是:
So, as you can see I have a perfect dictionary - number values are just numbers without quotes, string values are just strings with single quotes. I know, you will ask about the schema of the tables. So, here its is:
SQL> SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS WHERE
TABLE_NAME = 'TEST_TABLE';
COLUMN_NAME
------------------------------
DATA_TYPE
------------------------------
ID
NUMBER
SQL> SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS WHERE
TABLE_NAME = 'TEST_TABLE_2';
COLUMN_NAME
------------------------------
DATA_TYPE
------------------------------
ATTR
NUMBER
ID
NUMBER
TXT
VARCHAR2
SQL> SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'TEST_TABLE_3';
COLUMN_NAME
------------------------------
DATA_TYPE
------------------------------
ID
NUMBER
因此，似乎相同的查询在控制台中效果很好，但在使用Python时却无法正常工作.为什么呢?
So, it seems like one and the same query works good in the console, but does not work when using Python. Why is that?
编辑
这是一个证明-两个控制台窗口的屏幕.在第一个控制台中，我在sqlplus中运行查询，在第二个控制台中，我输出sql查询和用于绑定变量的字典:
And here is a proof - a screen of two console windows. In the first console I run the query in sqlplus, in the second console I print sql query and the dictionary, which is used for binding variables:
编辑
哦，这更有趣.我能够在Oracle Shell中重现此错误，它看起来像Oracle 11c错误.所以，看看这个:
Oh, it's even more interesting. I was able to reproduce this error in Oracle shell and it looks like Oracle 11c bug. So, look at this:
请注意ID字段具有NUMBER类型的事实.然后注意以下两个屏幕:
Please, pay attention to the fact that ID field has a NUMBER type. And then pay attention to these two screens:
在上面的屏幕中，您可以看到一切正常.但是，如果我们通过在WHERE部分中添加OR T_0_1.ID=2对其稍作更改，则它将中断:
In the screen above you can see that everything is ok. However, if we slightly change it by adding OR T_0_1.ID=2 to the WHERE part, then it breaks:
因此，即使在Oracle Shell中，此问题也可以重现.您可以使用我上面提供的模式来做到这一点.
So, this problem is reproducible even in Oracle shell. You can do it, using the schema I provided above.
编辑
我更新了问题的主题，因为它与Python无关. Oracle本身的整个问题.
I updated the topic of my question, because it has nothing to do with Python. The whole problem with Oracle itself.
编辑
顺便说一句.我的最后评论与我调查的开始并不矛盾.关键是，如果我在TEST_TABLE_3中有一些数据，那么查询将中断.如果我删除数据，则可以开始工作.这是一个很大的证明:
BTW. My last comment does not contradict to the beginning part of my investigation. The thing is, if I have some data in TEST_TABLE_3, then the query breaks. And if I delete data, then is starts working. Here is a big proof:
数据如何影响查询的正确性?
How can data affect correctness of the query??
推荐答案
在您的最后一个屏幕上，您所拥有的语句的最后一行下方
On your last screen just below the last line of the statement you have
CASE WHEN ( T_O.ID=1 AND ( T_0_0.ID=3 OR T_0_1.ID='VAL_1') )
有一个星号(现在有帮助，但有时可能会导致错误的方向)，显示遇到的问题的位置
there's an asterisk (now it helps, but sometimes it could lead in the wrong direction) showing the place of the encountered issue
T_0_1.ID='VAL_1'
表ID列中的
是数字类型. 'VAL_1'-是Varchar.
in your table ID column is of Number type. 'VAL_1' - is Varchar.
比较规则规定:
在将字符值与数字值进行比较时，Oracle将字符数据转换为数字值.
When comparing a character value with a numeric value, Oracle converts the character data to a numeric value.
请参阅( https://docs.oracle.com/database/121/SQLRF/sql_elements002.htm#SQLRF00214 )
当oracle遇到此问题时，它将尝试将字符串转换为数字-并收到错误消息
when oracle encounters this it tries to cast your string to number - and you get the error
数据如何影响查询的正确性?
How can data affect correctness of the query??
当表中没有数据时-表中没有返回记录，因此无需检查列的值是否相等-此比较不会执行且不会显示错误
When there's no data in the table - there's no record returned from the table, hence there's no need the check the value of the column for equality - this comparison is not executed and no error shown
这篇关于Oracle:ORA-01722:无效的号码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
