12 Replies Latest reply: Mar 4, 2013 1:55 PM by Sergiusz Wolicki-Oracle RSS

    Oracle XE charset problem

    561125
      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
          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
            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
              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
                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
                  In other words... it can't be done!
                  Try using the standard versions, not the XE.
                  • 6. Re: Oracle XE charset problem
                    730518
                    #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
                      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
                        @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
                          Supper, for me it worked:

                          SHUT;
                          STARTUP RESTRICT;
                          ALTER DATABASE CHARACTER SET INTERNAL_USE BLT8MSWIN1257;
                          • 10. Re: Oracle XE charset problem
                            Srini Chavali-Oracle
                            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
                              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
                                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