Home » RDBMS Server » Networking and Gateways » DB Link from SQL Server 2005 (Big5) to Oracle(UTF8) (10.2.0.4)
DB Link from SQL Server 2005 (Big5) to Oracle(UTF8) [message #447992] Thu, 18 March 2010 23:48 Go to next message
summoner
Messages: 44
Registered: March 2009
Member
Dear all,

We got a big problem that we have a db link to connect to sql server(big 5).

In original setting, there is no chinese characters in the data. We simply grab the data from sql server and do not have any issue.
But now we have data with Big5. When we import the data into our Oracle DB, which uses UTF8, all the text becomes "???????"

Changing characterset in DB is not possible. We need help to convert the text into unicode format, or allow oracle/sql server to convert it automatcically.

Thanks for help

Report message to a moderator

Re: DB Link from SQL Server 2005 (Big5) to Oracle(UTF8) [message #448000 is a reply to message #447992] Fri, 19 March 2010 00:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>When we import the data into our Oracle DB, which uses UTF8, all the text becomes "???????"

Oracle UTF8 properly handles any character set as long as data source & target client are properly configured.

Regardless of client or client's character set you can validate UTF8 data within Oracle by doing as follows:


SELECT ASCIISTR(CHINESE_FIELD) FROM USER_TABLE;

The ASCIISTR function displays any UTF8 characters as multi-byte ASCII characters.

If the UTF8 (Chinese) characters exist in the DB then ?????? is strictly a data presentation issue with the client.

Report message to a moderator

Re: DB Link from SQL Server 2005 (Big5) to Oracle(UTF8) [message #448013 is a reply to message #448000] Fri, 19 March 2010 02:01 Go to previous messageGo to next message
summoner
Messages: 44
Registered: March 2009
Member
Thanks for help

But how can I verify the following situation
"as long as data source & target client are properly configured."

The collation of table in "SQL Server 2005" is Chinese_Taiwan_Stroke_CI_AS

The characterset in Oracle DB is UTF8

Here is my situation
1. I could display the text in SQL server.
2. I could import the data to Oracle using import function in SQL Server
3. I create a DB Link to SQL server and it cannot display chinese text via db link. It shows ??????????? for all chiense text

[Updated on: Fri, 19 March 2010 02:07]

Report message to a moderator

Re: DB Link from SQL Server 2005 (Big5) to Oracle(UTF8) [message #448018 is a reply to message #448013] Fri, 19 March 2010 02:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>1. I could display the text in SQL server.
OK, no problem exists

>2. I could import the data to Oracle using import function in SQL Server
Indeterminate results.
Did Chinese characters noew reside in Oracle DB?
TBD. I told you how to verify.

>3. I create a DB Link to SQL server and it cannot display chinese text via db link

Since #1 above is true, this means failure is strictly a data presentation issue.

Problems now exist.
So go deploy solutions.

Report message to a moderator

Re: DB Link from SQL Server 2005 (Big5) to Oracle(UTF8) [message #448023 is a reply to message #448018] Fri, 19 March 2010 02:21 Go to previous messageGo to next message
summoner
Messages: 44
Registered: March 2009
Member
Thanks for quick response

BlackSwan wrote on Fri, 19 March 2010 02:11

>2. I could import the data to Oracle using import function in SQL Server
Indeterminate results.
Did Chinese characters noew reside in Oracle DB?
TBD. I told you how to verify.

I select the data and inserted it in Oracle DB table
The text is still ?????

Report message to a moderator

Re: DB Link from SQL Server 2005 (Big5) to Oracle(UTF8) [message #448115 is a reply to message #448023] Fri, 19 March 2010 09:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: DB Link from SQL Server 2005 (Big5) to Oracle(UTF8) [message #448235 is a reply to message #448115] Mon, 22 March 2010 04:35 Go to previous message
summoner
Messages: 44
Registered: March 2009
Member
Sorry for incorrect posting format. Let me tried to rephrase my wordings

There are several tables in my Oracle database and we imported data from SQL Server to Oracle DB using DB Link. Right now we got a problem on text display.
We have inserted the text into our Oracle tables. The chinese text (Big5) cannot be displayed and shown as "?????" while English text could be displayed correctly.

We could view chinese text in SQL Server 2005 and the collation in SQL Server is correct. But we failed to retrieve correct text when we retrieve it from Oracle using db link

[Updated on: Mon, 22 March 2010 04:37]

Report message to a moderator

Previous Topic: Problem Connecting Developer 6.0 to 10G Database
Next Topic: TNS Listener Service Won't Start after Changing "Log on as" Setting
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Nov 21 11:42:12 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.