mikezhou
asked on
Issue to store Chinese character to Oracle9i
Hi Friends,
I have a question regarding to storing chinese characters in oracle 9i (rel 2) db, I use unicode data type to implement it instead of a unicode database.
My current db settings for NLS are:
PARAMETER VALUE
1 NLS_CHARACTERSET WE8MSWIN1252
2 NLS_NCHAR_CHARACTERSET AL16UTF16
My Client setting for NLS_LANG (system environment variables)
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
My operating system is Win2000 and its default locale is Chinese.
I can see chinese characters in SQLPLUS:
In SQLPLUS, I create a table
create table test (mynvarchar2 nvarchar2(10));
then
insert into test values ('[some chinese chars]');
then
select * from test;
It shows ???...
Any advice on the above ?
Best Regards,
Mike Zhou
I have a question regarding to storing chinese characters in oracle 9i (rel 2) db, I use unicode data type to implement it instead of a unicode database.
My current db settings for NLS are:
PARAMETER VALUE
1 NLS_CHARACTERSET WE8MSWIN1252
2 NLS_NCHAR_CHARACTERSET AL16UTF16
My Client setting for NLS_LANG (system environment variables)
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
My operating system is Win2000 and its default locale is Chinese.
I can see chinese characters in SQLPLUS:
In SQLPLUS, I create a table
create table test (mynvarchar2 nvarchar2(10));
then
insert into test values ('[some chinese chars]');
then
select * from test;
It shows ???...
Any advice on the above ?
Best Regards,
Mike Zhou
sathyagiri
Try updating the registry on the client m/c under software->oracle->NLS_LANG
ASKER
I have tried that (understand that system envrionment variable overwrite registry value) and the result still the same...
Question to your answer is in Oracle9i gobalization Guide. Listed below is the link. Please take a look.
http://download-east.oracle.com/docs/cd/B10500_01/server.920/a96529/toc.htm
http://download-east.oracle.com/docs/cd/B10500_01/server.920/a96529/toc.htm
ASKER
The doc is the exact document I am referencing before trying the above. I feel I follow all the steps stated in the doc...however I can not get the result as expected...I suspect something not obvious missing in my steps...
To store Chinese letters your DB has to be created with AL32UTF8 character set.
Or with some of the specific chinese character sets, but WE8MSWIN1252
doesn't support chinese (so far!)
Or with some of the specific chinese character sets, but WE8MSWIN1252
doesn't support chinese (so far!)
ASKER
I want a solution 'use unicode data type to implement it instead of a unicode database.' as just a very small fraction of my application will use Chinese, Japanese etc.
Sorry, this is not possible as far as I am informed!
The character set of a DB is defined at the beginning and cannot be changed.
It has global influence on the created DB and there couldn't be pieces of the DB
using another character sets. Of course applications can use different character sets, but
they should be subsets of the character set of the DB.
For your case I will suggest to create another DB with Unicode. If you have license isuues use
Oracle XE (free!, but only 4 GB user data).
The character set of a DB is defined at the beginning and cannot be changed.
It has global influence on the created DB and there couldn't be pieces of the DB
using another character sets. Of course applications can use different character sets, but
they should be subsets of the character set of the DB.
For your case I will suggest to create another DB with Unicode. If you have license isuues use
Oracle XE (free!, but only 4 GB user data).
ASKER
Oracle provide this by the solution of unicode data types NCHAR, NVARCHAR2 and national character set.
A workaround is to do schema export (only non-systen users), to create another DB with AL32UTF8 character set and to import the schemas there. You can do the experiment running the existing DB and if you are satisfied to do this for production.
The only issue is to investigate all non-system schemas and tablespaces (they need precreation before Import), but sometimes this is a good new knowledge and also can improve the DB. Also a trap could be the value of NLS_LANG registry entry - it dictates the character set of Export and Import utilities.
The only issue is to investigate all non-system schemas and tablespaces (they need precreation before Import), but sometimes this is a good new knowledge and also can improve the DB. Also a trap could be the value of NLS_LANG registry entry - it dictates the character set of Export and Import utilities.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Schwertner, sorry for the delayed acceptance. I must be either not notice your last answer or busy at other things then. Thanks a lot for your detailed explaination and kind help.