ORA-12704: Unable to convert character data

Question

I am trying to perform SET operations in Oracle across remote databases. I am using the MINUS operator.

My query looks something like this.

SELECT NAME FROM localdb MINUS SELECT NAME from remotedb@dblink

This is throwing up a ORA-12704 error. I understand this warrants some kind of conversion or a NLS Setting.

What should I try next?

Answer 1

It seams the types of NAME column in those 2 tables are different.

Make sure the NAME column in the remotedb table is exactly the same type as the NAME in localdb table. It is mandatory when you use a MINUS operator.

Answer 2

The two name columns are stored in different characters sets. This could be because of their type definitions, or it could be because the two databases are using different character sets.

You might be able to get around this by explicitly converting the field from the remote database to the character set of the local one. Try this:

SELECT NAME FROM localdb MINUS SELECT TO_CHAR(NAME) from remotedb@dblink