• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 27985
  • Last Modified:

How to set NLS_NCHAR_CHARACTERSET to from AL16UTF16 to UTF8 in oracle 10g.

Hello All,

I am using nvarchar2 data type to support multi language in my table for 10g oracle database.
I want to set it maxlength to 4000.

But my oracle database 10g supports only AL16UTF16 for NLS_NCHAR_CHARACTERSET which allow to set only nvarchar2 (2000).

I came to know that UTF8 supports nvarchar2 (4000) datatype.

Can any one please tell me how to set successfully this NLS_NCHAR_CHARACTERSET setting.

Thanks in advanced.
dashish.
0
dashish
Asked:
dashish
1 Solution
 
ravindran_eeeCommented:
There are four ways to specify NLS parameters in general --
1. As initialization parameters on the server.
   Parameters can be included in the initialization file (INIT.ORA) to specify a default server NLS environment.
   These settings have no effect on the client side; they control only the server's behavior.
   For example:  NLS_TERRITORY = "CZECH REPUBLIC"
2. As environment variables on the client. NLS parameters can be used to specify
   locale-dependent behavior for the client, overriding the defaults set for the server
   in the initialization file.For example: on a UNIX system:
   % setenv NLS_SORT FRENCH
3. As ALTER SESSION parameters. NLS parameters set in an ALTER SESSION statement can be used
   to override the defaults set for the server in the initialization file,
   or set by the client with environment variables. For example:
   SVRMGR> ALTER SESSION SET NLS_SORT = FRENCH
4. As an SQL function parameter. NLS parameters can be used explicitly to hardcode
   NLS behavior within a SQL function. Doing so will override the defaults set for the
   server in the initialization file, the client with environment variables, or
   ALTER SESSION on the client.For example:
  TO_CHAR(hiredate, 'DD/MON/YYYY', 'nls_date_language = FRENCH')

Please let me know whether the above explanation is sufficient..
0
 
dashishAuthor Commented:
I tried to set
 ALTER SESSION SET NLS_NCHAR_CHARACTERSET=UTF8
 it gave me error
 "ORA-00922: missing or invalid option"

 
0
 
actonwangCommented:
character set CAN NOT be changed after database is created.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
actonwangCommented:
you might change nls_language or nls_territory but not nls_nchar_characterset.
0
 
actonwangCommented:
you might consider to use nclob if you really want column with very big size.
0
 
actonwangCommented:
also, be aware that even you use UTF8 , you might not get to store 4000 characters because UTF8 is a variable length encoding. for nvarchar2 the limit is 4000 bytes which is FIXED.
0
 
schwertnerCommented:
The way to change the character set is to Export the DB (set NLS_LANG parameter in order not to lose characters),
to create new database using AL32UTF8 character set
and to Import the DB (again set NLS_LANG parameter).
You can create new DB on the same machine using the same Oracle installation (Oracle Home)
using the GUI DBCA (Data Base Creation Assistant).
0
 
actonwangCommented:
anyway, as I already said, you can not change charset in your existing db. And also as the reason I posted, i don't see you will have more success with UTF8 in your case.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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