in Oracle 9.2, how to compare LONG Type containing String text to a Column of VARCHAR2.

Question

simple q. in Oracle 9.2, how to compare LONG Type containing String text to a Column of VARCHAR2.

select * from table1 t1, table2 t2 where t1.long_value = t2.varchar2_value

how can i execute such a query the easiest way?

Answer 1

hmm, without having Oracle accesible I'm guessing your problem is with casting to the correct types.

try (''||t1.long_value) = t2.varchar2_value to force a conversion

but it depends on how you have your indexes setup. If you want to utilize the index on t1.long_value you may be better of converting t2.varchar2_value to something

Answer 2

The short answer is you can't, directly. Have a look at this for a function to convert longs to varchar2 so you can use it in a SQL statement. LONG is more trouble than it's worth, but sometimes you're stuck with it.