Solved

How to set NLS_NCHAR_CHARACTERSET for client and server side?

Posted on 2002-07-07
12
7,866 Views
Last Modified: 2007-11-27
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
Comment
Question by:mchicken
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 7136947
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
 

Author Comment

by:mchicken
ID: 7137264
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
 
LVL 3

Accepted Solution

by:
mnicoras earned 50 total points
ID: 7137282
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:mchicken
ID: 7138639
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
 
LVL 3

Expert Comment

by:mnicoras
ID: 7139964
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
 

Author Comment

by:mchicken
ID: 7141547
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
 
LVL 3

Expert Comment

by:mnicoras
ID: 7142640
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
 

Author Comment

by:mchicken
ID: 7143176
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
 
LVL 3

Expert Comment

by:mnicoras
ID: 7143222
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
 

Author Comment

by:mchicken
ID: 7143508
Is it alter the nls_language and nls_territory each time for different language?
0
 
LVL 3

Expert Comment

by:mnicoras
ID: 7143541
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
 

Author Comment

by:mchicken
ID: 7144286
You help me a lot...Thx very much
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question