Hi all,
I am trying to build a MSSQL dblink in Oracle, the connection is fine, when selecting English character from the table with dblink its ok, but when selecting Chinese character, it becomes all "?????"
Below are the environment and configure:
Oracle version 10.2.0
Oracle Environemtn Encoding: UTF-8
Windows Server 2003
| select * from nls_database_parameters; |
|---|
PARAMETER VALUE ------------------------------ ---------------------------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET UTF8 NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM PARAMETER VALUE ------------------------------ ---------------------------------------- NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_RDBMS_VERSION 10.2.0.1.0 NLS_CSMIG_SCHEMA_VERSION 5 |
$ORACLE_HOME/hs/admin/initSQLSERVER.ora
|
|---|
HS_FDS_CONNECT_INFO = SQLSERVER
HS_AUTOREGISTER = TRUE
HS_DB_NAME = hsodbc
HS_NLS_NCHAR = UCS2
HS_FDS_TRACE_LEVEL = 0
HS_LANGUAGE=AMERICAN_AMERICA.AL16UTF16
HS_FDS_REMOTE_DB_CHARSET = ZHS16GBK
|
SQL Server 2016 64 bit
Windows Server 2016 64 bit
In MSSQL:
SELECT sqltablename FROM sqltable; |
|---|
測試測試測試測試 |
In Oracle:
SELECT sqltablename FROM sqltable@sqlserver; |
|---|
| ???????? |
SELECT name, collation_name FROM sys.columns WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM sys.objects WHERE type = 'U' AND name = 'sqltable') AND name = 'sqltablename' |
|---|
name collation_name -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- sqltablename Chinese_PRC_CI_AS (1 row(s) affected) |
| sqltablename column properties: |
|---|
data_type=nvarchar character set=UNICODE collation name=Chinese_PRC_CI_AS |
Any help would be greatly appreciated!!Thanks!