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