This discussion is archived
12 Replies Latest reply: Mar 4, 2013 11:55 AM by Sergiusz Wolicki (Oracle) RSS

Oracle XE charset problem

561125 Newbie
Currently Being Moderated
Is there any way to change NLS language territory and character set in Oracle 10.2 XE on windows xp ?

In documentation there is note to change registry values but when I run

select * from NLS_DATABASE_PARAMETERS;

i always get american america AL32UTF8

and i need german germany EE8ISO8859P2
  • 1. Re: Oracle XE charset problem
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated
    There is no official way to change the character set of a XE database.

    Note, AL32UTF8 is a good character set to store German and it has the advantage of being able to store much much more.

    As far as language and territory database settings are concerned, you usually should not care about them. They are used only to provide the NLS environment for evaluation of CHECK constraints. But you should write your CHECK constraints so that they do not depend on external settings anyway.

    -- Sergiusz
  • 2. Re: Oracle XE charset problem
    PostmodernLuddite Newbie
    Currently Being Moderated
    I'm having the same issue. I would like to change to UTF8 and the current character set is WE8MSWIN1252.

    I've found some Oracle documentation here:http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10749/ch11charsetmig.htm#CEGHDCJF

    Is there any reason I can't run the csalter script on my XE database just to change the character set? XE, while lacking much of the web functionality of regular 10g, seems to have a lot of the back-end equipment.
  • 3. Re: Oracle XE charset problem
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated
    The reason is probably more of the legal than technical nature. OracleXE has been designed to provide a pre-created database in either WE8MSWIN1252 or AL32UTF8 character set. The csalter.plb script is not provided with OracleXE.

    The Enterprise Edition or Standard Edition has this script but I doubt the license allows you to use the script for anything else than the database to which it belongs. This restriction includes the use in XE. For any official statement regarding this possible restriction, contact Oracle Customer Relations or try to post the question on the XE forum.

    The proper way to migrate your database is to install a second AL32UTF8 database from the XE Universal version and move the data using export and import.


    -- Sergiusz
  • 4. Re: Oracle XE charset problem
    orafad Oracle ACE
    Currently Being Moderated
    As said, Oracle XE is available in two variants. If you want to change from WE8MSWIN1252, the other option is "Universal" (AL32UTF8). See this download page.
  • 5. Re: Oracle XE charset problem
    582282 Newbie
    Currently Being Moderated
    In other words... it can't be done!
    Try using the standard versions, not the XE.
  • 6. Re: Oracle XE charset problem
    730518 Newbie
    Currently Being Moderated
    #sqlplus /nolog

    conn sys as sysdba;

    SHUT;
    STARTUP RESTRICT;
    Alter database character set INTERNAL_USE EE8ISO8859P2;
    SHUT;
    STARTUP;
  • 7. Re: Oracle XE charset problem
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated
    This method is unsupported, i.e. Oracle warns you that it may lead to problems. Of course, as the whole Oracle XE is a free product and has therefore no support, this is more a formal warning.

    Note, if you do the mentioned steps on the Universal (AL32UTF8) edition of XE, you will corrupt the database.


    -- Sergiusz
  • 8. Re: Oracle XE charset problem
    440119 Newbie
    Currently Being Moderated
    @user7293931 -- Thanks! I tried this and it actually messed up all applications and db.
    I wouldnt suggest anyone trying this. I had to retrace my steps back.

    best,
    -vaibhav

    Edited by: Vaibhav Chourasia on Nov 24, 2010 6:28 AM
  • 9. Re: Oracle XE charset problem
    736941 Newbie
    Currently Being Moderated
    Supper, for me it worked:

    SHUT;
    STARTUP RESTRICT;
    ALTER DATABASE CHARACTER SET INTERNAL_USE BLT8MSWIN1257;
  • 10. Re: Oracle XE charset problem
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    user12045823 wrote:
    Supper, for me it worked:

    SHUT;
    STARTUP RESTRICT;
    ALTER DATABASE CHARACTER SET INTERNAL_USE BLT8MSWIN1257;
    Huh ? 100% guaranteed that your database is now corrupt beyond repair :-)

    Srini
  • 11. Re: Oracle XE charset problem
    712761 Newbie
    Currently Being Moderated
    The database won't be corrupted if it is empty. This is a very useful way of changing the character set on an XE database.
  • 12. Re: Oracle XE charset problem
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated
    There are more requirements then just "an empty database" for this to work successfully. There is no such thing as an empty database, because each database contains at least the data dictionary. If you do not know all the implications of using an unsupported, undocumented, internal-only command, do not use it. Unless, of course, the database is for playing, testing and experimenting only.


    -- Sergiusz