Oracle number and varchar join

Question

I have a query that joins two tables. One table has a column that is of type varchar, and the other table has type of number. I have executed my query on 3 oracle databases, and am seeing some strange results I hope can be explained. On two of the databases something like the following works.

select a.col1, b.somecol 
from tableA a inner join tableB b on b.col2=a.col1;

In this query tableA.col1 is of type number and tableB.col2 is of type varchar. This works fine in two of the databases but not in the third. In the third I get (ORA-01722) error. In the third I need to do something like...

select a.col1, b.somecol 
from tableA a inner join tableB b on b.col2=to_char(a.col1);

This works in all the databases. The question I have is why? The above is a simplified query, and the real query is a little more complex and retrieves a lot of data, hence the first version is much faster. If I could get that to work in all environments it would be great.

Does anyone know why this may work in some oracle databases and not others without the cast on the datatype? Is there a global setting that enables such behavior?

Answer 1

One reason why implicit conversions fail is when the joining varchar column contains data which is not numeric. Oracle handles number to varchar2 joins by converting the strings (check out Gary's citation in his comment), so it actually executes this :

select a.col1, b.somecol 
from tableA a inner join tableB b on to_number(b.col2)=a.col1;

If tableB.col2 contains values which are not numeric - seems quite likely, it is a string after all - then it will hurl ORA-01722: invalid number. By explicitly casting the number column to a string you short-circuit Oracle's default behaviour.

The fact that you don't get this problem in your first two environments is a matter of luck not configuration. It could strike at any time, because it only requires one non-numeric string to break the query. So really you ought to run with the explicit conversion in all environments.

As for performance, you could build a function-based index ...

create index whatever_idx on tableA ( to_char(col1) )
/