This discussion is archived
6 Replies Latest reply: Jul 4, 2011 2:28 AM by 872966 RSS

How to set NLS_CHARACTERSET on install ( or later ? )

872966 Newbie
Currently Being Moderated
Hello,

I'll need to set up a Installation of Oracle 11g XE Beta with NLS_CHARACTERSET = WE8ISO8859P15

( Because I have to import Data from an existing Installation )

Target is Linux ( Suse 11.4 )

before the installation my:

messse:~ # locale charmap
ISO-8859-15

So the documentation says the Install should use NLS_CHARACTERSET = WE8ISO8859P15

but after the install ill get :

SQL> select * from NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET';

PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_CHARACTERSET
AL32UTF8

what can or should i do ?

Regards
Jens-Peter Hillers
  • 1. Re: How to set NLS_CHARACTERSET on install ( or later ? )
    gokhanatil Oracle ACE
    Currently Being Moderated
    869963 wrote:
    So the documentation says the Install should use NLS_CHARACTERSET = WE8ISO8859P15
    Hello Jens-Peter

    Welcome to the forums. I think you misunderstood the document. You can only change the charset of your client. Here's the document for Oracle Database XE Character and Language Configurations:

    http://download.oracle.com/docs/cd/E17781_01/install.112/e18803/toc.htm#XEINW138
    Oracle Database XE is available only in Universal character set and language configurations:

    The database is created using Unicode(AL32UTF8) character set, which is suitable for global data in any language.

    The Oracle Application Express user interface and database error messages are available in Brazilian Portuguese, Chinese (Simplified and Traditional), English, French, German, Italian, Japanese, Korean, and Spanish.
    So you don't need to change the charset of your database to be able to import data.

    Regards

    Gokhan
  • 2. Re: How to set NLS_CHARACTERSET on install ( or later ? )
    872966 Newbie
    Currently Being Moderated
    Hello Gokhan,

    What I really want to do is:

    Take a Dump from the Production System ( Oracle 11g on a SUN OS )
    an Import it as Test / Show System to a Linux based Oracle 11g XE.

    when I try to import I'll get a lot of Errors,
    because of fully filled varchar2 fields that could not be imported
    because the Fieldsize is too small.

    My guess was that this is happening in the character set conversion during the import.

    that's why I try to set the same charset as in the Production-System which is WE8ISO8859P15

    any help or idea ?

    Regards
    Jens-Peter
  • 3. Re: How to set NLS_CHARACTERSET on install ( or later ? )
    Udo Guru
    Currently Being Moderated
    Hello Jens-Peter,

    in addition to what Gokhan said, I think it's worth to mention, that you practically can't change the database charset - XE 11.2 always comes with AL32UTF8. As the instance is already created when you install the software, it would not be advisable to change the charset. As discussed in several other threads, there won't be a release with a different charset (see Kris' post in {thread:id=2210640}).
    But there should be no need to change the charset in your case, as AL32UTF8 covers WE8ISO8859P15 completely, i.e., all characters from your source charset are available in your target charset and hence can be imported properly if your client is configured accordingly.

    -Udo
  • 4. Re: How to set NLS_CHARACTERSET on install ( or later ? )
    gokhanatil Oracle ACE
    Currently Being Moderated
    Please read MOS [ID 436240.1] ORA-2375 ORA-12899 ORA-2372 Errors While Datapump Import Done From Single Byte Characterset to Multi Byte Characterset Database:

    https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=436240.1

    Summary:

    You need pre-create tables with bigger field sizes, then import data.

    Regards

    Gokhan
  • 5. Re: How to set NLS_CHARACTERSET on install ( or later ? )
    orafad Oracle ACE
    Currently Being Moderated
    869963 wrote:
    when I try to import I'll get a lot of Errors,
    because of fully filled varchar2 fields that could not be imported
    because the Fieldsize is too small.
    What is the error message?

    To be able to migrate this app/database dump to 11.2 XE, you may need to pre-create tables with columns definitions using character [url http://download.oracle.com/docs/cd/E11882_01/server.112/e10729/ch2charset.htm#i1006683]length semantics over byte semantics. E.g. (col varchar2(10 char)). In case of varchar2 columns with more than 1000 characters, there is a risk of truncation since in XE/AL32UTF8 a character may need up to 4 bytes of storage.

    Edited by: orafad on Jul 4, 2011 2:56 PM
  • 6. Re: How to set NLS_CHARACTERSET on install ( or later ? )
    872966 Newbie
    Currently Being Moderated
    Thanks a lot, I'll have a try with the vachar2(20 CHAR) syntax
    and will report the result tomorrow.

    regards
    Jens-Peter

Legend

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