Solved

How to set NLS_NCHAR_CHARACTERSET for client and server side?

Posted on 2002-07-07
12
7,772 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
  • 6
  • 5
12 Comments
 
LVL 47

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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now