NVARCHAR2 versus VARCHAR2

I've been coming across more and more databases using NVARCHAR2 instead of the more usual VARCHAR2 data type and found some issues.

First, I had issues inserting data from an NVARCHAR2 column into a table where the corresponding column in the other table has a data type of VARCHAR2.

Second, I had an issue joining tables together where the same column was defined with a different data type, one being NVARCHAR2 and one being VARCHAR2.

Here is the way I solved these issues:

Inserting NVARCHAR2 into VARCHAR2
If you try inserting data from an NVARCHAR2 column into a table where the corresponding column in the receiving table is defined as VARCHAR2 you will get a character set mismatch error. You will need to use the TRANSLATE USING command, like this:

• TRANSLATE(nvarchar2 USING TABLE1.NVARCHAR2_COLUMN) INTO VARCHAR2_COLUMN

Joining NVARCHAR2 to VARCHAR2
If you try creating a join using

WHERE TABLE1.NVARCHAR2_COLUMN = TABLE2.VARCHAR2_COLUMN

you will get an error. You will need to do this:

WHERE TO_CHAR(TABLE1.NVARCHAR2_COLUMN) = TABLE2.VARCHAR_COLUMN