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

Posted on 2006-04-27
Last Modified: 2012-05-05
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.
Question by:dashish
    LVL 10

    Expert Comment

    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:
    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..

    Author Comment

    I tried to set
     it gave me error
     "ORA-00922: missing or invalid option"

    LVL 19

    Expert Comment

    character set CAN NOT be changed after database is created.
    LVL 19

    Expert Comment

    you might change nls_language or nls_territory but not nls_nchar_characterset.
    LVL 19

    Expert Comment

    you might consider to use nclob if you really want column with very big size.
    LVL 19

    Expert Comment

    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.
    LVL 47

    Expert Comment

    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).
    LVL 19

    Accepted Solution

    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.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    Via a live example, show how to take different types of Oracle backups using RMAN.

    728 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

    24 Experts available now in Live!

    Get 1:1 Help Now