Oracle: ORA-01722: invalid number

Question

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

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

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 )

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

So, it seems like one and the same query works good in the console, but does not work when using Python. Why is that?

EDIT

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:

EDIT

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:

Please, pay attention to the fact that ID field has a NUMBER type. And then pay attention to these two screens:

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:

So, this problem is reproducible even in Oracle shell. You can do it, using the schema I provided above.

EDIT

I updated the topic of my question, because it has nothing to do with Python. The whole problem with Oracle itself.

EDIT

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??

Answer 1

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' 

in your table ID column is of Number type. 'VAL_1' - is Varchar.

As the comparison rules state:

When comparing a character value with a numeric value, Oracle converts the character data to a numeric value.

see (https://docs.oracle.com/database/121/SQLRF/sql_elements002.htm#SQLRF00214)

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