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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ravindran GopinathanCommented:
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.
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:
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..
dashishAuthor Commented:
I tried to set
 it gave me error
 "ORA-00922: missing or invalid option"

character set CAN NOT be changed after database is created.
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

you might change nls_language or nls_territory but not nls_nchar_characterset.
you might consider to use nclob if you really want column with very big size.
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.
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).
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.