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

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?

From stackoverflow

• 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

  DCookie : I don't know why Oracle implemented a datatype that's useless without the intrinsics to deal with it. Your try generates the intuitive error: ORA-00932: inconsistent datatypes: expected NUMBER got LONG :-|

• 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.