Select string as number on Oracle

Question

I found this odd behavior and I'm breaking my brains with this... anyone has any ideas?

Oracle 10g: I have two different tables, both have this column named "TESTCOL" as Varchar2(10), not nullable.

If I perform this query on table1, i get the proper results:

select * from table1 where TESTCOL = 1234;

Note that I'm specifically not placing '1234'... it's not a typo, that's a dynamic generated query and I will try not to change it (at least not in the near future).

But, if I run the same query, on table2, I get this error message:

ORA-01722: Invalid number

Both queries are run on the same session, same database.

I've been joining these two tables by that column and the join works ok, the only problem shows whenever I try to use that condition.

Any ideas on what could be different from one table to the other?

Thanks in advance.

Answer 1

If TESTCOL contains non-numbers, then Oracle might run into problems when converting TESTCOL entries to numbers. Because, what it does internally, is this:

select * from table1 where TO_NUMBER(TESTCOL) = 1234;

If you're so sure that 1234 cannot be expressed as a VARCHAR literal, then try this instead, in order to compare varchar values, rather than numeric ones:

select * from table1 where TESTCOL = TO_CHAR(1234);

Answer 2

Well obvious TABLE2.TESTCOL contains values which are not numbers. Comparing a string to a numeric literal generates an implicit conversion. So any value in TESTCOL hich cannot be cast to a number will hurl ORA-1722.

It doesn't hit you where you compare the two tables because you are comparing strings.

So you have a couple of options, neiher of which you will like. The most obvious answer is to clean the data so TABLE2 hdoesn't contain non-numerics. Ideally you should combine this with changing the column to a numeric data type. Otherwise you can alter the generator so it produces code you can run against a shonky data model. In this case that means wrapping literals in quote marks if the mapped column has a character data type.

Answer 3

The following should work. Just replace the "your where".

select * 
from table1 
where (select TO_NUMBER(TESTCOL) 
       from table2 
       where "your where") = 1234;

Answer 4

You are hitting the perils of implicit typecasting here.

With the expression testcol = 1234 you state that you want to treat testcol as a numeric column, so Oracle tries to convert all values in that column to a number.

The ORA-01722 occurs because apparently at least one value in that column is not a number.

Even though you claim that this is "not a typo" it indeed is one. It's a syntactical error.

You will have to declare your parameter as a string literal using single quotes: where testcol = '1234'

Creating a correct condition is the only solution to your problem.