CODEWITHSUNDEEP
oracle-databasecharacter-encoding
haki
haki

Reputation: 9759

Charset mismatch when querying from db link

I'm querying from a 10g database using a dblink to an 8i database.

select col1, col2 ... from table@my_dblink_to_8i

8i charset is IW8ISO8859P8

10g charset is WE8MSWIN1252

the data is coming out as gibrish. I've tried all of variations I can think of 

to_char(col1)
cast(col1 as nchar(4))
cast(col1 as nvarchar2(4))
cast(col1 as char(4))
cast(col1 as varchar2(4))
convert(col1, 'WE8MSWIN1252', 'IW8ISO8859P8')
convert(convert(col1,'UTF8','IW8ISO8859P8'),'WE8MSWIN1252','UTF8')

all returning with either gibrish or 

ORA-12704: character set mismatch
ORA-02063: preceding line from OTHERDB

any suggestions ?

Is there an intermediate charset I can convert to ?

Upvotes: 2

Views: 3588

Answers (1)

Guido Leenders
Guido Leenders

Reputation: 4262

Yes, this is a known problem that sometimes occurs. I remember the first time experiencing it using a database link between two identical Oracle 7 version and then seeing it back in 9 when using Oracle 8.1.5.

It can not always be solved. Oracle development does not seem to test as intensively with non-US characters as with US characters.

The first thing you can try is to check the EXACT versions of Oracle 8i in use. Check that the server version is 8.1.7 or newer (such as 8.1.7.4). With 8.1.5 there are known problems, I think to recall that that is the first version to do AL32UTF8.

Also check the version of the SQL*Net client installation (if you are using a separate installation, I don't think so). It must be 8.1.7 or newer also.

Also check that the characters are available in BOTH character sets. They are largely identical, but not completely. I think the 8859P8 is an international without Europ-support, whereas MSWIN1252 is something of Microsoft.

Check the NLS_LANG on all nodes in between and that the database character set is correctly configured. Make sure they are correct. The interim nodes you can change to AL32UTF8. SQL*net does no character conversion but also no checks when the client and server talk the same characterset, so bugs in the characterset setup can slumber for years.

After testing those, you might want to try convert to AL32UTF instead of UTF8 (I think it was already available by 8, don't know sure, but maybe only mainstream supported on 9i).

As a last resort, do the character conversion yourself. Use a procedure to transport it binary to the caller and do the conversion on the receiving 10g database.

Or use an ETL tool like Kettle, spooling to text files as interim or alike.

I hope this answers your question. If not, please help me with some samples of the gibberish (transporting us7ascii texts, more advanced texts, and the results of out varchar2 parameters called across dblink). If yes, please let me know too. You have a intriguing question!

Upvotes: 1

Related Questions