NVARCHAR2 datatype conversion problems

We have several custom databases defined with VARCHAR2 text fields. We interface with an ERP system that is changing to NVARCHAR2 text fields. There are many applications that retrieve data from the ERP system and join to a custom database to get the complete picture.

If we do nothing, all our queries will have performance problems. You cannot join a varchar2 with nvarchar2 without wrapping it in a function such as

               Where to_nchar(t1.orderno) = t2.orderno

Using the above where clause, we would then need to create a function based index on t1.orderno.

There are so many applications and SQL to change it does not seem feasible to rewrite all the code. Or should we consider converting all our custom applications to use NVARCHAR2 fields? In that way we have the same character set in all databases. Is there a risk doing mass conversions from VARCHAR2 to NVARCHAR2?

We are hoping to get some direction on the best way to tackle this and avoid any conversion problems.

Thanks,

Vinay Narisetty

--
http://www.freelists.org/webpage/oracle-l