dblink及字符集转换问题。（UTF8->WE8ISO8859P1)

信息描述：

1.应用：

在database a中：

sql>select a.remark from a@db_link_b; db_link_b连到database b

中文出现乱码。

其中database a的数据库字符集为：AMERICAN_AMERICA.WE8ISO8859P1.

database b的数据库字符集为：

AMERICAN_AMERICA.UTF8.

日常，database b的客户端用CHINESE_CHINA.ZHS16GBK 往数据库输入汉字。

解决方案如下：

1、先在database b，user b中建view，将中文字段用utl_raw.cast_to_raw转换
CREATE OR REPLACE VIEW view_1 (
remark
) AS
SELECT Utl_Raw.cast_to_raw(remark) remark FROM b.table;

2、在database a上用如下的sql来select就可以正常显示中文了：
SELECT Convert(Convert(UTL_RAW.CAST_TO_VARCHAR2(remark),'ZHS16GBK','UTF8'),'WE8ISO8859P1') FROM b.view_1@db_link_b;

以下是oracle的完整解决方案：

The details of the Note 270774.1 are as follows:

Scope and Application

Under normal circumstances the automatic NLS conversion on a database
link is a good thing, because it means Oracle understands the data on
both sides of the database link. Therefore this note should only be in
very specific circumstances.

If either of the databases in a database link is mis-configured, then as
a short term measure it can be useful to either have no NLS conversion,
or to override the automatic conversion, on this database link.

Moving data across a database link with manual or no NLS conversion

*Normal circumstances*
Database links are used to move data between 2 databases. If the 2
databases have a different character set, then there will be automatic
conversion of data from the character set in the source database to the
character set in the target database. As long as you move (N)(VAR)CHAR
or (N)CLOB data across this is not something that can be switched off in
any way. This will always work automatically.

*Mis-configurations*
If, for whatever reason, you have data that is not stored correctly in
the database character set on the source side of the database link, the
automatic conversion on these database links can lead to problems in the
transfer of data. Here are 2 examples of situations you might find
yourself in, but this issue can occur in very many similar situations as
well:

/Example 1/
The source database stores data that is inserted from US or Western
European Windows machines. However, when this database was created the
US7ASCII character set was chosen. Because the NLS_LANG on the clients
is also set up as US7ASCII the data appears to be loaded correctly in
this database. This is of course an invalid set up, but it all looks
correct â

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16856446/viewspace-1030904/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16856446/viewspace-1030904/