Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8154
  • Last Modified:

How to set NLS_NCHAR_CHARACTERSET for client and server side?

I read some posts here on how to input chinese and display chinese correctly on both client and server side.

I think both NLS_CHAR_CHARACTERSET and NLS_NCHAR_CHARACTERSET should be changed to UTF8. Is that right?
I tried to do it by issuing "ALTER SESSION SET NLS_NCHAR_CHARACTERSET" in SQL*Plus but it has such response:

SQL> ALTER SESSION SET NLS_NCHAR_CHARACTERSET='UTF8';
ALTER SESSION SET NLS_NCHAR_CHARACTERSET='UTF8'
*
ERROR at line 1:
ORA-00922: missing or invalid option

--------------------------------------------------------

Actually I have to design an web interface (using JDBC-ODBC to connect to the database) to allow others to input chinese and then store them into database. Do I need to alter for each session for each update?
0
mchicken
Asked:
mchicken
  • 6
  • 5
1 Solution
 
schwertnerCommented:
The big problem is that you cannnot change the character set of an Oracle DB on the fly. The only time you can do this is at the creation of the database.
One way to walk around is to export the DB and to recreate it.
0
 
mchickenAuthor Commented:
That means the NLS_NCHAR_CHARACTERSET could not be changed unless I recreate it.

Then I think it may have problem. As the existing database is now storing english material, it gonna be add chinese version for the english material.

Thus is there any other work around rather than export the DB and recreate it?
0
 
mnicorasCommented:
Hi,

At your first questions the answer is almost TRUE. It can be done by altering the CHARACTER SET and NATIONAL CHARACTER SET of the database. Mostly it depends of the current settings of the database. You can migrate the database to a new character set without re-creating the database. The migration cannot be rollbacked-up and can be done just to s super-set of the current one.

shutdown immediate;

alter database XXXX character set UTF8;

alter database XXXX national character set UTF8;

before doing this operation make a full backup.

It also possible to use the CONVERT function which allows you to insert special caracters in the database. For JDBC&ODBC application, it needs some settings on client settings (for JDBC thick classes or ODBC on the client machine you need to set the NLS_LANG parameter to support the character set you want. More details?

best regards,
Marius Nicoras
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mchickenAuthor Commented:
Marius, I think your method is nearly what I want.

I have just checked the database setting with my client. The NLS_CHAR_CHARACTERSET is UTF8 while NLS_NCHAR_CHARACTERSET is US7ACSII.

1) Does there any problem that only NLS_CHAR_CHARACTERSET set to UTF8?

2) If I gonna to store XML document which contains chinese characters, should CLOB be used to store the data?
0
 
mnicorasCommented:
Hi,

1. yes. so just execute the second command for alter database.

2. also I suggest to use NCLOB.

Dont' forget about the problems with necessary JDBC&ODBC settings.

best regards,
Marius Nicoras
0
 
mchickenAuthor Commented:
I have some more questions to confirm:

1) That means I must set both character set & national character set to UTF8 if I want to support chinese?

2) Also, can I input chinese to VARCHAR? Is it a must to store in all national character set, e.g. NVARCHAR, NCLOB ?

For JDBC&ODBC setting, I want more information for the setting of NLS_LANG parameter.

Thank you very much.
0
 
mnicorasCommented:
Hi,

1. you need to set not necessary to UTF8 but definetly to a characterset which support the chinese. Consult de Oracle doc to see all characterset(http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/server.901/a88856/c13datyp.htm#14968). For simplicity I recommend UTF8.

2. it's not a must it's a suggestion otherwise should be used some tricks to work properly. depends on the Oracle version.

3. For JDBC thick client and ODBC, for which it means that you use connection trough Oracle Net Configuration, you need to have set in registry (Windows systems) as environment variable (for UNIX/LINUX systems) the NLS_LANG to support the chinese characters because the Oracle Net Configuration use this settings to translate the caracters when data is send trough this layer to the DB. Got it? suggestion NLS_LANG=CHINESE_CHINA.ZHT16MSWIN950 (http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/server.901/a90236/appa.htm#967600)
0
 
mchickenAuthor Commented:
The database is now running Oracle8i v8.1.7.
I think better use UTF8 as it would also need to store Japanese / Korean later.

For JDBC/ODBC, can I just specific in the programs so that clients need not to concern the setting for NLS_LANG. They could input foreign language by just using web browser.
0
 
mnicorasCommented:
Hi,

alter session set nls_language = 'JAPANESE'

alter session set nls_territory = 'JAPAN'

we use something like this in our web-based application using JDBC.

best regards,
Marius Nicoras
0
 
mchickenAuthor Commented:
Is it alter the nls_language and nls_territory each time for different language?
0
 
mnicorasCommented:
Hi,

yes. we have a table for all languages and at every login or language switch we have a procedure which get the input (language id) and executes automatically these two statements.

best regards,
Marius Nicoras
0
 
mchickenAuthor Commented:
You help me a lot...Thx very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now