This discussion is archived
9 Replies Latest reply: Aug 29, 2011 1:36 AM by orafad RSS

Change internal nls_characterset and get started not work properly

user12242205 Newbie
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    user194720 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    "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 Oracle ACE Director
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points