9 Replies Latest reply: Aug 29, 2011 3:36 AM by orafad RSS

    Change internal nls_characterset and get started not work properly

    user12242205
      i install Oracle XE 11g in windows vista
      i changed the internal nls_characterset

      i applied this command

      sqlplus /nolog
      conn sys as sysdba;
      shutdown;
      STARTUP MOUNT;
      ALTER SYSTEM ENABLE RESTRICTED SESSION;
      ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
      ALTER SYSTEM SET AQ_TM_PROCESSES=0;
      ALTER DATABASE OPEN;
      Alter database character set INTERNAL_USE WE8MSWIN1252;
      SHUTDOWN IMMEDIATE; -- or SHUTDOWN NORMAL;
      STARTUP;


      when i go to get started or put the url for application express.....

      in the browser i saw

      #HEAD# #FORM_OPEN#
      #BODY#
      #CLOSE##PREVIOUS##NEXT##DELETE##EDIT##CHANGE##CREATE##CREATE2##EXPAND##COPY##HELP#
      #TITLE#
      #CLOSE##PREVIOUS##NEXT##DELETE##EDIT##CHANGE##CREATE##CREATE2##EXPAND##COPY##HELP#
      #BODY#
      #TITLE#
      #CLOSE##PREVIOUS##NEXT##DELETE##EDIT##CHANGE##CREATE##CREATE2##EXPAND##COPY##HELP#
      #BODY#
      #TITLE#
      #CLOSE##PREVIOUS##NEXT##DELETE##EDIT##CHANGE##CREATE##CREATE2##EXPAND##COPY##HELP#
      #BODY#
      id="header">
      #LOGO##REGION_POSITION_06#
      #REGION_POSITION_07#
      #WELCOME_USER#
      #NAVIGATION_BAR# #REGION_POSITION_08#
      #TAB_CELLS#

      any idea?

      thanks
      Daniel
        • 1. Re: Change internal nls_characterset and get started not work properly
          Srini Chavali-Oracle
          What was the original characterset value ? Why do you need to change the characterset ? The steps you mention above are only supported in very specific limited set of cases. Your best bet is to de-install and re-install 11g XE with the appropriate characterset.

          HTH
          Srini
          • 2. Re: Change internal nls_characterset and get started not work properly
            Udo
            Hi,

            XE can only be installed with one charset, which is unicode. See the [url http://download.oracle.com/docs/cd/E17781_01/install.112/e18803/toc.htm#XEINW138]Installation Guide for details. There are no plans on having this changed for the final release, as discussed in {thread:id=2210640}.
            Especially since APEX comes preinstalled, you won't be able to use that installation with a non-unicode charset. You might get it working when you reinstall it manuall after changing the charset. Note that you'll loose the XE-specific database management applications in that case. And there's no guarantee that other components will work properly with a different charset either.

            -Udo
            • 3. Re: Change internal nls_characterset and get started not work properly
              sdcsdc
              Just re-create the database and specify a single-byte character set. I've done it. Works great.
              • 4. Re: Change internal nls_characterset and get started not work properly
                Etore Schiavini
                "XE can only be installed with one charset, which is unicode"

                And this is a shame. This "great" idea prevents migration from 10g XE on several languages other than english.
                • 5. Re: Change internal nls_characterset and get started not work properly
                  Srini Chavali-Oracle
                  Etore Schiavini wrote:
                  "XE can only be installed with one charset, which is unicode"

                  And this is a shame. This "great" idea prevents migration from 10g XE on several languages other than english.
                  How so ?

                  Srini
                  • 6. Re: Change internal nls_characterset and get started not work properly
                    orafad
                    I think this is nothing new or specific to XE. Edition regardless, Oracle has recommended Unicode for database character set since 11.1 (or even 10.2).

                    Its in the docs. Examples:

                    +"At the top of the list of character sets Oracle recommends for all new system deployment is the Unicode character set AL32UTF8."+

                    +"Oracle recommends Unicode AL32UTF8 as the database character set. Unicode is the universal character set that supports most of the currently spoken languages of the world. It also supports many historical scripts (alphabets). Unicode is the native encoding of many technologies, including Java, XML, XHTML, ECMAScript, LDAP. Unicode is ideally suited for databases supporting the Internet and the global economy."+
                    • 7. Re: Change internal nls_characterset and get started not work properly
                      Etore Schiavini
                      Srini Chavali wrote:

                      How so ?

                      Srini
                      I will try to explain in english, sorry for the language errors.

                      In my case, brasilian portuguese, there are a lot of characters above ascii 128.
                      All of them need more space on 11g XE and I cannot simply export and import data from 10g XE to 11g XE.
                      I need to rewrite all database models and duplicate all varchar2 sizes on 11g XE before the import operation.

                      Example in 10g XE:

                      SQL> create table t( c varchar2(3) );

                      Table created.

                      SQL> insert into t values( 'abç' );

                      1 row created.

                      SQL> select c, length( c ) from t;

                      C LENGTH(C)
                      --- - - - - - - - -
                      abç 3

                      On the same table in 11g XE:

                      SQL> insert into t values( 'abç' )
                      insert into t values( 'abç' )
                      *
                      ERROR at line 1:
                      ORA-12899: value too large for column "SCH"."T"."C" (actual: 4, maximum: 3)

                      Same error occurs on the import operation (imp or impdp)

                      I managed to alter the internal characterset on 11g XE to WE8MSWIN1252, but as someone said earlier on this thread it's a non supported operation.

                      Étore
                      • 8. Re: Change internal nls_characterset and get started not work properly
                        Etore Schiavini
                        orafad wrote:
                        I think this is nothing new or specific to XE. Edition regardless, Oracle has recommended Unicode for database character set since 11.1 (or even 10.2).

                        Its in the docs. Examples:

                        +"At the top of the list of character sets Oracle recommends for all new system deployment is the Unicode character set AL32UTF8."+

                        +"Oracle recommends Unicode AL32UTF8 as the database character set. Unicode is the universal character set that supports most of the currently spoken languages of the world. It also supports many historical scripts (alphabets). Unicode is the native encoding of many technologies, including Java, XML, XHTML, ECMAScript, LDAP. Unicode is ideally suited for databases supporting the Internet and the global economy."+
                        Ok for new applications or uses, but a serious company would offer a compatibility option for your costumers (even for a free product).
                        And come on, we are talking about a very simple thing. An option on installation would resolve the problem (like that on dbca).

                        Étore
                        • 9. Re: Change internal nls_characterset and get started not work properly
                          orafad
                          Etore Schiavini wrote:
                          SQL> insert into t values( 'abç' )
                          insert into t values( 'abç' )
                          *
                          ERROR at line 1:
                          ORA-12899: value too large for column "SCH"."T"."C" (actual: 4, maximum: 3)
                          Well, the docs for GA release of XE 11g hopefully will have something on migration (including this certainly expected issue), i.e a chapter on user data migration.


                          For a solution in your case, couldn't you do something like:
                          - import just table definitions to a schema
                          - write a script that adjusts the definitions for all varchar objects in the schema (alter .. (x varchar2(n CHAR)).
                          - import the data