SharePoint Oracle UNICODE issue : Chinese Control Characters

When you run an English SharePoint version on an English OS. Getting SSRS to deal with Oracle Unicode Characters is a challenge as well !

ORACLE has does not show the Chinese Characters properly in SharePoint.

image

As you can see there is always 1 control Character between each Chinese character.

Oracle and Microsoft are not friends yet Ninja

Solution :

This should work using the CONVERT function between Character Sets. But it did not 😦

Convert between character sets it says, give it a try.

SELECT CONVERT('Ä Ê Í Ó Ø A B C D E','US7ASCII','WE8ISO8859P1') FROM DUAL

By the way you can’t show the Chinese characters on an English OS either using SQLPLus command line.

Because the Character set is defined in the NLS_LANG environment variable

solve it use the UNISTR() and ASCIISTR() functions to display which control characters are in play.

SELECT UNISTR(ASCIISTR(trim(FieldName)) AS Alias FROM ...

Next you can remove them using the REPLACE() function.

SELECT UNISTR(REPLACE(ASCIISTR(trim(FieldName)), ('\FFFD%'),'')) AS Alias FROM ...

If you know a better way, please share here

Tip 1 :

By the way you can’t show the Chinese characters on an English OS either using SQLPLus command line.

Because the Character set is defined in the NLS_LANG environment variable

or using this qry

<br>SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET','NLS_TERRITORY','NLS_LANGUAGE')<br>

Tip 2 :

If you set the NLS_LANG parameter on the client that runs the ODBC using the Oracle driver to

NLS_LANG = AMERICAN_AMERICA.ZHS16GBK

image

It might just work ?!

Tip 3 :

An other option is NOT to use the ODBC connection3 But to use the .NET Oracle provider including the Unicode=True setting

image

Data Source=YourDataSource;Unicode=True

But it say only valid till Oracle 9i

clip_image002

Ps :

There is definetly no UNICODE support if you use the Microsoft Driver for Oracle.

http://support.microsoft.com/kb/244661
… The ODBC Driver for Oracle does not support any of the new Oracle8 data types — Unicode data types, BLOBs, CLOBs, and so on …

Unless you install a later version of the drivers (See MDAC documention)
But then again I read there ara other issues coming your way ? https://community.oracle.com/message/1147185

Work arounds using the ALTER SESSION command http://msdn.microsoft.com/en-us/library/ms714756(v=vs.85).aspx
Or us the .NET version of the MS Driver for Oracle which support UNICODE.

Look here for the MS versions history http://msdn.microsoft.com/en-us/library/ms810810.aspx

Next Solution might be that you tweak the native Oracle Driver to support the code page you are looking for.

This explains it very well ?
http://www.toadworld.com/products/toad-for-oracle/w/toad_for_oracle_wiki/233.unicode-support-in-toad-10.aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: