Accessing a Function over Database Link with Different Charsets in Oracle

Question

I have a function in DatabaseA which i want to access in DatabaseB, so i created a DB link in DatabaseB and started accessing the function as

SELECT function_name@dblink ('A0484')  FROM DUAL;

Doing this resulted in the following Error

ERROR at line 1:
ORA-12703: this character set conversion is not supported
ORA-06512: at line 1

On digging i found the below info of the databases

DatabaseA:
   Version-8i
   Charset-US7ASCII

DatabaseB:
   Version-10g
   Charset-AL32UTF8

direct access to any of the table in DatabaseA from DatabaseB through the DBLink works fines, it is only the function which returns this error.

The function has a signature similar to below

CREATE OR REPLACE FUNCTION TestFunc(p_Number IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
     RETURN 'Some Data';
END;

Any ideas what could be causing the conversion error and what can be done to avoid the error since direct access to the tables seem to work fine.

Answer 1

This is a known Oracle bug - if you have access to Metalink see note 237593.1. The recommended solution is to use a UTF8 character set for the 10g database when you have pre-9i clients connecting to it (remember the 8i database is a "client" of the 10g database in this case). Alternatively there is a 8i patch that might work.