I am trying to join Table 1 from Database 1 to Table 2 from Database 2. Database 1 and Database 2 have different character sets. When I perform a join, I get the ORA-29275 error (partial multibyte character). I understand that this is due to the character sets / collations being different.
Is it possible to join Table 1 to Table 2 without changing the collation / character sets of either database? I tried cast() or substr() - that doesn't help.
Character set db1: WE8ISO8859P1
Character set db2: UTF8
Query:
SELECT * FROM EMPLOYEES@DB1 e1
LEFT JOIN EMPLOYEES@DB2 e2
ON e1.SURNAME = e2.SURNAME
Data types:
e1.SURNAME = varchar2(78bytes)
e2.SURNAME = varchar2(40bytes)